Excel – SaveAs FileFormat argument in Excel isn’t version-dependent

excelexcel-2007file-formatvba

Starting in Excel 2007 it is recommended that you provide a FileFormat argument to the Workbook.SaveAs command because even if you specified a .xls filename but without a FileFormat argument, it will make the file corrupt and unable to be read in Excel 2003 since the newly saved file will take the format of the ActiveWorkbook (which would be 2007).

In order to save in Excel 2003 compatible format, it is suggested to use these following FileFormat values:
–4143 if in Excel 2003 and 56 in Excel 2007 as so:

If Val(Application.Version) < 12 Then
  ' You use Excel 97-2003
  FileExtStr = ".xls": FileFormatNum = -4143
Else
  ' you use excel 2007 or later
  FileExtStr = ".xls": FileFormatNum = 56
End If

However i tried using -4143 regardless if the executing client was Excel 2003 or 2007 and it worked fine. Can anyone confirm if this is the case?

Source: Use VBA SaveAs in Excel 2007-2010

Best Answer

If you're using Excel 2003 to save a 2003 format file, then there's no need to specify the file format at all, since the default would be to save 2003 format anyway. The Application.Version code in the question works fine -- but no need to specify the FileFormat.

Unless you're using the 2007 document converters in 2003, in which case I don't know how 2003 would play that card, but I expect it'll choose the default (56) unless you specify otherwise.

NB: I certainly can't specify xlFileFormat.xlExcel8 in 2003, as suggested above, so that's not much help.

Related Topic