Force Save as XLSM While Maintaining File Structure

before-save, excel, filenames, vba

Solution

The problem appeared to have existed because the template would name the file "Template(1)1" prior to it actually being saved initially. This changes the way that Excel saves the file, so the easiest way to contrast between this initial save and further saves (that already contain a file extension) was to use an if-then statement to judge whether an extension exists already.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FileNameVal As String
If SaveAsUI Then
    FileNameVal = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
    Cancel = True
    If FileNameVal = CStr(False) Then 'User pressed cancel
        Exit Sub
    End If
    Application.EnableEvents = False
        If Right(ThisWorkbook.Name, 5) <> ".xlsm" Then
            ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Else
            ThisWorkbook.SaveAs Filename:=FileNameVal, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        End If
    Application.EnableEvents = True
End If
End Sub

Problem

So I am working with a XLTM file, and I want the user to make sure they save as XLSM. When they click "Save," this works fine, but I find when they click "Save As," the file is saved as "*.xlsm.xlsm". I am a little lost with how to make sure that the user saves as XLSM, while keeping the file name as "filename.xlsm" and not "filename.xlsm.xlsm". ``` 'Action makes sure the user saves as XLSM file type. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim FileNameVal As String If SaveAsUI Then FileNameVal = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm") Cancel = True If FileNameVal = "False" Then 'User pressed cancel Exit Sub End If Application.EnableEvents = False ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=ThisWorkbook.FileFormat Application.EnableEvents = True End If End Sub ``` I thought the problem may have been writing ".xlsm" in: ``` ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=ThisWorkbook.FileFormat ``` However, without ".xlsm" written there, I find the file instead saves as a bad file suffix. (E.g., if my XLTM file is called Template(File001).xltm, and the user opens a new template file, it will save as Template(File001)1 (believing that "1)1" is the file type). It may be the structure of my code, so I need direction in how to revise it.

Original source