How to Export into Multiple Worksheets in Same Workbook

[michelle copper]

How can I export 4 to 5 queries into a single Excel workbook but in separate sheets?

[NeoPa]

Michelle,

It is not ok to ask unrelated questions in the same thread, even if the thread is about one of your questions. This has been moved to it's own thread from Exporting Access Pivot tables to Excel (in case anyone is interested to see the lead-up to it.

[NeoPa]

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\WBMult i.XLS with three worksheets called qry1, qry2 & qry3.

[michelle copper]

Hi NeoPa!
Sorry about that and thank you so much for your answer to my question.

I tried and your code works perfectly. My only other question is that- do we have to state the FILENAME? What if I wanted Access to ask me where to save them to instead? like to my desktop or my documents...

[NeoPa]

In my example code the FileName parameter is set to a literal string, but a string variable could equally well be used instead.

If you want to ask a question about how one would go about setting that string in response to the operator's selection, please do so - but in its own thread of course. I'd be happy to help when I see it.

[michelle copper]

okay sure! I will do that right away and I will wait for your response... Thanks!