Exporting 2 queries to 1 excel workbook

[padinka]

How can I export two queries in Access 97 to excel as sheet1 and sheet2 in the workbook???<br><br>Thanks in advance.

 

[WP]

This cannot be done in the normal way.&nbsp;&nbsp;See help on TransferSpreadsheet method.&nbsp;&nbsp;It can however be done using VBA code.&nbsp;&nbsp;&nbsp;It is a lot of work for just megring two spreadsheets.&nbsp;&nbsp;If you really want to do this I'll look out some code for you.<br><br>WP <p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[GDGarth]

I've done something similar.&nbsp;&nbsp;This might be clumsy, but it does work.<br><br>Export each query to a different Excel workbook (Q1.xls & Q2.xls), then create a new workbook (or template) with two worksheets.&nbsp;&nbsp;<br><br>In sheet1 of the new workbook, write a simple formula to put the contents of cell A1 from Q1.xls into cell A1.&nbsp;&nbsp;Copy that cell, and paste it into the entire range you want to see.&nbsp;&nbsp;Do the same thing with sheet2 and Q2.xls.<br><br>If you have a lot of data, you probably want to convert all the formulas to values.&nbsp;&nbsp;You can easily record a macro to do that (just copy and past special (values).&nbsp;&nbsp;The have the macro do &quot;save as&quot; so you don't write over your original formulas.<br><br>This isn't an elegant solution, but it does work.<br><br>Hope it helps.

 

[OhBother30]

I have a database where I am doing this. Here is part of my code:

DoCmd.TransferSpreadsheet acExport, 8, &quot;Past Due Report&quot;, &quot;Fata.xls&quot;, True, &quot;&quot;
DoCmd.TransferSpreadsheet acExport, 8, &quot;Officer Delq Report&quot;, &quot;F:\Data.xls&quot;, True, &quot;&quot;
DoCmd.TransferSpreadsheet acExport, 8, &quot;Future Maturing Report&quot;, &quot;Fata.xls&quot;, True, &quot;&quot;

I end up with the workbook Data.xls. It contains three spreadsheets. Each sheet has the name of the corresponding query.
Andrea