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

Answer 2

There's a way to do what you want...sort of. The name or path of the original template can't be known, because Excel makes an instant copy of it to create the new workbook, and it doesn't expose any properties with that information. But, the template can be identified by a unique tag which can then be accessed in the new workbook. And the tag doesn't have to be anything funky like a secret cell, hidden worksheet, or hidden textbox. It uses a rarely mentioned built-in feature of Excel called CustomDocumentProperties.

To do this requires a bit of extra VBA code that you may not be familiar with. Specifically, a VBA project reference to MSO.DLL, aka the Microsoft Office X Object Library, where "X" is the version of office. In my case, it's Office 2007, which is version 12, so it's Microsoft Office 12.0 Object Library.

To give the template a tag, simply add a custom document property to it. This can be done right from the main Excel window while you have the workbook open, as follows:

Click the Office Button > Prepare > Properties as shown below:

This opens the yellow Document Properties bar with various textboxes such as Author, Title, etc:

Then click Document Properties > Advanced Properties...

This opens the Workbook Properties window:

• Click the Custom tab.
• Type a property name.
• Type a property value.
• Click the Add button.

The new custom property will appear in the Properties list. Click the OK button:

Now for that bit of extra VBA I mentioned...

Open the VBA editor window, click Tools > References, and add a reference for Microsoft Office X Object Library (assuming you don't already have it), where X is your version of Office. If it doesn't appear in the list, you'll have to browse for it, and it could be in a variety of places depending on your version of Windows and Office.

This is how it appears IF you already have it. In my case it's already there and the box is checked:

If you scroll way down to the Microsoft items in the middle of the list, and you don't find it, poke the browse button. Note that if it is in the list, but you miss it, adding it again won't hurt anything. Plus sometimes it's just easier to do that instead of staring at the list for five minutes.

On my 32-bit dev-beater box, the path is as follows. I think this should also be the same for 64-bit Windows with 64-bit office, but I'm not sure.

C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL

On my 64-bit Windows desktop machine with 32-bit Office, it's:

C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE12\MSO.DLL

Remember to change the OFFICE12 to the version of Office you have if that's not it. Once you have it, click the OK button.

Now for the code...

In the VBAProject Explorer, double-click ThisWorkbook and add the following code. If you already have a Sub Workbook_Open, you'll have to integrate this into that as you see fit. Then once you see how it works, you can use it however necessary:

Private Sub Workbook_Open()
    Dim xl      As Excel.Application
    Dim wb      As Excel.Workbook
    Dim dcps    As Office.DocumentProperties
    Dim dcp     As Office.DocumentProperty
    Dim tname   As String
    Const dq As String = """"
    Set xl = Excel.Application
    Set wb = xl.ActiveWorkbook
    Set dcps = wb.CustomDocumentProperties
    Set dcp = dcps.Item("ThisTemplateName")
    tname = dcp.Value
    MsgBox "This workbook was created from the template named " & dq & tname & dq & "..!"
End Sub

Answer 3

I am working on a similar task. I am using the following as my starting point:

MsgBox Application.TemplatesPath

https://learn.microsoft.com/en-us/office/vba/api/excel.application.templatespath

Answer 4

The issue is that, in an unsaved workbook, Application.ActiveWorkbook.path is undefined. Here is a method that exposes the template path to the unsaved workbook. It requires two batch (command) files to be in the same folder as the Excel Template.

The first batch file, SetVariable.cmd, creates an environment variable.

@echo off

rem Create an environment variable That Excel macro can read
setx TTxyPATHzz "%cd%"

Because of the way Windows handles environment variables, the value is not immediately available to the current console session. (Source)

Therefore, the second command file, OpenInstance.cmd is needed.

@echo off
:: This boot-strap script enables the use of an environment variable which can be read by an Excel macro
:: Because of the way user environment variables are created, run SetVariable the first time you use template.

rem Create an environment variable That Excel macro can read
rem Setting the variable again will allow the location to change.
setx TTxyPATHzz "%cd%"

rem Invoke cmd.exe to open instance of Excel template
FOR /F "usebackq delims==" %%i IN (`dir *.xltm /B`) DO start excel /n "%%i"

In the template's Workbook.Activate event handler for ThisWorkbook, include the following:

Private Sub Workbook_Activate()

   Dim CurrentLocation As String

   CurrentLocation = Environ("TTxyPATHzz")

   ' Your workbook now knows where the template is located.
   
End Sub

Instruct the users to double-click SetVariable.cmd one time. Thereafter, users just have to double-click OpenInstance.

The choice of name for the environment variable is arbitrary and is intended to avoid accidentally overwriting an existing variable.

Answer 5

First-time poster, long-time benefactor of this forum and others like it.

For those that want to skip the yada yada yada and get right to the meat and potatoes, please scroll to the bottom for the code and sample usage. After all, that's really what any of us come here for.

For those that are interested, and to make a short story long, I came across this post a few months ago when I also needed to know where an Excel template had been opened from. My quest for an answer to this problem found that various forms of this question had been posed at least as far back as 2003. The few answers that I could find fell into two basic categories. The first was that it simply couldn't be done due to the mechanics of how Microsoft creates a new spreadsheet from a template. The second was that you would have to cheat and pre-code a static answer or have the end-user somehow dynamically provide the answer ahead of time. All valid answers but not the more elegant and hassle-free solution that I, or seemingly the OP, was looking for. For that, I had to play detective.

Microsoft has provided the clues to solving this mystery at least as far back as Windows 9X and Office '97. They practically rub our noses in the fact that they have what we're looking for but won't provide us any easy way to get it ourselves. Open any Office application and the first thing that you see is a dialog box with suggested templates to choose from. Below that is a list of previously opened documents for that application. If you hover your mouse over one of those filenames, you even see a tooltip showing the complete path to that file. So where is Microsoft hiding this little nugget of knowledge? The Registry, in a key named some permutation of MRU; meaning Most Recently Used. Unfortunately, the key's exact name and location are, seemingly, subject to change based on the OS, application, and it’s version. I'm sure that with a sufficient amount of coding, anyone, including Microsoft, could come up with a VBA function to provide the path that we're looking for using the MRU. Fortunately, Microsoft also provides a little more user-friendly version of this information for those of us looking for an easier path, so to speak.

Every time that we open a picture, video, PDF, etc., Windows creates a shortcut, AKA .lnk, for that file. This allows Windows to provide you with an application-independent look at your MRU; most recently viewable in File Explorer's "Quick access" pin in the Navigation pane. The exact location of where these shortcuts are located has changed over the years, but only a handful of times. This is still much more convenient for our purposes than trying to sift through Registry keys. The important thing about this shortcut is that it stores the complete path to the file that it refers to. While not entirely straightforward, the puzzle pieces are starting to come together.

The code that I provide at the bottom of this post takes all of the puzzle pieces that Microsoft provides and extracts the one bit of information that we're looking for - the filepath to the Excel template that was opened. Just to make sure that we're all on the same page, I'm not talking about having opened the template for editing, rather, using the template to create an entirely new spreadsheet based on the template in question. This is a necessary distinction because the shortcut that we need to find is named using the filename of the template, but the only filename that we're going to get from Excel's newly created spreadsheet itself is a derivation of the template's filename; usually just the name of the template with a single digit appended to it. Once we have this, there's just a little more work to be done.

So, here's how we put everything together. First, we have Excel give us the current file's name and strip-off the right-most character; this should be that aforementioned single digit. Then we try to figure out where the OS is storing its MRU shortcuts. This is a little bit of trial and error but easy enough with just a few If/Then statements. Last, but not least, a little trial and error figuring out the exact naming format for the shortcut to query for our template path. Again, just a few If/Then statements narrow it down rather quickly. Not exactly rocket surgery; more like a plinko board with a single slot at the bottom.

I've structured the code, and sprinkled in enough comments, that it should be easy to add additional If/Then statements to address any situations that you come across that I didn't experience in my testing. Alternatively, if you're looking to improve execution time and know the exact circumstances that you're coding for, you can pare down the code for your specific needs. My testing included using Office '97, Office 2007, and Microsoft 365 running on Windows 98, Windows 2000, and Windows 10. I opened my template from the harddrive, removeable storage, cloud storage, and network shares, both mapped to a drive letter and unmapped. This is not an exhaustive list of possible combinations by any means and your mileage may vary.

Having benefitted from the collective intelligence here, and elsewhere, I wanted to return the favor and share this solution. I sincerely hope that this helps the next person that runs into this problem and/or the OP, if they're still working on this after all this time.

----------- VBA Function -----------

Function GetTemplatePath()

' -- Gracefully sidestep errors
On Error Resume Next

    ' -- Set up variables
    Dim MyFileName As String
    Dim MyRecentsPath As String
    Dim MyRecentFilePath As String
    
    ' -- Start with the open file's name
    MyFileName = ActiveWorkbook.Name
    
    ' -- Remove appended digit that Excel adds
    ' -- Creates a unique name from template name
    ' -- Typically just a single digit, sequentially
    MyFileName = Left(MyFileName, Len(MyFileName) - 1)
    
    ' -- Derive path to Recents for current user
    ' -- The path depends on the Windows version
    ' -- Try path for Vista (and later) first
    MyRecentsPath = Environ("USERPROFILE") & "\AppData\Roaming\Microsoft\Windows\Recent"
    
    ' -- No Recents directory means tweak the path for XP
    If Dir(MyRecentsPath, vbDirectory + vbHidden) = "" Then

        MyRecentsPath = Environ("USERPROFILE") & "\Recent"
        
    End If
    
    ' -- Still no Recents directory means tweak the path for 9X
    If Dir(MyRecentsPath, vbDirectory + vbHidden) = "" Then

        MyRecentsPath = Environ("WINDIR") & "\Recent"
        
    End If
    
    ' -- Derive path to this specific file's "Recent" shortcut
    MyRecentFilePath = MyRecentsPath & "\" & MyFileName
    
    ' -- Extract the "Start in" path from this shortcut, maybe
    ' -- Depending on whether or not file name extensions are
    ' -- hidden (default) or visible to the current user it
    ' -- might be necessary to tweak the shortcut name
    With CreateObject("Wscript.Shell").CreateShortcut(MyRecentFilePath & ".lnk")
        GetTemplatePath = .WorkingDirectory
        .Close
    End With
    
    ' -- No path returned means tweak the shortcut name and try again
    If GetTemplatePath = "" Then
    
        With CreateObject("Wscript.Shell").CreateShortcut(MyRecentFilePath & ".xltm.lnk")
            GetTemplatePath = .WorkingDirectory
            .Close
        End With
        
    End If
    
    ' -- Still no path returned means tweak the shortcut name and try again
    If GetTemplatePath = "" Then
    
        With CreateObject("Wscript.Shell").CreateShortcut(MyRecentFilePath & ".xlt.lnk")
            GetTemplatePath = .WorkingDirectory
            .Close
        End With
        
    End If

End Function

----------- Sample Usage -----------

MsgBox (GetTemplatePath)