Finding an Excel Spreadsheet's template path

Question

I have an Excel spreadsheet template with VBA code (an 'xltm' file).

I want to find the path of the template in the spreadsheet that is opened by the template.

Example of the problem:

• A user opens a new spreadsheet from the template file in 'C:\My Stuff' by double clicking it.
• They fill in the cells, then click a button that creates a text file after asking them what to call it.
• The code then uses Application.ActiveWorkbook.path to save the text file in the same place as the spreadsheet is open.
• This causes a permission error, as the spreadsheet hasn't yet been saved, and as such, doesn't have a path.

Is there a way to find the original template's file path? In the example C:\My Stuff.

I could make the user save the file before the text file is created, or I could just use an xlsm file but a template will minimise the chance of messing up the formatting of the file. I don't want to ask them to save the file each time. They'll probably copy and paste most data in, then just want the text file, not a different spreadsheet each time.

Answer 1

So my question is: Is there a way to find the original template's file path? In the example this would be C:\My Stuff.

You can't.

I am sure this is not the answer you were looking for but unfortunately this is the answer.

If you double clicked the template in explorer to create a file then AFAIK you cannot find that path or template name like you can do in MS Word using oDoc.AttachedTemplate

Alternative

In your template, put this code in ThisWorkbook

Private Sub Workbook_Open()
    ChDir "C:\Blah Blah" '<~~ Change this to the templates path
End Sub

And then from the new workbook that you created by double clicking the template you can use CurDir to get that path.

Drawbacks of the above method

1. The Workbook_Open() code gets carried over to the new file
2. If the user changes the location of the template then you will not get the right path