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

Anson Antony

Anson Antony

Anson is a contributing author and founder at 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!

6 Responses

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

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

Launching soon...

E-book price at Amazon will be $14.99 

A Practical Guide To Online Business
You Made It,
Don't Close!

Enter your chance to win our Premium Membership License for FREE! ( Normal Price $199/Year)

Thank you for your visit!

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

Premium Membership Giveaway - Popup

Let's have a chat

Fill out the form below to consult with us

*Please check your Inbox or Junk Folder for the Confirmation Email. You must confirm your Company email address before we could revert.

Share to...