DoCmd.OutputTo Creates Old Excel Version

[Guest]

Greetings! I am using Access 2000 and in VBA I have the following code:

DoCmd.OutputTo acOutputQuery, "PLAN", acFormatXLS, "MyFile.xls", True, ""

This code dutifully creates and opens a workbook with the results of my
query, but when I go to save the workbook, Excel informs me that it is in
"Excel 5.0/95" format and asks if I would like to save it in the current
format. I am using Access and Excel 2000 which makes me think that creating
the file in the "current format" would be automatic. I checked Tools /
References in Access VBA and I am using ADO 2.6. I also tried selecting the
Jet and Replication Objects 2.6 Library, but it did not make any difference.

Any ideas as to how I can get Access 2000 to create the file in Excel 2000
format.

Thanks in advance!

 

[Ken Snell [MVP]]

Use TransferSpreadsheet method of DoCmd.

 

[Guest]

Use the Transferspreadsheet method, and for spreadsheet type, use
acSpreadsheetTypeExcel9