Import Access Table to Excel with VBA (Images and Code)
What is it?
This VBA code helps you to download or import Access Table data or Query to Excel in one click.
Why is it?
You can import Access table data to Excel without even opening the Access DB.
How to import Access table data to Excel
Step 1: Open the Excel Workbook and got to VBA code builder (Alt + F11) and Open a New Module.
Step 2: Copy and Paste the below code in it.
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String
strFilePath = “C:\DatabaseFolder\myDB.accdb” ‘Replace the ‘DatabaseFolder’ and ‘myDB.accdb’ with your DB path and DB name
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open “Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=” & strFilePath & “;”
sQRY = “SELECT * FROM tblData” ‘Replace ‘tblData’ with your Access DB Table name or Query name from which you want to download the data
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Set rs = Nothing
Set cnn = Nothing
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.)
Access DB records have been successfully downloaded to Excel, Open your Excel workbook and check the data.
Note: Make sure ‘Microsoft ActiveX Data Objects Library’ is enabled from the Tools – References (number use latest version [6.1 as of this post]).