By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,281 Members | 1,916 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,281 IT Pros & Developers. It's quick & easy.

2 Queries Export to Excel in the same spreadsheet

P: n/a
I would like to export the output of 2 queries to excel namely:

1. QryProrationbyWBS_1
2. QryProrationbyWBS_2

However I want to do this using the same spreadsheet say sheet1 and
sheet2 or better still with sheet name "ProrationWBS1" and
"ProrationWBS2", how do I do this with acOutputQuery

I have the following 2 commands attached to a command button:
1) DoCmd.OutputTo acOutputQuery, "QryProrationbyWBS_1", acFormatXLS,
"C:\Documents and Settings\All Users\Desktop\Consumbles Report.xls",
False
2) DoCmd.OutputTo acOutputQuery, "QryProrationbyWBS_2", acFormatXLS,
"C:\Documents and Settings\All Users\Desktop\Consumbles Report.xls",
False

How do I combine both of the above outputs such that they both come on
a single spreadsheet

Many thanks for your help in advance
Edward

May 21 '06 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,669
I would like to export the output of 2 queries to excel namely:

1. QryProrationbyWBS_1
2. QryProrationbyWBS_2

However I want to do this using the same spreadsheet say sheet1 and
sheet2 or better still with sheet name "ProrationWBS1" and
"ProrationWBS2", how do I do this with acOutputQuery

I have the following 2 commands attached to a command button:
1) DoCmd.OutputTo acOutputQuery, "QryProrationbyWBS_1", acFormatXLS,
"C:\Documents and Settings\All Users\Desktop\Consumbles Report.xls",
False
2) DoCmd.OutputTo acOutputQuery, "QryProrationbyWBS_2", acFormatXLS,
"C:\Documents and Settings\All Users\Desktop\Consumbles Report.xls",
False

How do I combine both of the above outputs such that they both come on
a single spreadsheet

Many thanks for your help in advance
Edward
What you are describing can quite easily be implemented within Excel, not
Access, and possibly automated via an Excel Macro. Here it goes:
01) Select cell A1 in Excel
02) Select the Data Menu
03) Select Import External Data
04) Select Import Data
05) Select you Data Source (YourDatabase.mdb)
06) Select qryProbationWBS_1
07) Go
08) Make sure Existing Worksheet is selected with Cell = $A$1
09) Import Data
10) Repeate the process for qryProbationWBS_2 but with a different
starting Cell location. e.g. =$A$200

Hope this helps
May 21 '06 #2

P: n/a
>How do I combine both of the above outputs such that they
both come on a single spreadsheet


DoCmd.TransferSpreadsheet will do that for you, e.g.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"Query1", "d:\foo.xls"

May 21 '06 #3

P: n/a
Thanks, it worked

May 22 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.