Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2010
    Location
    Orlando, FL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm importing an Excel file into Access using a macro and it works fine. I'm looking for code that would open the file browse window allowing the user to select a file. This seems to be a much better method than have the path hard-coded in a macro. I'm assuming it's possible to open a window in Access and allow the user to select the file to import from. I see post 304810 referenced in requests of this nature. Yet, I'm unable to access this post.

    Thanks Much,
    Mason

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    What version of Access are you using? The macro capabilities in 2007 are a fair bit more robust than prior version. In prior versions you would need to use VBA, and invoke the common file dialog to let the user browse to the file, then capture the path and file name in a string variable, and then use the TransferSpreadsheet method to import the Excel file. In 2007, if you don't specify the file name and location, it is possible that Access will prompt you for a file location, but I haven't tested it. With regard to post 304810, a number of old posts were lost in the 2009 conversion to IP Board, as well as in a crash that happened a few years ago.
    Wendell

  3. #3
    New Lounger
    Join Date
    Mar 2010
    Location
    Orlando, FL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    What version of Access are you using? The macro capabilities in 2007 are a fair bit more robust than prior version. In prior versions you would need to use VBA, and invoke the common file dialog to let the user browse to the file, then capture the path and file name in a string variable, and then use the TransferSpreadsheet method to import the Excel file. In 2007, if you don't specify the file name and location, it is possible that Access will prompt you for a file location, but I haven't tested it. With regard to post 304810, a number of old posts were lost in the 2009 conversion to IP Board, as well as in a crash that happened a few years ago.
    I'm using Access 2007... I will play around with the variables with TransferSpreadsheet command.

    Thanks

  4. #4
    New Lounger
    Join Date
    Mar 2010
    Location
    Orlando, FL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't seem to be making any headway in enabling a macro using the 'TransferSpreadsheet' command to use a 'dialog box' for importing a file.

    Help...

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Are you familiar with Visual Basic for Applications? If not, then I don't think you are going to be able to do what you want.
    Wendell

  6. #6
    New Lounger
    Join Date
    Mar 2010
    Location
    Orlando, FL
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    Are you familiar with Visual Basic for Applications? If not, then I don't think you are going to be able to do what you want.
    I've basic skills with VBA and I'm due to take an MS Access 2007 level 3 class this Friday that will cover some VB topics. I've been
    querying the Web on this topic for a few days and have been able to 'pull' some script/routines online.

    Yet, I'm still having issues with the third varialbe in the TransferSpreadsheet's third variable (were the import file name is identified as a variable)

    Below is the routine I'm messing with...

    Private Sub cmdFileDialog_Click()
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim strMsg As String
    On Error GoTo Err_cmdFileDialog_Click
    ' This requires a reference to the Microsoft Office 12.0 Object Library.

    MsgBox "Please ensure that the Columns of the Excel document about to be loaded are labeled EXACTLY as followed: 'DTCNUMBER' 'PARTICIPANTNAME' 'SHARES'", , "***IMPORTANT INFORMATION***"

    ' Clear the list box contents.
    Me.FileList.RowSource = ""

    ' Set up the File dialog box.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
    ' Allow the user to make multiple selections in the dialog box.
    .AllowMultiSelect = True

    ' Set the title of the dialog box.
    .Title = "Select One or More Files"

    ' Clear out the current filters, and then add your own.
    .Filters.Clear
    .Filters.Add "DTC List (Excel Format)", "*.xls,*.xlsx"

    ' Show the dialog box. If the .Show method returns True, the
    ' user picked at least one file. If the .Show method returns
    ' False, the user clicked Cancel.
    If .Show = True Then
    ' Loop through each file that is selected and then add it to the list box.
    For Each varFile In .SelectedItems
    Me.FileList.AddItem varFile

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "misc", Me.FileList.RowSource, True
    Next
    Else
    MsgBox "You clicked Cancel in the file dialog box."
    End If
    End With
    Exit_cmdFileDialog_Click:
    Exit Sub

    Err_cmdFileDialog_Click:
    MsgBox "The following error occurred: " & "***" & Err.Description & "***" & " Please ensure the excel columns are labeled correctly.", , "Import Failure"
    Resume Exit_cmdFileDialog_Click

    End Sub

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Well, that is the name that will be given to the table that you import the spreadsheet to - and you presumably want it to be a table that doesn't already exist. You might want to give it the name of the file that you are importing on the presumption that would make it unique. Otherwise users would get a prompt asking if you want to overwrite the existing table.
    Wendell

  8. #8
    Star Lounger
    Join Date
    Mar 2012
    Posts
    78
    Thanks
    20
    Thanked 0 Times in 0 Posts
    Hi

    Did you get anywhere with the code you needed for importing Excel files into Access?

    Jean

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •