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
