How to stop a Macro-Enabled Template from saving and adding a "1" to the file name

[AwesomeSteph]

Is there a setting or a macro that could disable the auto function of a macro-enabled template from adding a number?

I have a workbook that is used for every bit of work done in house, and is copied many times. I chose to use a template to discourage anyone from making edits to the original that everyone uses. I have a problem though that when a user goes to save the template as their workbook to use for their forms it adds a 1 to the end of the file name and since we are currently at revision 3 of the workbook it saves for the user as Rev31 (since the Rev3 has to be at the end of the file name per company policy).
Any bit of help on this is very much appreciated! I've been searching for months to try and fix this issue. I can't tell users to take off the 1 because people really like to tell me that they don't like doing extra work so I have been told to make it "user friendly"....

 

[Kenneth Hobson]

If they use a template (XLTM file type), a click means run New, not Open. What is in your autorun macro that causes the New copy to be saved as a template file?

 

[AwesomeSteph]

Original is a template that users go to and save as a workbook. I don't want the workbook to end with a "1" as excel auto inputs when saving from the template. users do not save as template they save as workbook.

 

[Kenneth Hobson]

If your file type is not a template, then it is not a template. Problems like that will occur.

If your file type is an XLSX or XLSM then it is not a template but more of a boiler plate sort of thing I guess. As such, a Save As will cause Windows to use its naming convention when the file is in Read Only status. Users will just have to learn how Windows operating system works, or you have to do the work for them in a macro.

If you are already saving it for them, please paste the code.

 

[AwesomeSteph]

I don't think you understand my question.

I have a template that I maintain and is used as a template, all users open the template and save out into their file as a workbook (yes it is a boilerplate but that is irrelevant).
When a user opens the .xltm (template) and saves it saves as an Excel Macro-Enabled Workbook .xlsm all this works as needed and I don't have a macro for that it is how it saves. the problem I have is that when it saves it adds a sequential number to the file name that is what I don't want to happen.

 

[jkpieterse]

It is by design that Excel adds a 1 after the original name of a template when opened. There is nothing you can change about that, except writing VBA code in the Workbook_BeforeSave routine to control how the saving process works. Note that properly writing that code is tricky!

 

[AwesomeSteph]

It is by design that Excel adds a 1 after the original name of a template when opened. There is nothing you can change about that, except writing VBA code in the Workbook_BeforeSave routine to control how the saving process works. Note that properly writing that code is tricky!

I understand, that is why I came here cause I'm struggling with it on my own.

 

[jkpieterse]

Something like this:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sName As String
    Dim sSaveName As String
    Dim bSave As Boolean
    If SaveAsUI And Len(ThisWorkbook.Path) = 0 Then
        sName = ThisWorkbook.Name
        sName = Left(sName, Len(sName) - 1)
        sSaveName = Application.GetSaveAsFilename(sName, "Microsoft Excel files (*.xlsm), *.xlsm", , "Save changes")
        If Len(sSaveName) > 0 Then
            If Len(Dir(sSaveName)) > 0 Then
                bSave = (MsgBox("File '" & sSaveName & "' already exists, overwrite?", vbQuestion + vbYesNo, "File exists") = vbYes)
            Else
                bSave = True
            End If
            If bSave Then
                Application.EnableEvents = False
                ThisWorkbook.SaveAs sSaveName, xlOpenXMLWorkbookMacroEnabled
                Application.EnableEvents = True
            End If
            Cancel = True
        End If
    End If
End Sub

 

[Kenneth Hobson]

I see. So you don't like the unique name that Excel uses for a New workbook based on the template file? This is how the normal New workbooks work. It is not saved yet though.

If you want to save it for the user at Open, where did you want to save it to and what is the filename? Or, did you want to show a FileSave dialog with a suggested path and filename?

 

[AwesomeSteph]

I see. So you don't like the unique name that Excel uses for a New workbook based on the template file? This is how the normal New workbooks work. It is not saved yet though.

If you want to save it for the user at Open, where did you want to save it to and what is the filename? Or, did you want to show a FileSave dialog with a suggested path and filename?

The user will pick where it goes (as one is needed for every work packet) but the name should remain the same as the original template just not a template "BoilerPlate Rev3" not "BoilerPlate Rev31".
I like the default settings that force opens the save location but if I have to tell the user to delete the "1" at the end there will be a huge problem (aleady is).