Import Access Table to Excel with VBA (Images and Code)
What is it?
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
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]).