Export query results to multiple Excel sheets

[jbphoenix]

Is it possible to export query results to different excel files. I have several queries that need to go to 5 different excel files. The results of each query need to be on each excel sheet. Currently I'm using the transferspreadsheet command but that way I have to call the query each time.

 

[HiTechCoach]

Yes, it is possible to do with Excel Automation.

Here is the basics using DAO:

*** AIR CODE --- Untested ****

' using late binding so you do not need to set a reference to the Excel lib

Dim rsQry as DAO.recordset

Dim objExcel As Object
Dim objWB As Object
Dim objWS As Object

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

' create a workbook 

Set objWB = objExcel.Workbooks.Add

Set objWS = objWB.ActiveSheet

' copy recordset

Set rsQry = CurrentDB.OpenRecordset("qryMyQuery1")

objWS.Range("A1").CopyFromRecordset rsQry

rsQry.Close

objExcel.UserControl = True

Hopefully this will get you started.