Saving Excel from Access

[JT]

I have a macro in Access that I'm runnign and using the code below to save a
file in Excel. How do I save this file as Excel 2003. I have Excel 2007 on
my machine and when I try to open the file later in the code I get an error
message that says "External Table is not in the expected format.". The macro
is erroring out on the following line:

DoCmd.TransferSpreadsheet acExport, 8, "Sum_CN", DataFile, False

Any help would be greatly appreciated. Thanks...


With ExcelApp

.Workbooks.Add
.Application.DisplayAlerts = False
.ActiveWorkbook.SaveAs FileName:="C:\Test\Data File.xls"
.Application.DisplayAlerts = True

DataFile = "C:\Test\Data File.xls"

.ActiveWorkbook.Close (True)

ControlFile = "N:\Users\E77306\Control File.xls"
DoCmd.TransferSpreadsheet acExport, 8, "Sum_US", DataFile, False

End With

 

[Pendragon]

If you explicitly state the Excel file type, that should save the file as
Excel 97-2003 and not 2007.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Sum_CN",
DataFile, False

Ross