In this article I will explain how you can export values from an excel worksheet to a new blank Access table.
- The database is located in the path “D:\Stuff\Business\Temp”
- The database file has the name “NewDB.accdb”
Note: In early binding reference must be added to the Access Object Library. For more information please see the article below:
The next step would be to export the values from the worksheet to the access table. This is done through the Access.DoCmd.TransferSpreadsheet Method:
TransferType: The type of transfer going to be performed. In this example it is acImport.
SpreedSheetType: The worksheet type. For more information please see the link below:
TableName: The name of the table the values will be imported to.
FileName: The full path of the excel file. In this example it is assumed the excel file is located in the path “D:\Stuff\Business\Temp\Worksheet to access table.xlsm”
HasFieldNames: If this value is set to true, the top row of the selected range will be chosen as the field names.
Range: A valid string value that represents a range in the excel worksheet
For more information about the DoCmd.TransferSpreadsheet command please see the link below:
Assume we have the following data in the excel worksheet:
We want to export these values to the access database. We want the first row to become the field names of the table. The data will imported to to a new table with the name “MyTable1”:
Note: If the range was in a different sheet (for example sheet2) you could have used the following string expression:
You can download the file and code related to this article from the link below: