VBA Macro - Prompt Save As dialog upon open in Excel Template file

[lneidorf]

Hi there.

I am using Excel 2007.

I have a template file (.XLTM) that I want to distribute to various users. My goal is for them to launch the file and be immediately prompted to save it as an XLSM file.

I've experimented with placing various code into the This Workbook module of my template file. Here's what I have currently:

Code:
Private Sub Workbook_Open()
Dim fNameAndPath As Variant
fNameAndPath = Application.GetSaveAsFilename(initialFilename:="", FileFilter:="Excel Files (*.XLSM), *.XLSM", Title:="Save As")
If fNameAndPath = False Then Exit Sub
Me.SaveAs Filename:=fNameAndPath
End Sub

It works perfectly. Here's the rub: upon opening the resulting XLSM file (i.e. the file that was saved upon opening the XLTM template), that too contains the same code and prompts the user to save as again. So my goal is for this code to run only in the initial template and not in the resulting files saved from the template. I hope I've explained that well.

An important point: my template contains a multitude of other code that I will want the saved XLSM files to still contain. I want to eliminate only the code abpove from resulting XLSM files.

Any advice would be most appreciated.

Thanks!

 

[rafaelspeixoto]

Try this:

Private Sub Workbook_Open()
Dim fNameAndPath As Variant
if right(thisworkbook.name,4) = "XLTM" then
fNameAndPath = Application.GetSaveAsFilename(initialFilename:="", FileFilter:="Excel Files   (*.XLSM), *.XLSM", Title:="Save As")
If fNameAndPath = False Then Exit Sub
Me.SaveAs Filename:=fNameAndPath
end if
exit sub

 

[lneidorf]

Thank for the suggestion.

I've updated the code in my XLTM template. When I open the file (thereby spawning a new workbook), I'm receiving a Compile Error: Expected End Sub. I've tried adding an End Sub to the end of the code, which stops the error, but results in no Save As... dialog.

Just to be clear, I want to distribute an XLTM (macro-enabled template) to my users; have them open the file, thereby spawning a new version of the workbook; and be forced to immediately save as an XLSM (macro-enabled workbook). The trick is to stop the Save As... dialog from reappearing when and if they reopen the XLSM that they saved.

Thanks for your help!

 

[rafaelspeixoto]

you are right about the end sub instead of exit sub. I think I know what went wrong with my suggestion. Forgot that vba codes are case sensitive. Try this:

Private Sub Workbook_Open()
Dim fNameAndPath As Variant
if ucase(right(thisworkbook.name,4)) = "XLTM" then
fNameAndPath = Application.GetSaveAsFilename(initialFilename:="", FileFilter:="Excel Files   (*.XLSM), *.XLSM", Title:="Save As")
If fNameAndPath = False Then Exit Sub
Me.SaveAs Filename:=fNameAndPath
end if
end sub

 

[lneidorf]

rafaelspeixoto,

Thanks so much for your response.

That did indeed eliminate the error. Alas, it's not producing the desired behavior.

When I left-click on my XLST and select Open, then I am getting the Save As... prompt. However, when I simply double-click the XLST file (which is the behavior I expect from my users), it spawn a new workbook from the template and does not prompt to Save As. That's the scenario when I'd want the prompt to appear.

Let's make this simpler. Basically, if the file has not yet been saved, I want a prompt to the user to Save As... in XLSM format. If it has been previously saved, then no action required. That way, when my users open the XLST template, it will spawn a new workbook, at which point they'll be prompted to save it is XLSM format. After that, if they reopen the XLSM file, no prompt will appear.

I've played with a variety of options, but am missing the mark.

Thanks again.