CODEWITHSUNDEEP
excelvba
Albert Zahra
Albert Zahra

Reputation: 83

VBA Code to Save As .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

Upvotes: 8

Views: 69508

Answers (4)

Frain Technologies
Frain Technologies

Reputation: 1

Public Sub SAVE_WORKBOOK()
    Dim ThisFile As String, newFile As String 'Variable definition.
    ThisFile = Range("A10").Value 'Get file name from cell.
    'Adding today's date in file name:
    newFile = ThisFile & " " & Format$(Date, "dd-mm-yyyy")
    ChDir _
    "C:\BACKUP" 'Naming the file path to save
    'Saving the file as xlsm:
    ActiveWorkbook.SaveAs Filename:=newFile & ".xlsm", FileFormat:=52
End Sub

For example, if Cell A10 is written Frain and today's date is 09.11.2024 the file name would be Frain 09-11-2023.

Upvotes: 0

One Time I
One Time I

Reputation: 23

Different Fileformats for excel are:

.xlsx = 51 '(52 for Mac)

.xlsm = 52 '(53 for Mac)

.xlsb = 50 '(51 for Mac)

.xls = 56 '(57 for Mac)

ActiveWorkbook.SaveAs FileFormat:=52   '=.xlsm in Windows

Upvotes: 2

Annemoenchen
Annemoenchen

Reputation: 11

The solution is:
.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

Upvotes: 1

Miguel_Ryu
Miguel_Ryu

Reputation: 1418

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

Excel 2007-2010 Macro-Enabled Workbook (.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.

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

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

Upvotes: 17

Related Questions