4

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!

apaderno
  • 28,547
  • 16
  • 75
  • 90
ABB
  • 557
  • 2
  • 8
  • 12
  • I exactly have the same issue. A my_file_with_macro.xlsm file generating some *.xls files to be compatible with 2003 excel which cannot be opened by my colleagues who still run under Office 2003. Did you get any solution since you asked this question here? – Nicolas C. Apr 26 '12 at 09:16
  • Actually, I just found a solution (which I do not really find clean but... anyways, it is working for me). Application.DisplayAlerts = False Set resultFile = Workbooks.Open(resultFileName) resultFile.CheckCompatibility = False resultFile.SaveAs Filename:=resultFileName, FileFormat:=xlExcel8 resultFile.Close Application.DisplayAlerts = True – Nicolas C. Apr 26 '12 at 09:35

6 Answers6

7

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
burnside
  • 2,551
  • 23
  • 20
  • I have the compatibility pack installed and it still doesn't work. Not sure why. If I take out the FileFormat:=56 it saves fine but when I add that in it simply doesn't save at all. Any ideas? – ABB Jan 26 '10 at 22:26
  • What happens if you try to "save as" under a different file name? – Jay Jan 26 '10 at 22:35
  • I just tried that and it did not work. I stepped through the code and made sure it hit that line of code and it did so I'm not sure if it has to do with my current setup on my machine or what. I'm at a loss. – ABB Jan 26 '10 at 22:42
  • The machine I'm testing it on has both 2003 and 2007 installed (I ran the code in 2007), but I'm not sure that would make a difference. – burnside Jan 26 '10 at 22:51
  • I tried it on another machine with a similar setup as mine (both 2003 and 2007 installed) and it worked. Very weird. Sorry to waste your time, I guess it's a just an issue with my machine's setup for some very odd reason. – ABB Jan 26 '10 at 23:05
  • No probs. Excel can be a temperamental beast. – burnside Jan 26 '10 at 23:19
2

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
Nicolas C.
  • 235
  • 4
  • 14
1

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.

guitarthrower
  • 5,624
  • 3
  • 29
  • 37
0

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

user2481205
  • 46
  • 1
0

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.

Jay
  • 56,361
  • 10
  • 99
  • 123
  • Don't ask me why but the compatibility pack does not work. I'm trying to create an app to convert the files. – ABB Jan 26 '10 at 22:25
0
'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

Juan Fernando
  • 1