Import Multiple tables into Access | Prompt user RRS feed

  • Question

  • Hei :)

    I'm fresh  in Access. I'm creating a form whose first step is to import  3 files from Excel into existing tables.I want to ask the user the file path of each file and then import them to 3 separate tables.

     any suggestion?

     I really appreciate your help :) 


    Wednesday, October 7, 2015 2:52 PM


  • Hi Sea and Anne,

    Based on my understanding, we can use file dialog to enable users select the spreadsheets want to import. Then we can use DoCmd.TransferSpreadsheet to import the spreadsheets. And here is an simple demo to import the spreadsheet selected by user to the specific table for your reference:

    Sub ImportSpreadSheets()
    Dim excelFile As String
    excelFile = GetFile()
    If Len(excelFile) <> 0 Then
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
     "Employees1", excelFile, True
    End If
    End Sub
    Function GetFile()
       ' Requires reference to Microsoft Office 11.0 Object Library.
       Dim fDialog As Office.FileDialog
       Dim varFile As Variant
       ' Set up the File Dialog.
       Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
       With fDialog
          ' Allow user to make multiple selections in dialog box
          .AllowMultiSelect = False
          ' Set the title of the dialog box.
          .Title = "Please select one spreadsheet"
          ' Clear out the current filters, and add our own.
          .Filters.Add "Excel Files", "*.xlsx"
          .Filters.Add "All Files", "*.*"
          ' 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
            GetFile = .SelectedItems(1)
            GetFile = ""
          End If
       End With
    End Function

    To make the code work, we need to add the reference for Microsoft Office {15.0} object library via VBE-> Tools-> Reference.

    Here are some helpful articles for you learning Access developing:

    Application.FileDialog Property (Access)

    DoCmd.TransferSpreadsheet Method (Access)

    Access VBA reference

    Regards & Fei

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 8, 2015 2:46 AM