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

---

Step 1:

The first step would be to connect to an existing access database or create a new access database. In this example we will be connecting to an existing database. It is assumed:

• The database is located in the path “D:StuffBusinessTemp”
• The database file has the name “NewDB.accdb”

Early Binding:

Note: In early binding reference must be added to the Access Object Library. For more information please see the article below:

• VBA Access Object Library

Sub Example1()
'the path to create the new access database
Dim strPath As String
'an Access object
Dim objAccess As Access.Application

strPath = "D:StuffBusinessTempNewDB.accdb"
Set objAccess = New Access.Application
Call objAccess.OpenCurrentDatabase(strPath)
objAccess.Visible = True
End Sub


Late Binding:

Sub Example2()
'the path to create the new access database
Dim strPath As String
'an Access object
Dim objAccess As Object

strPath = "D:StuffBusinessTempNewDB"
Set objAccess = CreateObject("Access.Application")
Call objAccess.OpenCurrentDatabase(strPath)
objAccess.Visible = True
End Sub


---

Step 2:

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:

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:

• Microsoft MSDN: AcSpreadSheet Enumeration

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

• Microsoft MSDN: TransferSpreadsheet Method [Access 2003 VBA Language Reference]

---

Example:

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

Sub Example3()
'the path to create the new access database
Dim strPath As String
'an Access object
Dim objAccess As Access.Application
Dim strExcelPath As String

strPath = "D:StuffBusinessTempNewDB.accdb"
strExcelPath = "D:StuffBusinessTempWorksheet to " & _
"access table.xlsm"
Set objAccess = New Access.Application
Call objAccess.OpenCurrentDatabase(strPath)
objAccess.Visible = True
Call objAccess.DoCmd.TransferSpreadsheet(acImport, _
acSpreadsheetTypeExcel8, "MyTable1", strExcelPath, _
True, "A1:D11")
End Sub


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:

• Worksheet to Access Table.xlsm

See also:

• Excel VBA, Export Worksheet to Existing Access Table

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