How to Create a Macro to Run an Access Query & Paste the Result Into Excel

Creating a Microsoft Access macro that pastes a query's results into Excel can be done by linking to Access from Excel, or by applying macro code to directly control Excel from Access.

Note: all but the last three steps apply to users of Access (and Excel) 2007. If you have an earlier version of Access, proceed to the third to last step.

Things You'll Need

• Microsoft Excel and Access

Instructions

1. In Access, create a table of sample data: enter the following data in a new table:

   the accidental tourist,12/1/2009,$6.01
   the accidental tourist,12/3/2009,$7.98
   iron john,12/5/2009,$4.98
   iron john,12/6/2009,$5.98

2. Double-click the column headers (e.g. "Field1") and replace each with these headers, in this order:

   book,datesold,netsale

   Save the table ("control-s") with the name "books."

3. Create a query from the table, and press the "Esc" key in the "show table" dialog box. Right-click on the query's tab and select "SQL view." Enter the following in the code window:

   SELECT books.* INTO queryresults
   FROM books
   WHERE (((books.book) Like '*acc*'));

   Save the query ("control-s") and name it "vbaquery."

4. Open Excel and press the toolbar's "Data>From Access" icon. Select the "queryresults" table in the "Select Table" dialog box. Click "OK" on the "Import Data" dialog box and notice the query's results: only the "iron john" books are shown. Save the Excel file with any name, and close it.

5. Reopen the "books" database in Access. Open the "vbaquery" and revise its "Criteria:" field to read "Like '*acc*'" (Don't type the double quotes. Do type the inner, single quotes.) Resave the query.

6. Create a new query. Type the following SQL statement in the "SQL view" window, then save the query as "dropqueryresults":

   DROP TABLE queryresults;

7. Enter the Visual Basic integrated development environment (IDE) by pressing "alt-f11," then select "Insert>Module." Paste the following code into the new module's blank code window:

   Public Sub runquery()
   'delete the results table first
   On Error GoTo DO_QUERY
   RunQueryForExcel ("dropqueryresults")

   DO_QUERY:
   RunQueryForExcel ("vbaquery")
   End Sub

   Public Sub RunQueryForExcel(qName As String)
   DoCmd.SetWarnings False
   CurrentDb.Execute qName
   DoCmd.SetWarnings True
   End Sub

8. Position the cursor anywhere in the "runquery" subroutine and press "F5" to run the query. Reopen the Excel workbook you previously opened and notice the updated data: your macro has replaced the "iron john" rows with the "accidental tourist" rows. (Access 2007 or later users can stop here.)

9. (For users of Access 2003 and earlier). Use step 7 to paste the following code into a new module in the Visual Basic IDE:
   '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   Public Sub pasteToExcel()

   Const qName = "vbaquery"
   Dim db As DAO.Database
   Dim recset As DAO.Recordset
   Dim s As String
   Dim appXL As Excel.Application
   Dim ro, co

   '''''''''''''''''''''''''
   Set appXL = CreateObject("Excel.Application")
   appXL.Workbooks.Add

   Set db = CurrentDb
   Set recset = db.OpenRecordset(qName)
   s = "book" & ", " & "dateddsold" & ", " & "netsale" & vbCr
   appXL.ActiveSheet.Cells(1, 1) = s
   ro = 2
   co = 1
   s = ""
   Do While Not recset.EOF
   s = s & recset![book] & ", " & recset![datesold] & ", " & recset![netsale] & vbCr
   appXL.ActiveSheet.Cells(ro, co) = s
   recset.MoveNext
   ro = ro + 1
   s = ""
   Loop
   recset.Close
   db.Close
   appXL.ActiveWorkbook.SaveAs ("c:\dataFromAccess.xls")
   appXL.Quit

   End Sub
   '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   Select "Tools>References" and check the "Microsoft Excel Objects Library."

10. Return to Access and do steps 1 to 3. However, for step 3, paste this SQL code into the SQL code window:

    SELECT books.*
    FROM books
    WHERE (((books.book) Like '*acc*'));

11. Return to the Visual Basic IDE. Place the cursor inside the "pasteToExcel" function and press "F5" to run the function. Open the Excel file "c:\dataFromAccess.xls" to view the results.

References

• Microsoft Online: Export data to Excel
• Microsoft Office Development Center: Controlling One Microsoft Application from Another