Force Save as XLSM While Maintaining File Structure

Question

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.

What is the value of `FileNameVal` when using the debugger after a user inputs to the text box? — enderland, Aug 24 '12 at 18:02
What happens when you change `FileFormat:=ThisWorkbook.FileFormat` to `FileFormat:=52`? — Siddharth Rout, Aug 26 '12 at 08:19
@enderland after saving it once, I find that `FileNameVal` will automatically add a suffix for me. So after initially saving it as ".../My Documents/Template(1).xlsm", when I select save as and type in "1111", it will give `FileNameVal` the value of "1111.xlsm" and save the file as ".../My Documents/1111.xlsm.xlsm" — engineerchange, Aug 30 '12 at 13:49
`ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=ThisWorkbook.FileFormat` Change this line to `ThisWorkbook.SaveAs Filename:=FileNameVal, FileFormat:=52` — Siddharth Rout, Aug 30 '12 at 13:55
@SiddharthRout Unfortunately on the initial save, when removing ".xlsm", it will include no extension. Therefore, saving it as ".../My Documents/Template(1)1". The ".xlsm" on that line appears necessary for the initial save. — engineerchange, Aug 30 '12 at 14:10
When you use `Application.GetSaveAsFilename` the extension will automatically get added. — Siddharth Rout, Aug 30 '12 at 14:15
The use of `Application.GetSaveAsFilename` is to make sure that .xlsm and only .xlsm is available to the user. — engineerchange, Aug 30 '12 at 14:29

score 6 · Accepted Answer · edited Aug 31 '17 at 16:33

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

I know this is a very old thread, but people still find it. The line `If FileNameVal = "False" Then 'User pressed cancel` fails if your locale settings are non English (in my case, Dutch). Use `cstr(False)` instead of `"False"` to prevent this. — Jzz, Jul 05 '17 at 07:55

Force Save as XLSM While Maintaining File Structure

1 Answers1

Linked