Macro to copy tables to excel

[Guest]

How can I have a macro that runs 12 queries and pastes the results of each
query onto a single sheet in a spreadsheet?. Each query result pasted
immediatly below the previous one.

thanks,


--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[Guest]

Correction: title should be "Macro to copy query results to excel"
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[John Nurick]

Hi Billy,

Can't be done in a macro unless you can build a single query that
returns all the data you want.

Otherwise, you'd need to write VBA code that launches Excel, opens the
workbook, and then
opens a recordset into the first query
gets the RecordCount and checks that there aren't too many
records to fit on the worksheet
uses Excel's Range.CopyFromRecordset method to place the
data on the worksheet
uses the recordset's RecordCount to calculate where to
place the next query's records
repeats for each of the remaining queries
before finally saving and closing the workbook.

 

[Steve Schapel]

Billy,

If you can make a Union Query, to add the results of your 12 existing
queries to each other, then the TransferSpreadsheet action in your macro
should do the trick.