Export to Excel with Named Sheet

D

Dallas64

I have the following code in one of my report events:

DoCmd.OutputTo acReport, stDocName, acFormatXLS, "P:\Filename.xls", False

It works like a charm, except for one thing. When I open the file in Excel,
it give me an error message for each line indicating that I used an invalid
sheet name, and it re-names the sheet, "Recovered_Sheet1". Is there
something I can add to the code above that will produce a valid sheet name,
and then dump the data into a sheet with that name?

I am still in the dark ages with Access 97 (it talks with our legacy
databases), even though we have Office 2003 for Excel and Word.
 
C

Cyberwolf

Did you try the TransferSpreadsheet method. This may give you better results.

The format is

DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename,
filename[, hasfieldnames][, range]

You can get more detailed in the help file. I use this all the time without
any problems.
 
Top