VBA代码另存为.XLSM [英] VBA Code to Save As .XLSM

问题描述

需要协助添加命令以另存为.xlsm:-

Need assistance to add command to save as .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

推荐答案

要将工作簿另存为 .xlsm ，您需要以下文件格式

To save a Workbook as .xlsm you need the following file format

Excel 2007-2010启用宏的工作簿(.xlsm)-52-xlOpenXMLWorkbookMacroEnabled

Excel 2007-2010 Macro-Enabled Workbook (.xlsm) - 52 - xlOpenXMLWorkbookMacroEnabled

要将文件保存为选定的格式，您需要在保存时指定适当的格式.这可以通过在您的保存操作中添加 FileFormat:= 来完成.

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.

ThisWorkbook.SaveAs Filename:=Path & Filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled

在代码中添加了保存操作和 FileFormat 之后.

Below the addition of the save action and FileFormat to your code.

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

这篇关于VBA代码另存为.XLSM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！