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

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, whe开发者_JS百科n 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!

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

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.

Here is a solution which is working for me. Basically, it opens again the same file and simulates a "Save As" from the Office button with the compatibiliy of Excel 97-2003. However, it does hide any alerts and specifies to Excel to NOT check the compatibility which is generating a popup window preventing you to work silentely in batch mode.

'before that, my code was using Workbooks.Add method, saving and then closing.
'just add the following after your last operation, once your file is closed where
'of course "resultFile" is the workbook to be saved, and resultFileName the path

Application.DisplayAlerts = False
Set resultFile = Workbooks.Open(resultFileName)
resultFile.CheckCompatibility = False
resultFile.SaveAs Filename:=resultFileName, FileFormat:=xlExcel8
resultFile.Close
Application.DisplayAlerts = True

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.

just closing the alerts also works. Application.DisplayAlerts = False Workbooks.Add.SaveAs name, FileFormat:=56 * do modifications to the file if necessary Activeworkbook.close SaveChanges:=true Application.DisplayAlerts = True

'this script is created by me. But usable to vbscript ( vbs ).
UTILIZAR EL ARCHIVO INICIAL.XLSX COMO COMODÍN PARA CREAR UN ARCHIVO DATOS.XLS.

'Creado por Juan Fernando Arango Trujillo
Set app = CreateObject("Excel.Application")
app.Visible = False
app.DisplayAlerts = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set wb = app.Workbooks.Open("C:\Users\Juan\Documents\Inicial.xlsx") 
wb.SaveAs "C:\Users\Juan\Documents\Datos.xls",  -4143
wb.Close True
app.Quit
Set app = Nothing
Set fso = Nothing

'FIN DEL PROCESO DE CREACIÓN DE DATOS.XLS