VBA Code to Import Access Table /Query data to Excel

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.

XModule

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.)

import access table

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]).

import access table



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.

2 Comments

  1. Pingback: VBA Code to Import Access Table/Query data to E...

  2. Pingback: VBA code to Execute Access Query (with Image)

Leave a Reply