Rename Sheet1 on Excel Export

[Tom]

Hi

Am using Access 2002 and running the following query to export data to Excel
2002:

DoCmd.OutputTo acOutputQuery, "qryTestDbXls", acFormatXLS,
"C:\Temp\TestDb.xls", False

This works OK but sheet1, in Excel is also named qryTestDbXls and I would
like it to be called TestA.

How can this be achieved?

TIA

Tom

 

[Klatuu]

Use the TransferSpreadsheet method rather than the OutputTo.

The last argument is Range. Help says using in for an export will fail, but
this is not true. Put the name you want for the sheet in that arguement.

 

[Ken Snell \(MVP\)]

Additional info per Klatuu's suggestion. This article will give you some
information about some "tricks" for exporting data via TransferSpreadsheet:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

 

[Klatuu]

Thanks, Ken.
I consider Alex the authority on the subject.

 

[Tom]

Many thanks to you both works a treat!

Tom

Thanks, Ken.
I consider Alex the authority on the subject.