Export Excel data to Access with VBA (Images and Code)
What is it?
This VBA code helps you to upload or export Excel data to Access in one click.
Why is it?
You can export Excel data to Access without even opening the Access DB.
How to Export Excel data to Access
Step 1: Open the Excel sheet and got to VBA code builder (Alt + F11) and Open a New Module
Step 2: Copy and Paste the below code in it.
Const TARGET_DB = “myDB.accdb” ‘Replace the ‘myDB.accdb’ with your db name, make sure both Excel and Access DB are at the same path.
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim i As Long, j As Long
Dim Rw As Long
Sheets(“Sheet Name”).Activate ‘Replace the ‘Sheet Name’ with your Excel Sheet name
Rw = Range(“A65536”).End(xlUp).Row
‘Give your Excel Sheet data Range
Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
.Provider = “Microsoft.ACE.OLEDB.12.0”
‘Replace the ‘tblMyExcelUpload’ with your table name in Access
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=”tblMyExcelUpload”, ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
‘Load all records from Excel to Access.
For i = 2 To Rw
For j = 1 To 7
rst(Cells(1, j).Value) = Cells(i, j).Value
‘ Close the connection
Set rst = Nothing
Set cnn = Nothing
Step 3:Click the Run button or F5.(Also you can insert a button/shape in excel sheet then right click and assign this macro to run everytime.) You are done with uploading!! Excel records have been successfully uploaded to Access, Open your Access DB Table and check the data.
Note: Make sure ‘Microsoft ActiveX Data Objects Library’ is enabled from the Tools – References (number use latest version [6.1 as of this post]).