How to name an excel file no matter what the user inputs

[lizziegirl]

Hello there,

I am creating an excel workbook template, which we are going to provide to Managers as an xlt file and store in our workgroup templates. Upon accessing it, it of course becomes an xls and the Manager makes changes to it. Upon the user going file save (which is what they do out of sheer habit) I would like the file to be named specifically no matter what the user inputs to ensure consistency. The file path does not matter.
I have the below code which creates a file as I specify, however this still allows the user to save a file named however they want it and I am ending up with two files.
Can I control this at all. I have trawled previous forums howver cannot find what I am specifically after.
Any ideas..? Thanks Lizzie

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
MyFileName = "GB Management Workbook - " & Sheets("GB").Range("e24").Text
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=MyFileName & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.DisplayAlerts = True
End Sub

 

[Ron Morris]

Hi Lizzie,

One way I have controlled this is buy making the original file (or template) "read only" so users cannot alter the name unless they use "Save As".

I have the users click on a save button which then names my file, similar to what you are doing in your code above.

It doesn't keep them from using the "Save As" option though.

I am new at VB myself and I am sure there is a better solution but this may be a start.

Ron

 

[Legacy 98055]

The complexity comes about because we are not enforcing the path, but only the name. I think this code will do the job though it should be improved. The filename should be validated, ect... Download the example if you have any issues with the code...

Save As Static Name.zip

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_BeforeSave(ByVal SaveAsUI <font color="#0000A0">As</font> Boolean, Cancel <font color="#0000A0">As</font> Boolean)
       <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">GoTo</font> Err_Workbook_BeforeSave

       <font color="#0000A0">Dim</font> BuildFullName <font color="#0000A0">As</font> String, ImposedFileName <font color="#0000A0">As</font> String, UserSelectedName <font color="#0000A0">As</font> <font color="#0000A0">String</font>

      <font color="#008000"> 'check to see if the saveas dialog will be shown</font>
      <font color="#008000"> 'this dialog must be shown in order to change the readonly name</font>
      <font color="#008000"> 'property of the workbook by saving it as a different file or</font>
      <font color="#008000"> 'to save it initially</font>
      <font color="#008000"> 'if the dialog is shown, we will cancel it and create our own</font>
      <font color="#008000"> 'if the dialog is not shown, we do nothing at all</font>
       <font color="#0000A0">If</font> SaveAsUI <font color="#0000A0">Then</font>
           Cancel = <font color="#0000A0">True</font>
           ImposedFileName = "GB Management Workbook - " & Sheets("GB").Range("e24").Text
          <font color="#008000"> 'note that the default name to save this file "as" will be shown in the dialog</font>
           BuildFullName = Application.GetSaveAsFilename(ImposedFileName, _
               "Microsoft Excel Spreadsheet (*.xls), *.xls")
          <font color="#008000"> 'If BuildFullName = "False" then the user cancelled the dialog</font>
          <font color="#008000"> 'and the workbook will not be saved</font>
           <font color="#0000A0">If</font> BuildFullName = "False" <font color="#0000A0">Then</font> <font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>
          <font color="#008000"> 'find the user selected filename</font>
           UserSelectedName = Trim(Mid(BuildFullName, InStrRev(BuildFullName, "\") + 1))
          <font color="#008000"> 'replace the user selected filename with the imposed FileName</font>
           BuildFullName = Replace(BuildFullName, UserSelectedName, ImposedFileName)
          <font color="#008000"> 'when we save below, we do not want this event to run again</font>
           Application.EnableEvents = <font color="#0000A0">False</font>
           Me.SaveAs BuildFullName
       <font color="#0000A0">End</font> <font color="#0000A0">If</font>

  Err_Workbook_BeforeSave:
       Application.EnableEvents = <font color="#0000A0">True</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("972006231037656").value=document.all("972006231037656").value.replace(/<br \/>\s\s/g,"");document.all("972006231037656").value=document.all("972006231037656").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("972006231037656").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="972006231037656" wrap="virtual">
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo Err_Workbook_BeforeSave

Dim BuildFullName As String, ImposedFileName As String, UserSelectedName As String

'check to see if the saveas dialog will be shown
'this dialog must be shown in order to change the readonly name
'property of the workbook by saving it as a different file or
'to save it initially
'if the dialog is shown, we will cancel it and create our own
'if the dialog is not shown, we do nothing at all
If SaveAsUI Then
Cancel = True
ImposedFileName = "GB Management Workbook - " & Sheets("GB").Range("e24").Text
'note that the default name to save this file "as" will be shown in the dialog
BuildFullName = Application.GetSaveAsFilename(ImposedFileName, _
"Microsoft Excel Spreadsheet (*.xls), *.xls")
'If BuildFullName = "False" then the user cancelled the dialog
'and the workbook will not be saved
If BuildFullName = "False" Then Exit Sub
'find the user selected filename
UserSelectedName = Trim(Mid(BuildFullName, InStrRev(BuildFullName, "\") + 1))
'replace the user selected filename with the imposed FileName
BuildFullName = Replace(BuildFullName, UserSelectedName, ImposedFileName)
'when we save below, we do not want this event to run again
Application.EnableEvents = False
Me.SaveAs BuildFullName
End If

Err_Workbook_BeforeSave:
Application.EnableEvents = True
End Sub</textarea>

Save As Static Name.zip

 

[lizziegirl]

Right_Click
I like it!

I think this is maybe how controlled I am going to be able to make it, even though if the user feels like being rebellious they can still change it. Hopefully when the user sees that the file name has been created for them they do not try to change it.

Maybe I try and insert a msg box at some stage to clarify to them that the file should be saved as is being displayed.

You are a gem..

 

[lizziegirl]

ooooh right_click....I have played round with my template and you have impressed me very much. I didn't realise you had tailored it so that no matter what the user saves it as, it only saves it as what we have specified!!

To utilise your skill further if I may, what would I need to code if I wanted to define the path in the future?

Also, (and I may be pushing the boundary) if I wanted to control this in the same respect on one of our templates in WORD, is there much difference? You have made me very excited!!