469,366 Members | 2,198 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

How to Export into Multiple Worksheets in Same Workbook

How can I export 4 to 5 queries into a single Excel workbook but in separate sheets?
Aug 25 '11 #1
5 30713
NeoPa
32,185 Expert Mod 16PB
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.
Aug 25 '11 #2
NeoPa
32,185 Expert Mod 16PB
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.

Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
  2.                                TableName:="qry1", _
  3.                                FileName:="C:\Excel\WBMulti.XLS")
  4. Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
  5.                                TableName:="qry2", _
  6.                                FileName:="C:\Excel\WBMulti.XLS")
  7. Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
  8.                                TableName:="qry3", _
  9.                                FileName:="C:\Excel\WBMulti.XLS")
This leaves a spreadsheet file C:\Excel\WBMulti.XLS with three worksheets called qry1, qry2 & qry3.
Aug 25 '11 #3
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...
Aug 25 '11 #4
NeoPa
32,185 Expert Mod 16PB
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.
Aug 25 '11 #5
okay sure! I will do that right away and I will wait for your response... Thanks!
Aug 26 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Steve Chatham | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.