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


About Anson

I’m Anson, having versatile experience in Business Process Outsourcing, Finance & Accounting, Information Technology, Operational Excellence & Business Intelligence. I'm currently working in a MNC in Bangalore, India. Learning anything new has always been my passions and if its related to Business and Technology then, I love it. This website/blog is an outcome of my passion towards it and I have been blogging since 2012. Outside business and technology I’m a movie freak who spends hours together watching and learning Cinema.

3 Comments

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

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

Leave a Reply