How to modify code for Excel 2007 to use SaveAs

Question

I have an excel template that uses a macro to save the file, so that the users maintain standardized file name formats. I created the code using Excel 2003. the code is as follows:

Sub SaveBook()
   Dim sFile As String
   sFile = "ConsolidatedDemand" & "_" & Format(Now(), "yyyy.mm.dd") & ".xls"
   ActiveWorkbook.SaveAs Filename:= "\\file location\" & sFile
End Sub

I have one user who uses Excel 2007. When they try to run the macro, they recieve an error: "The following features can not be saved in macro-free workbooks: VB project. To save a file with these features, click No, and then choose macro-enabled file type in the file type list. To continue saving as a macro-free workbook, click yes"

I tried chaing the file extension to ".xlsm" in the second line of code, but that yielded the same error message. Any other ideas of how I can modify this code so that it will work for Excel 2007 users?

Solution 1

(taken from: http://blogs.office.com/b/microsoft-excel/archive/2009/07/07/use-the-vba-saveas-method-in-excel-2007.aspx)

In Excel 2007, the SaveAs method requires you to provide both the FileFormat parameter and the correct file extension.

For example, in Excel 2007 this line of code will fail if the ActiveWorkbook is not an .xlsm file:

ActiveWorkbook.SaveAs "C:\ron.xlsm"

But this code will always work:

ActiveWorkbook.SaveAs "C:\ron.xlsm", fileformat:=52 

These are the main file formats in Excel 2007:

• 51 = xlOpenXMLWorkbook (without macro's in 2007, .xlsx)
• 52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, .xlsm)
• 50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro's, .xlsb)
• 56 = xlExcel8 (97-2003 format in Excel 2007, .xls)

Solution 2

Just incase you don't know, you can check which version the application is in the macro, that way you can determine if it should be saved as "xls" or "xlsm"

If Application.Version = "13.0" Then
    MsgBox "You are using Excel 2010."
ElseIf Application.Version = "12.0" Then
    MsgBox "You are using Excel 2007."
ElseIf Application.Version = "11.0" Then
    MsgBox "You are using Excel 2003."
ElseIf Application.Version = "10.0" Then
    MsgBox "You are using Excel 2002."
ElseIf Application.Version = "9.0" Then
    MsgBox "You are using Excel 2000."
ElseIf Application.Version = "8.0" Then
    MsgBox "You are using Excel 97."
ElseIf Application.Version = "7.0" Then
    MsgBox "You are using Excel 95."
Else
    MsgBox "You are using an unknown version of Excel: " & Application.Version
End If

Hope this helps.

Comments

[Tim Williams]

blogs.office.com/b/microsoft-excel/archive/2009/07/07/…