In this article I will explain how you can export values from an excel worksheet to an existing 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:

Note: In order to export data from an excel worksheet to an existing access table , the first row of the range that will be exported must contain the names of the fields each column is being exported to.

Assume we want to append data from the range “A2:D11” to an existing access table:

The access table already contains the following data:

The code below will append the data in the range “A2:D11” to the access table with the name “MyTable1”.

Note: The first row defines the field names and will not be appended:

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 " & _
"Existing 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:

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

• Worksheet to Existing Access Database.Zip

See also:

• Excel VBA, Export Worksheet to Blank 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