Import Spreadsheet Problem

Discussion in 'Access Macros' started by fishqqq@hotmail.com, Dec 13, 2011.

  1. Guest

    Is there a way to create a macro that will ask the user which .xls
    file (on their hard drive) they wish to a particular table in a .mdb
    file?

    I have customers emailing me .xls files which have only one row of
    data in them and i would like the user to click a button and be asked
    which .xls file they wish to import and have this data pasted into
    [table1] in my .mde file.

    Once the data is in the table I know how to get it to do what i wish.
    What i don't understand is how to create a macro that will ask the
    user which file to import.
    The "importexportspreadsheet" action will let me import a
    predetermined file but i need something that will ask me for the
    specific file i wish to import.

    I know i can do this with the external data options but when i create
    my .mde file the end users will not have the ability to do this. i
    need something 'built in' to access which will allow them to do this.

    any ideas are greatly appreciated.

    tks
    Steve
     
    , Dec 13, 2011
    #1
    1. Advertisements

  2. Ken Snell Guest

    Ken Snell, Dec 14, 2011
    #2
    1. Advertisements

  3. Guest

    I am looking to have this code (above) triggered at the beginning of a
    macro sequence and I believe the best way to do this would be to use
    the :
    "Run Code" expression . But in order to do this I need to insert the
    "function name".

    the code I would like to run is an event procedure:

    Private Sub Command439_Click()
    Dim strPathFile As String, strFile As String, strPath As String
    Dim strTable As String
    Dim blnHasFieldNames As Boolean
    ' Change this next line to True if the first row in EXCEL worksheet
    ' has field names
    blnHasFieldNames = True

    ' Replace C:\Documents\ with the real path to the folder that
    ' contains the EXCEL files
    strPath = "C:\FMS\SHAN\"

    ' Replace tablename with the real name of the table into which
    ' the data are to be imported
    strTable = "1 Shan Ship Download"

    strFile = Dir(strPath & "*.xls")
    Do While Len(strFile) > 0
    strPathFile = strPath & strFile
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    strTable, strPathFile, blnHasFieldNames

    ' Uncomment out the next code step if you want to delete the
    ' EXCEL file after it's been imported
    Kill strPathFile

    strFile = Dir()
    Loop

    End Sub

    If i wanted to run this before the rest of my macro with the "run
    code" expression - what would the function name be?
    I know i'm trying to combine code with macros here but i have a a lot
    going on in the macro that i need done AFTER the above code has been
    run. And instead of having the code triggered on one command button
    and the macro triggered with another - i would like to have both run
    when the user clicks one button (instread of two)

    thanks
    Steve
     
    , Dec 24, 2011
    #3
  4. Ken Snell Guest

    I'm assuming that you want to run a macro on the OnClick event of the
    Command439 control on the form? Assuming yes, copy the code you have posted
    and put it into a public module (Visual Basic Editor | Insert Module).
    Rename the procedure
    Public Function DoTheImport

    and change End Sub to End Function.

    Then create a macro and make its first step RunCode and use the DoTheImport
    name as the function name. Then add other steps that you want to run in the
    macro. Assign the macro to the OnClick event of the Command439 control.
    --

    Ken Snell
    http://www.accessmvp.com/KDSnell/




    I am looking to have this code (above) triggered at the beginning of a
    macro sequence and I believe the best way to do this would be to use
    the :
    "Run Code" expression . But in order to do this I need to insert the
    "function name".

    the code I would like to run is an event procedure:

    Private Sub Command439_Click()
    Dim strPathFile As String, strFile As String, strPath As String
    Dim strTable As String
    Dim blnHasFieldNames As Boolean
    ' Change this next line to True if the first row in EXCEL worksheet
    ' has field names
    blnHasFieldNames = True

    ' Replace C:\Documents\ with the real path to the folder that
    ' contains the EXCEL files
    strPath = "C:\FMS\SHAN\"

    ' Replace tablename with the real name of the table into which
    ' the data are to be imported
    strTable = "1 Shan Ship Download"

    strFile = Dir(strPath & "*.xls")
    Do While Len(strFile) > 0
    strPathFile = strPath & strFile
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    strTable, strPathFile, blnHasFieldNames

    ' Uncomment out the next code step if you want to delete the
    ' EXCEL file after it's been imported
    Kill strPathFile

    strFile = Dir()
    Loop

    End Sub

    If i wanted to run this before the rest of my macro with the "run
    code" expression - what would the function name be?
    I know i'm trying to combine code with macros here but i have a a lot
    going on in the macro that i need done AFTER the above code has been
    run. And instead of having the code triggered on one command button
    and the macro triggered with another - i would like to have both run
    when the user clicks one button (instread of two)

    thanks
    Steve
     
    Ken Snell, Dec 26, 2011
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.