Access VBA, Import Excel Worksheet to Blank Table

In this article I will explain how you can import values from an excel worksheet to a new blank table in Access using VBA.


DoCmd.TransferSpreadsheet:

The values are imported using the DoCmd.TransferSpreadsheet Method:

Syntax:

Call DoCmd.TransferSpreadsheet(TransferType, SpreadSheetType, TableName, Filename, HasFieldNames, Range)

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. If the table does not exist it will be created.
FileName: The full path of the excel file. In this example it is assumed the excel file is located in the path “D:StuffBusinessTempWorksheet 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:


Example:

Assume we have the following data in the excel workbook:
Example1

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”:

Sub Example()
'the path to the excel workbook
Dim strExcelPath As String
strExcelPath = "D:StuffBusinessTempExcelData.xlsx"
'import data from excel
Call DoCmd.TransferSpreadsheet(acImport, _
acSpreadsheetTypeExcel8, "MyTable1", strExcelPath, _
True, "A1:D11")
End Sub

Result:
Result

Note: If the range was in a different sheet (for example sheet2) you could have used the following string expression:

"Sheet2!A1:D11"

You can download the file and code related to this article from the link below:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *