Save As File Format

[jstevens]

Does anyone have a reference to VBA code that will identiy the Excel Application Version (application.version) and perform a file "save as" using the version of Excel that opened the file.

As an example: A file was created with Excel 2003 and subsequently is opened by another user with Excel 2007. There are instances where some folks do not have Excel 2007; just Excel 2003. I know there is a conversion utility for Excel 2003 to read Excel 2007 files but they may not have the software installed.

Regards,
John

[HansV]

Important: I modified the code below because the version I originally posted won't work (the constant for Excel 2007 should be 51 instead of 50 as I originally had. Sorry for the confusion!

Try something like this:

Code: Select all

Dim strFileName As String
strFileName = "Testing"
If Val(Application.Version) < 12 Then
  ' Excel 2003 or before
  ActiveWorkbook.SaveAs strFileName & ".xls", 56 '=xlExcel8
Else
  ' Excel 2007 or later
  ActiveWorkbook.SaveAs strFileName & ".xlsx", 51 '=xlOpenXMLWorkbook
End If

You can't use the constants xlExcel8 and xlOpenXMLWorkbook because they would cause an error in versions before Excel 2007, so I used their numeric values.

[Sundog]

Hans, very handy, and good input re constants in early versions. Thanks.

[HansV]

Please note that I modified my previous reply since it contained an error!