VBA Office 2007 Create 2003 Excel Sheet

Question

Hi,

Since a few days I'm using MS Access 2007 in a certian process. In my VBA code I use: CreateObject("Excel.Sheet")

In Access 2003 a document was created in 2000-2003 format. Now in Access 2007 VBA enforces the new format. This causes problems in some additional coding.

Any suggestions how to enforce older format (2000-2003) through VBA?

Kind Regards

Answer 1

You should call SaveAs method with its second parameter FileFormat as xlExcel8

Answer 2

You can also use the DoCmd.TransferSpreadsheet with a second param of acSpreadsheetTypeExcel8