When exporting to Excel from Access the name of the workbook (spreadsheet) is determined by the
FileName parameter and the name of the worksheet is determined by the
TableName parameter. In other words, the name of the table or query is
also the name of the sheet the data is put into. If multiple such commands all refer to the same workbook, but use different queries, then the results will all go in under their own matching worksheet names and you will have a multi-sheet workbook.
It's a sometimes unfortunate limitation that the worksheet name is not able to be specified separately from the query/table name, but it can be got around by copying or creating a QueryDef (saved query) that matches what you want but with a name specific to what you want to see as the worksheet name. Be careful of simply renaming any existing objects though, even if you will rename them back afterwards, as this approach leaves your database without the ability to be used safely in multi-user mode.
- Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
-
TableName:="qry1", _
-
FileName:="C:\Excel\WBMulti.XLS")
-
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
-
TableName:="qry2", _
-
FileName:="C:\Excel\WBMulti.XLS")
-
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
-
TableName:="qry3", _
-
FileName:="C:\Excel\WBMulti.XLS")
This leaves a spreadsheet file C:\Excel\WBMulti.XLS with three worksheets called qry1, qry2 & qry3.
63
