Output to specific Excel worksheet

[Goodrich]

I currently have three Access queries that I want to export to one Excel
spreadsheet, however I want each of the three queries to be on it's own
worksheet within the same workbook. When using the OutputTo action within an
Access macro, is there a way to specifiy the worksheet name in the Output
File path or is there a more suitable action that would enable me to
accomplish this task?

 

[Ken Snell \(MVP\)]

Use the TransferSpreadsheet action instead. The worksheet name will be the
same as the query being exported.

 

[Goodrich]

I noticed your response states the worksheet will inherit the name of the
"QUERY" being exported, however, can the TransferSpreadsheet action be used
to export a "QUERY" or will it only work when exporting "TABLES"? Two of the
three items I am attempting to export are CrossTab queries and I could not
get the TransferSpreadsheet action to work with them, however I can easily
create a Make Table Query and then use the TransferSpreadsheet action on the
resulting tables if that is necessary.

 

[Ken Snell \(MVP\)]

TransferSpreadsheet will export a query or a table. Don't recall hearing
that a crosstab query cannot be exported via TransferSpreadsheet, but it is
possible that it cannot. You don't need to create a table for the crosstab
query's output, though; just create another query that selects all the
records from the crosstab query, then export this new query.