The simplest way to import a spreadsheet is to use the TransferSpreadsheet method
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, "TableName", "FileName", True
You can place this line of code behind a button's click event.
You can implement variables and make your code dynamic with a couple of string variables.
Dim strTable as string
dim strFilePath as string
strTable = "MyTableName"
strFilePath = "C:\Test\FileName.xlsx"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, "strTable", "strFilePath", True
The above is still hardcoded with literal text but the declaration of the two string variables give you a couple of buckets to hold User Input.
You can collect user input via the File System Object's FilePicker. You need to make a reference to Microsoft Office XX.X Object Library in order for (Application.FileDialog) to work.
Code:
Dim strTable as string
dim strFilePath as string
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Locate a file to Import"
.ButtonName = "Import"
.Filters.Clear
.Filters.Add "Excel", "*.xlsx"
.InitialFileName = "C:\Test"
.InitialView = msoFileDialogViewThumbnail
If .Show = 0 Then
'There is a problem
Exit Sub
End If
'Save the first file selected
strFilePath = Trim(.SelectedItems(1))
End With
strTable = "MyTableName"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, "strTable", "strFilePath", True