vba 如何修改 Excel 2007 的代码以使用 SaveAs
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6111931/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to modify code for Excel 2007 to use SaveAs
提问by Jon
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:
我有一个 excel 模板，它使用宏来保存文件，以便用户维护标准化的文件名格式。我使用Excel 2003创建了代码。代码如下：
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"
我有一个使用 Excel 2007 的用户。当他们尝试运行宏时，他们收到一个错误：“以下功能无法保存在无宏工作簿中：VB 项目。要保存具有这些功能的文件，请单击否，然后在文件类型列表中选择启用宏的文件类型。要继续保存为无宏工作簿，请单击“是”
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?
我尝试在第二行代码中将文件扩展名链接到“.xlsm”，但这产生了相同的错误消息。关于如何修改此代码以使其适用于 Excel 2007 用户的任何其他想法？
回答by bouvierr
(taken from: http://blogs.office.com/b/microsoft-excel/archive/2009/07/07/use-the-vba-saveas-method-in-excel-2007.aspx)
（取自：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.
在 Excel 2007 中，SaveAs 方法要求您提供 FileFormat 参数和正确的文件扩展名。
For example, in Excel 2007 this line of code will fail if the ActiveWorkbook is not an .xlsm file:
例如，在 Excel 2007 中，如果 ActiveWorkbook 不是 .xlsm 文件，这行代码将失败：
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:
这些是 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)
- 51 = xlOpenXMLWorkbook（2007 年没有宏，.xlsx）
- 52 = xlOpenXMLWorkbookMacroEnabled（2007 年有或没有宏，.xlsm）
- 50 = xlExcel12（2007 年的 Excel 二进制工作簿，带或不带宏，.xlsb）
- 56 = xlExcel8（Excel 2007 中的 97-2003 格式，.xls）
回答by Harag
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"
以防万一你不知道，你可以检查应用程序在宏中的版本，这样你就可以确定它是否应该保存为“xls”或“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.
希望这可以帮助。
