Import Multiple Spreadsheets into Access | File Dialog VBA

Sometimes it would be great to combine a bunch of spreadsheets into one table. When working with consolidated spreadsheet data, be sure to manage duplicates.

To import multiple spreadsheets into Access with VBA, there are a few housekeeping items that must be completed. First, you need to make sure that the data structure for all of your source files is the same. Field names, datatypes, everything should be a match.

Step 1: Use the Access spreadsheet query wizard to pull in a sample of your data. This will allow you to design your schema properly. Open design view and set up all of your data types how you need them.

Step 2: Nuke the data(not the table itself) in your new table

DELETE * FROM

Step 3: Open your VBA environment, select your tools menu and add a reference to the MS Office xx.x Object Library. The xx.x will be 15.0 if you have Access 2013 and down from there with older versions.

Step 4: Add a new module to your VBA project and paste this function in:

'--------------------------------------------------
' NEED TO ADD REFERENCE
'--------------------------------------------------
'NOTE: To use this code, you must reference
'The Microsoft Office 14.0 (or current version)
'Object Library by clicking menu Tools>References
'Check the box for:
'Microsoft Office 14.0 Object Library in Access 2010
'Microsoft Office 15.0 Object Library in Access 2013
'Click OK
'--------------------------------------------------
Public Sub multSpreadsheetImport(iTable As String)
Dim fd As FileDialog, vrtSelectedItem As Variant
 
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    .AllowMultiSelect = True
    If fd.Show = True Then
        For Each vrtSelectedItem In fd.SelectedItems
            DoCmd.TransferSpreadsheet acImport, , iTable, vrtSelectedItem, True
        Next vrtSelectedItem
    Else
        'Exit code if no file is selected
        End
    End If
End With
 
Set fd = Nothing
 
End Sub

Step 5: Add a call to the imported function where you need it.

Call multSpreadsheetImport("")

Step 6: Test. If you get an error, make sure that you added a reference to the Microsoft Office Object Library. Without the reference it won’t work. After making the call, you should see a filedialog window pop open. Select all of you source files and click ‘OK’

As always comment if you get stuck.