另存为 .XLSM 的 VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48733454/
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
VBA Code to Save As .XLSM
提问by Albert Zahra
Need assistance to add command to save as .xlsm :-
需要帮助添加命令以另存为 .xlsm :-
Private Sub cmdSaveForm1_Click()
Dim strFolder As String
Dim i As Long
'Find the position of the period in the file name
i = InStr(ActiveWorkbook.Name, ".")
'Create a default file name by concatenating the file name without the extention _
plus the current date and time, and plus the xlsm extention
Filename = Left(ActiveWorkbook.Name, i - 1) & "_" & Format(Now, "yyyy-mm-dd_hh mm") & ".xlsm"
'Open Save As dialog to a default folder with default file name
With Application.FileDialog(msoFileDialogSaveAs)
.AllowMultiSelect = False
.InitialFileName = "P:\EU Funds Management - Treasury\TRS3_Abstract of Payments\TRS3_Authorisation_L1\" & Filename
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then strFolder = .SelectedItems(1) Else Exit Sub
.Execute
End With
End Sub
回答by Miguel_Ryu
To save a Workbook as .xlsmyou need the following file format
要根据.xlsm需要保存工作簿，请使用以下文件格式
Excel 2007-2010 Macro-Enabled Workbook (.xlsm) - 52 - xlOpenXMLWorkbookMacroEnabled
Excel 2007-2010 启用宏的工作簿 (.xlsm) - 52 - xlOpenXMLWorkbookMacroEnabled
To save a file to an chosen format you need to specify the appropriate format when saving. This can be done by adding FileFormat:=to your save action.
要将文件保存为所选格式，您需要在保存时指定适当的格式。这可以通过添加FileFormat:=到您的保存操作来完成。
ThisWorkbook.SaveAs Filename:=Path & Filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Below the addition of the save action and FileFormatto your code.
在添加保存操作和FileFormat您的代码下方。
Private Sub cmdSaveForm1_Click()
Dim strFolder As String
Dim i As Long
'Find the position of the period in the file name
i = InStr(ActiveWorkbook.Name, ".")
'Create a default file name by concatenating the file name without the extention _
plus the current date and time, and plus the xlsm extention
Filename = Left(ActiveWorkbook.Name, i - 1) & "_" & Format(Now, "yyyy-mm-dd_hh mm") & ".xlsm"
'Open Save As dialog to a default folder with default file name
With Application.FileDialog(msoFileDialogSaveAs)
.AllowMultiSelect = False
.InitialFileName = "P:\EU Funds Management - Treasury\TRS3_Abstract of Payments\TRS3_Authorisation_L1\" & Filename
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then strFolder = .SelectedItems(1) Else Exit Sub
'get selected folder path from FileDialog, but remove filename from FileDialog
folderPath = Left(strFolder, InStrRev(strFolder, "\"))
'Save this workbook in chosen file path & appropriate filename
'File format .xlsm
ThisWorkbook.SaveAs Filename:=folderPath & Filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End With
End Sub
回答by Annemoenchen
The solution is:
.FilterIndex = 2
1 = xlsx, 2 = xlsm
解决办法是：
.FilterIndex = 2
1 = xlsx, 2 = xlsm
Private Sub cmdSaveForm1_Click()
Dim strFolder As String
Dim i As Long
'Find the position of the period in the file name
i = InStr(ActiveWorkbook.Name, ".")
'Create a default file name by concatenating the file name without the extention _
plus the current date and time, and plus the xlsm extention
Filename = Left(ActiveWorkbook.Name, i - 1) & "_" & Format(Now, "yyyy-mm-dd_hh mm") & ".xlsm"
'Open Save As dialog to a default folder with default file name
With Application.FileDialog(msoFileDialogSaveAs)
.AllowMultiSelect = False
.FilterIndex = 2 '2 = xlsm
.InitialFileName = "P:\EU Funds Management - Treasury\TRS3_Abstract of Payments\TRS3_Authorisation_L1\" & Filename
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then strFolder = .SelectedItems(1) Else Exit Sub
.Execute
End With
End Sub
