Exporting separate query results to same workbook diff worksheets

[Gary S]

I am trying to have separate query results export to the same Excel workbook
but have a separate sheet for each query result. I would like to do it
either in VBA or with a macro. I have tried the transferspreadsheet and it
does not work even though the following help data seems to suggest it should:

Microsoft Access creates a new spreadsheet when you export data from
Microsoft Access. If the file name is the same as the name of an existing
spreadsheet, Microsoft Access replaces the existing spreadsheet, unless
you're exporting to a Microsoft Excel version 5.0, 7.0, 8.0 or Excel 2000
workbook. In that case, Microsoft Access copies the exported data to the next
available new worksheet in the workbook. END OF QUOTE.

Any ideas? I would greatly appreciate your help!!!!

 

[Klatuu]

Use the Range argument of the TransferSpreadsheet to determine the sheet it
goes in. For example, if you entered "Bozac", it will create a sheet named
Bozac and put the data in that sheet. Help says it does not work when
exporting, but that is not correct.

 

[Ken Snell \(MVP\)]

Here is some code that I wrote for a poster a few months ago that will do
this type of export. You'll need to modify the code so that it will loop
through a list of months instead of a list of managers. Post back with
questions (and with more details) if you have problems.



Generic code to create a temporary query, get list of
filtering values, and then loop through the list to filter
various data and export each filtered query to separate
worksheets in a single EXCEL file
----------------------------------------------------------

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strFileName As String = "PutEXCELFileNameHereWithout.xls"
Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' give it a dummy SQL statement initially
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
' Get list of manager IDs -- note: replace my generic table and field
names
' with the real names of the employees table and the manager ID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of manager IDs and create a query for each ID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic names
with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable,
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
' Replace C:\FolderName\ with actual path
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strFileName & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
'End of code


Additional info per Klatuu's suggestion. This article will give you some
information about some "tricks" for exporting data via TransferSpreadsheet:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html