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
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