vba 在保持文件结构的同时强制另存为 XLSM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12111491/
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
Force Save as XLSM While Maintaining File Structure
提问by engineerchange
So I am working with a XLTM file, and I want the user to make sure they save as XLSM. When they click "Save," this works fine, but I find when they click "Save As," the file is saved as "*.xlsm.xlsm". I am a little lost with how to make sure that the user saves as XLSM, while keeping the file name as "filename.xlsm" and not "filename.xlsm.xlsm".
所以我正在处理一个 XLTM 文件，我希望用户确保他们保存为 XLSM。当他们单击“保存”时，这工作正常，但我发现当他们单击“另存为”时，文件被保存为“*.xlsm.xlsm”。我对如何确保用户保存为 XLSM，同时将文件名保持为“filename.xlsm”而不是“filename.xlsm.xlsm”有点迷茫。
'Action makes sure the user saves as XLSM file type.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FileNameVal As String
If SaveAsUI Then
FileNameVal = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
Cancel = True
If FileNameVal = "False" Then 'User pressed cancel
Exit Sub
End If
Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=ThisWorkbook.FileFormat
Application.EnableEvents = True
End If
End Sub
I thought the problem may have been writing ".xlsm" in:
我认为问题可能是在以下位置写入“.xlsm”：
ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=ThisWorkbook.FileFormat
However, without ".xlsm" written there, I find the file instead saves as a bad file suffix. (E.g., if my XLTM file is called Template(File001).xltm, and the user opens a new template file, it will save as Template(File001)1 (believing that "1)1" is the file type).
但是，没有在那里写“.xlsm”，我发现该文件保存为错误的文件后缀。（例如，如果我的 XLTM 文件名为 Template(File001).xltm，并且用户打开一个新的模板文件，它将保存为 Template(File001)1（认为“1)1”是文件类型）。
It may be the structure of my code, so I need direction in how to revise it.
这可能是我代码的结构，所以我需要指导如何修改它。
回答by engineerchange
The problem appeared to have existed because the template would name the file "Template(1)1" prior to it actually being saved initially. This changes the way that Excel saves the file, so the easiest way to contrast between this initial save and further saves (that already contain a file extension) was to use an if-then statement to judge whether an extension exists already.
该问题似乎已经存在，因为模板会在最初实际保存文件之前将其命名为“Template(1)1”。这改变了 Excel 保存文件的方式，因此比较此初始保存和进一步保存（已包含文件扩展名）的最简单方法是使用 if-then 语句来判断扩展名是否已存在。
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FileNameVal As String
If SaveAsUI Then
FileNameVal = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
Cancel = True
If FileNameVal = CStr(False) Then 'User pressed cancel
Exit Sub
End If
Application.EnableEvents = False
If Right(ThisWorkbook.Name, 5) <> ".xlsm" Then
ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
Else
ThisWorkbook.SaveAs Filename:=FileNameVal, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End If
Application.EnableEvents = True
End If
End Sub
