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.
Ryan,
Sincerely, thank you. The code worked great! I’m using it to select multiple spreadsheets, each appending to a single table.
Brilliant, exactly what I was looking for. Thanks 🙂
same thing on everyone, single file upload or multiple
run time error ‘3274’
External table is not in the expected format