Re-opening a *.xltm file - name sequentially changes by +1 every time it's opened?

[dougmarkham]

Hi Folks,

I've created a multi-macro excel model that I've saved as a *.xltm file i.e., so that the users cannot damage the original file.

As hoped, when opened the file becomes filename1 instead of filename.xltm.
I can account for this in my vba code by referring to the workbook as filename1...no worries.

However, the users have managed to thwart my code by making input errors, deciding to close the workbook and reopen it.
Now the file reopens as filename2 instead of filename.xltm, thus causing code that refers to the workbook to error.

My question is this: is there a setting I can change so that *xltm files always open as filename1, no matter how many times the file is opened during one windows session?
Is there a solution to this issue?
I've considered run a macro upon opening the file i.e., to change the workbook name to filename1; however, just wondered if there was a well known solution to this?

Kind regards,

Doug.

 

[jmacleary]

Hi there.

If you define a global variable to hold the workbook name you could fill that in in your open macro and use the global variable to refer from then on.

 

[Kamolga]

I usually set up workbook open event with save as to avoid over-writing my template. As I usually name them filename_template, I like to have a saveas window suggesting filename.xlsm in the right path at the beginning as well.

In VBE, 2x click ThisWorkbook object and paste

Private Sub Workbook_Open()
Dim FileName As String: FileName = "[COLOR=#0000ff]FileName1[/COLOR]"
Dim fPth As Object: Set fPth = Application.FileDialog(msoFileDialogSaveAs)
    With fPth
      .InitialFileName = FileName
      .Title = "Save your File"
      .FilterIndex = 2
      .InitialView = msoFileDialogViewList
        If .Show <> 0 Then
          ThisWorkbook.SaveAs FileName:=.SelectedItems(1), FileFormat:=xlOpenXMLWorkbookMacroEnabled
        End If
    End With
End Sub

so user is suggested to save the document as filename1, you can even loop it if he cancels it to make it idiot proof. As soon as he would open what would be filename2, he will be asked to save it as filename1 before noticing.
Your macros can refer to ThisWorkbook or ActiveWorkbook so the chosen name does not matter

 

[Norie]

Doug

Why are you referring to the workbook by it's name?

If you want to refer to a workbook in code within that workbook use ThisWorkbook.

 

[SpillerBD]

However, the users have managed to thwart my code by making input errors, deciding to close the workbook and reopen it.
Now the file reopens as filename2 instead of filename.xltm, thus causing code that refers to the workbook to error.

??
Why would you expect them to be opening the XLTM (Template) file. They should be reopening the filename1.xlsm that they would have saved. It appears they are going back to the template file again and creating another file from the Template.

 

[dougmarkham]

Doug

Why are you referring to the workbook by it's name?

If you want to refer to a workbook in code within that workbook use ThisWorkbook.

Hi Norie,

Thanks for your suggestion. I had a look online afterwards and discovered the following which I have applied to my macro:

wrk = ActiveWorkbook.Name
Application.Run ([B]"'" & wrk & "'"[/B] & "!NameRangeABC")

That's done the trick for the 'issue' macro. Can I also use ActiveWorkbook or ThisWorkbook within Application.Run statements?

Kind regards,

Doug.

 

[dougmarkham]

I usually set up workbook open event with save as to avoid over-writing my template. As I usually name them filename_template, I like to have a saveas window suggesting filename.xlsm in the right path at the beginning as well.

In VBE, 2x click ThisWorkbook object and paste

Private Sub Workbook_Open()
Dim FileName As String: FileName = "[COLOR=#0000ff]FileName1[/COLOR]"
Dim fPth As Object: Set fPth = Application.FileDialog(msoFileDialogSaveAs)
    With fPth
      .InitialFileName = FileName
      .Title = "Save your File"
      .FilterIndex = 2
      .InitialView = msoFileDialogViewList
        If .Show <> 0 Then
          ThisWorkbook.SaveAs FileName:=.SelectedItems(1), FileFormat:=xlOpenXMLWorkbookMacroEnabled
        End If
    End With
End Sub

so user is suggested to save the document as filename1, you can even loop it if he cancels it to make it idiot proof. As soon as he would open what would be filename2, he will be asked to save it as filename1 before noticing.
Your macros can refer to ThisWorkbook or ActiveWorkbook so the chosen name does not matter

Hi Kamolga,

This method allows a workaround if there isn't a convenient or known a way of referring to ActiveWorkbook in a particular operation, thanks for suggesting it. I will try and find a ThisWorkbook or ActiveWorkbook method for all operations; however, if one is not forthcoming for whatever reason, your method will be the saving grace.

Kind regards,

Doug.

 

[dougmarkham]

Hi there.

If you define a global variable to hold the workbook name you could fill that in in your open macro and use the global variable to refer from then on.

Hi John,

Thanks for suggesting this. I will read a bit on using Global variables as I'm not too familiar with them.

Kind regards,

Doug.

 

[Norie]

Doug

If the code in the workbook needs to refer to the workbook itself all you need is ThisWorkbook.