VBA Code to Export Excel data to Access

VBA Code to Export Excel data to Access
Table of Contents

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 AccessPin

Picture of Anson Antony
Anson Antony
Anson is a contributing author and founder at www.askeygeek.com. Learning anything new has always been his passion, askeygeek.com is an outcome of his passion for technology and business. He has got a decade of versatile experience in Business Process Outsourcing, Finance & Accounting, Information Technology, Operational Excellence & Business Intelligence. During the tenure, he had worked for organizations like Genpact, Hewlett Packard, M*Modal and Capgemini in various roles and responsibilities. Outside business and technology, he is a movie buff who spends hours together watching and learning Cinema and a Film Maker too!

9 Responses

  1. Hello All;

    Must the these two path locations be the same for this ADODBConnection to work? That is the ThisWorkbook.Path and Target_DB.
    Command from above:
    ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

    Or, can the Workbook.Path be say
    “C:\MyExcelFiles\MyWorkbook.xlsx” & the Access data base TARGET_DB be in

    My code works when both the Excel WorkBook and Access DB reside in the same folder, but when they are in different folder (properly organized library structure, the code fails at the .Open MyConn point.

    Thanks All,

  2. 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

  3. Hello,

    my code runs until

    Sheets(“Sheet Name”).Activate

    I have got the sheet selected but nothing further happens.

    Could you advise?

    Thank you

Leave a Reply

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

You Made It,
Don't Close!

Get Up To 60,000 UberTTS Character Credits for Free!!!

This popup won’t show up to you again!!!

Share to...