VBA Code to Export Excel data to Access

Share on facebook
Share on twitter
Share on linkedin

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

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


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!

4 Responses

  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

Leave a Reply

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

small_c_popup.png

Let's have a chat

Fill out the form below to consult with us