excelfilenamesbefore-savevba

Force Save as XLSM While Maintaining File Structure


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.


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