VBA Code to Export Excel data to Access

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

.export Excel data to Access

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, _

‘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
Next j
Next i

‘ Close the connection
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.

export excel data to access

Note: Make sure ‘Microsoft ActiveX Data Objects Library’ is enabled from the ToolsReferences (number use latest version [6.1 as of this post]).

ADODB_export excel data to access


About Anson

I’m Anson having a decade of versatile experience in Business Process Outsourcing, Finance & Accounting, Information Technology, Operational Excellence & Business Intelligence. I have got the opportunity to work for Genpact, Hewlett Packard, M*Modal and Capgemini in various roles and responsibilities, starting from an Associate to a Manager. Learning anything new has always been my passion and if related to Business and Technology then I love it. asKeygeek.com is an outcome of my passion towards the same and I have been into asKeygeek since 2012. Outside business and technology I’m a movie buff who spends hours together watching and learning Cinema, I do make Short Films 🙂


  1. Hello,

    my code runs until

    Sheets(“Sheet Name”).Activate

    I have got the sheet selected but nothing further happens.

    Could you advise?

    Thank you

  2. Is there any single command to uploaded the data like (Range.CopyFromRecordset) ?

  3. Pingback: Export Excel data to Access with VBA (Images an...

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.