Macro 92: Running an Access Query from Excel
Here’s a nifty macro for those of you who often copy and paste the results of your Microsoft Access queries
to Excel. In this macro, you use DAO (Data Access Object) to open and run an Access query in the
background and output the results into Excel.
How it works
In this macro, you point Excel to an Access database and pull data from an existing Access query. You
then store that query in a Recordset object, which you can use to populate your Excel spread-
sheet.
Because you are automating Access, you need to set a reference to the Microsoft Access Object
Library. To do so, open the VBE in Excel and select Tools➜References. The Reference dialog box
opens. Scroll down until you find the entry Microsoft Access XX Object Library, where the XX is your
version of Access. Select the check box next to the entry.
In addition to the Access Object Library, you need to set a reference to Microsoft DAO XX Object Library,
where the XX is the version number. Note that you may see multiple versions of this library in the
Reference dialog box. You should generally select the latest version of the Microsoft DAO Library avail-
able. While still in the Reference dialog box, select the check box next to the entry.
Sub Macro92()
‘Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
‘Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
(“C:\Temp\YourAccessDatabse.accdb”)
Set MyQueryDef = MyDatabase.QueryDefs(“Your Query Name”)
‘Step 3: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
‘Step 4: Clear previous contents
Sheets(“Sheet1”).Select
ActiveSheet.Range(“A6:K10000”).ClearContents
‘Step 5: Copy the recordset to Excel
ActiveSheet.Range(“A7”).CopyFromRecordset MyRecordset
‘Step 6: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i –
1).Name Next i
End Sub
1. Step 1 declares the necessary variables. The MyDatabase object variable exposes your
Access database application via the DAO Object Library. MyQueryDef is also an object
variable that serves as a memory container for the target query. MyRecordset is a
Recordset object that holds the results of the data pull. In addition to these, the i
integer variable is used to add column headings.
2. Step 2 specifies the database that holds your target query as well as which query will be
run. Assigning the query to a QueryDef object allows you to essentially open the query in
memory.
3. Step 3 literally runs the query in memory. The results of the query are then stored into the
MyRecordset object. After the results are in a recordset, you can output the data to Excel.
4. Step 4 prepares for the recordset output by clearing the output area. This ensures no residual
data is left from previous data pulls.
5. This step uses Excel’s CopyFromRecordset method to get the returned dataset into the
spreadsheet. In this example, the macro copies the data in the MyRecordset object onto
Sheet1 at cell A7.
6. Finally, you enumerate through each field in the recordset to automatically get the name of
each header and enter it into Excel.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code into the newly created module.
