How do I export several queries to ONE Excel workbook?

[youngborg]

I know I have done this successfully in the past, but I can't get it right all of a sudden!!

I used "TransferSpreadsheet" in my macro and the output file as C:\NewData.xls !MySheet1

The file exports as an unknown format with !MySheet1 included in the file name. I want that to be the first sheet's name in the workbook, and then I would like to run the same action for various other queries into the same workbook.

Can anyone help me?

Sincerely frustrated,

Erica-ann

 

[Norie]

Erica-ann

As far as I know you can't export a query to a specific sheet in a workbook.

If you export to an existing workbook Excel creates a new sheet in the workbook and names it after the exported query.

 

[youngborg]

Thanks Norie,

It worked like a charm!!

Erica-ann

 

[mdmilner]

There's also this type of technique...transfering recordsets instead of queries.

http://www.mvps.org/access/modules/mdl0035.htm

And as a suggestion, there's nothing stopping you from transferring your query, then renaming your worksheet via code.

Mike