OutputTo: Report to Excel gives "The format in which you are attempting..." error.

[Fred Chamberlin]

OS: Windows XP SP3
Office 2007 Enterprise with SP2 (12.0.6557.50001) SP2 MSO(12.0.6544.5001)

When using DoCmd.OutputTo and attempting to output a report to Excel, I get the following error:

The format to which you are attempting to output the current object is not available.

DoCmd.OutputTo acOutputReport, strSource, acFormatXLSX, , False

where strSource is the name of the report.

Researching the error seems to indicate that outputting a report to Excel will only work if SP2 is installed, but I do have SP2 installed.

[TheSmileyCoder]

Do you have any fields in your report that might not be suitable for export to excel?

I have not myself used the export function much in 2003 (Since it cuts off memo fields longer then 255 chars), so its just a guess.

[Stewart Ross]

From a check of MSDN it seems you've found a known issue with A2007 (and 2010 - just tested it myself and got same problem). Only answer appears to be to specify acFormatXLS instead of acFormatXLSX. Staggering, really, that acFormatXLSX is not valid for A2007/2010!

You could try exporting the underlying query via the DoCmd.TransferSpreadsheet method instead. Otherwise you are stuck with xls format and therefore 65k rows maximum. Makes no sense at all, does it?

-Stewart