File Format for Excel 2003 help needed

[RickK]

Excel 2003

I have a macro that opens a CSV file and removes a few columns and rows and then saves the new file as an .XLS . At least that is what I want it to do. It continues to fail on the save part of the macro.

If I run this macro on a PC with excel 2013 it works fine. And I can take a copy of the new file and open it on the excel 2003 PC and it opens fine. I don't want to keep running the macro on one PC and move the resulted file to the other PC.

Here is my code:

Application.DisplayAlerts = False 'IT WORKS TO DISABLE ALERT PROMPT
    ActiveWorkbook.SaveAs Filename:="C:\ABC\Inventory" & ".xls" _
        , FileFormat:=56, CreateBackup:=False
      Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS
      
    ActiveWindow.Close

I have also tried:

Application.DisplayAlerts = False 'IT WORKS TO DISABLE ALERT PROMPT
    ActiveWorkbook.SaveAs Filename:="C:\ABC\Inventory.xls" _
        , FileFormat:=56, CreateBackup:=False
      Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS
      
    ActiveWindow.Close

Tried this one but no luck:

Application.DisplayAlerts = False 'IT WORKS TO DISABLE ALERT PROMPT
    ActiveWorkbook.SaveAs Filename:="C:\CER\Inventory" & ".xls" _
        , FileFormat:=xlExcel8, CreateBackup:=False
      Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS
      
    ActiveWindow.Close

I have tried changing the format number and tried it without the FileFormat:= altogether but nothing helps.

Am I using the wrong number?

 

[Ranman256]

even tho you save as .xls , the format is saving as a newer version than the Excel can read.
save it backwards to an older format, either
acSpreadsheetTypeExcel12 ( 9 ) ,or acSpreadsheetTypeExcel3 (0)
dont use xlExcel8 (56)

docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12 ,table,filename,true,sheetname