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.
Sub importAccessdata() 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 Sheet1.Range("A1").CopyFromRecordset rs rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing Exit Sub 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.)
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]).