This quick tutorial will show you how to import MS Excel worksheet into MS Access using VBA. On this tutorial, we will only import some data specified by data range from Excel. The method that I use here is DoCmd.TransferSpreadsheet. See how it works below:
First, the following information from the Excel spreadsheet we want to import. Change it with your own details
Excel file name and location: “E:\test.xls”
Sheet name on the Excel file: Report1
'Create a new sub routine called ImportExcel
' Delete any previous access table
DoCmd.DeleteObject acTable, "Test"
' Import data from Excel using a specific cell range
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test", "E:\Test.xls", True, "Report1!A2:U2000"
Private Sub cmdImport_Click()