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.Clear
.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)
Else
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.
