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]).
I’m sure you work on lots of Excel data, MS Access and VBA, so I Strongly Recommend you to Join the below Ultimate Excel VBA Userform with Access Database! Its a worth Investment in Yourself! DO IT NOW!
Build Your Own Real World Program, Learn Real Excel VBA(Macros), Exceed The Limits of Excel with Complete Project
8 Responses
Sir I want to export access database records to excel using command button in vb6.0 please send me complete vb6.0 code
I have sheet1 on my excel but I get Run-time error 424 on the macro on the following step, why is that?
Sheet1.Range(“A1”).CopyFromRecordset rs
Hi , wanted to check if I keep my Access DB in shared drive and distribute this excel macro in my organisation, will it work ? I want my excel macro to read some values from this Access DB and then update some variable when opening excel .
Also if Access doesn’t work , whether I can use another excel kept in organisation one drive and access it instead of DB. I tried with workbook open method in my macro to read my onedrive excel which is shared to all in my company but it did not work .
Compile error in line 5, 8, 9…
I finallly did.
i’m using 2 variables (textbox1 as value ‘currency, format R$0.000,00’ and textbox2 as ‘date’, format mm/dd/yyyy)
Code ↓
‘====================================================|
‘========================================== Kauê Vaz==|
‘====================================================|
Option Explicit
‘ Click Tools, References and select
‘ Microsoft ActiveX Data Objects 2.0 Library
Sub getDataFromAccess(strValue, strDate)
Dim DBFullName As String
Dim Connect As String, Source As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Dim mySQLVariable As String
Dim mySQLVariable_1 As String
Dim strSQL As String
mySQLVariable = strValue ‘Value from textbox1 (Currency format R$0.000,00)
mySQLVariable_1 = strDate ‘Value from textbox2 (Date format mm/dd/yyyy)
‘SQL code
strSQL = “SELECT * FROM tblDatabase WHERE ”
strSQL = strSQL & ” [Value] = ‘” & (mySQLVariable) & “‘ and ”
strSQL = strSQL & ” [Date] = ‘” & (mySQLVariable_1) & “‘ ”
‘ Database, select your database access
DBFullName = “C:\Users\Desktop\Databases\BD_PaynotProcess\bd_PaynotProcess.accdb”
‘ Open connection
Set Connection = New ADODB.Connection
Connect = “Provider=Microsoft.ACE.OLEDB.12.0;”
Connect = Connect & “Data Source=” & DBFullName & “;”
Connection.Open ConnectionString:=Connect
‘ Creat RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
‘ Search values using SQL Code variable strSQL
Source = strSQL
.Open Source:=Source, ActiveConnection:=Connection
‘ Importing columns from Database.
For Col = 0 To Recordset.Fields.Count – 1
Range(“A5”).Offset(0, Col).Value = Recordset.Fields(Col).Name
Next
‘ Importing lines from Database.
Range(“A5”).Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection =
ActiveSheet.Columns.AutoFit
End Sub
Regards
Can i import just some datas? Filtering by 2 variables?
Example:
X = 13/03
Y = ‘122315124’
Bring from the access bd just the lines that contains this variables?
Worked for me. Thank you very much!
This is the best query I have ever seen. Could you please help in adding a Where clause in the sql statement. I tried modifying your query but it throws error.