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
It contains worksheets of cells arranged by rows and columns as well as embedded macros programmed in the Visual Basic for Applications (VBA) language. XLSM files are saved in the Open XML format introduced in Microsoft Office 2007. XLSM files are . XLSX files with macros enabled.
read more to Save As the file in the regular worksheet is the F12 key. In VBA, too, we can save the file as “Save As.”
A quick way to save a file in VBA In this case, you have to give a path and file name where the program should save the file. In this code, the file name is given with the XLSX extension. You can drop it because there is specified the file format code: 51. This code means that the file will be saved in this format.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With