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

I'm not clear on whether you are just trying to convert all those files or if you are working on an application that will need to do that.

If the former, see if this is helpful : http://www.microsoft.com/downloads/details.aspx?familyid=941b3470-3ae9-4aee-8f43-c6bb74cd1466&displaylang=en

It allows you to open Office 2007 in earlier versions of Office.

Answer 2

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

Answer 3

This page: http://www.rondebruin.nl/saveas.htm has been helpful for me. You have to declare a different file type if you are saving from 2007 to 2003 and earlier.