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.
Sub PushTableToAccess()
Dim cnn As ADODB.Connection
Dim MyConn
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
With cnn
.Provider = “Microsoft.ACE.OLEDB.12.0”
.Open MyConn
End With
‘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, _
Options:=adCmdTable
‘Load all records from Excel to Access.
For i = 2 To Rw
rst.AddNew
For j = 1 To 7
rst(Cells(1, j).Value) = Cells(i, j).Value
Next j
rst.Update
Next i
‘ Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
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]).
6 Responses
rst(Cells(1, j).Value) = Cells(i, j).Value
code breaks here
———-
Error 3265
Item cannot be found in the collection corresponding to the requested name or ordinal
———-
How would the code look like if i have the spreadsheet and the db on different locations i.e paths?
Hello,
my code runs until
Sheets(“Sheet Name”).Activate
I have got the sheet selected but nothing further happens.
Could you advise?
Thank you
Is there any single command to uploaded the data like (Range.CopyFromRecordset) ?
Hi Krishnan, not sure, let me explore and will update you.