DoCmd.OutputTo Method: Excel format that opens for Access 2003 and 2010

Question

The following code works on my Access 2010, but brings a compile error for users with Access 2003:

DoCmd.OutputTo acOutputReport, "trndOTrpt", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint

So I changed the output format to acFormatXLS (per this website's suggestion):

DoCmd.OutputTo acOutputReport, "trndOTrpt", acFormatXLS, "", False, "", , acExportQualityPrint

This opens on my Access 2010 (yet to test Access 2003) but only in the Excel 5.0/95 Workbook format. Is there a way I can open this in the normal 97-03 .xls format in both versions of Access?

Answer 1

Looking here it turns out that Access 2003 had fewer argument options. So my last 3 arguments, "", ,acExportQualityPrint created the problem, hence the full error dialog "Compile Error: Wrong number of arguments or invalid property assignment."

I initially tried the TransferSpreadsheet method, but getting it to export to Excel while letting the user choose save name and destination was getting too cumbersome. Reducing the arguments, OutputTo works great!