472,371 Members | 1,503 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,371 software developers and data experts.

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 32182
32,511 Expert Mod 16PB

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
32,511 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
32,511 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

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

Similar topics

by: Shawn | last post by:
I have the basics working as is described in KB199841. I currently have my ASP page loading into Excel by setting the ContentType of the page: <%response.ContentType="application/vnd.ms-excel"%> ...
by: JMCN | last post by:
is it possible to export multiple queries into one workbook, having each query as separate worksheet? i cannot specify a range because the records will change on a daily basis as for query size. i...
by: Steve Chatham | last post by:
I need to export multiple datagrids (where we have a drill-through web application) into an Excel workbook. For instance, when you click on the first datagrid, you get a subset of the sql table. ...
by: Iris | last post by:
I have 8 text files (tab delimited) that I would like to import into an Excel workbook as 8 individual worksheets but I cannot find any example code on this subject. Can anyone help me please???? ...
by: lblanton | last post by:
I am trying to import an excel file with multiple worksheets into a single access table. The worksheets are formatted the same and with the same column headings but have varying rows. How do I import...
by: hal | last post by:
Hello all, I've been searching all day for an article or tutorial on how to get data from a SQL Server 2000 database and export the data to excel 2003 so that multiple worksheets are created,...
by: prinsipe | last post by:
Hi guys, i'm able to export data to an excel from my asp .net page, i can even create multiple worksheets. the problem is, i can not specify where worksheet should the data be placed. below is my...
by: forrestgump | last post by:
I want to be able to export multiple access query results to 1 excel worksheet so the results appear under each other. I need this to show the results of headcount, leavers and starters in a cost...
by: arjun007 | last post by:
i want to read multiple worksheets in a single program using php.i done it for one excel sheet by saving .csv file. but,iwant to read multiple worksheets.please help me.................
by: dafodil | last post by:
Hi I have a problem with exporting my produced datas to excel. I already tried using the xml.. where I create a html document and allow the user to download my excel file. The report created was...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.