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

dougmarkham

Active Member
Joined
Jul 19, 2016
Messages
252
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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

Code:
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
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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:
Code:
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.
 
Upvote 0
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

Code:
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.
 
Upvote 0
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.
 
Upvote 0
Doug

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

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top