Open excel 2007 excel files and save as 97-2003 formats in VBA

Question

I have a weird situation where I have a set of excel files, all having the extension .xls., in a directory where I can open all of them just fine in Excel 2007. The odd thing is that I cannot open them in Excel 2003, on the same machine, without opening the file first in 2007 and going and saving the file as an "Excel 97-2003 Workbook". Before I save the file as an "Excel 97-2003 Workbook" from Excel 2007, when I open the excel files in 2003 I get the error that the file is not in a recognizable format.

So my question is: if I already have the excel file opened in 2007 and I already have the file name of the open file stored in a variable, programatically how can I mimic the action of going up to the "office button" in the upper right and selecting, "save as" and then selecting "Excel 97-2003 Workbook"? I've tried something like the below but it does not save the file at all:

ActiveWorkbook.SaveAs TempFilePath & TempFileName & ".xls", FileFormat:=56

Thanks for any help or guidance!

Answer 1

It sounds like you should try the compatibility pack, but I can't see why your vba doesn't work. I tried the below and it worked perfectly:

ThisWorkbook.SaveAs "C:\test" & ".xls", FileFormat:=56