Finding an Excel Spreadsheet's template in vba

Question

I don't think this has been asked before, as far as I can tell from Google searches, and it's quite specific.

I have an excel spreadsheet template with vba code embedded (an 'xltm' file), and I want to find the path of the template in the spreadsheet that is opened by the template. If that doesn't make sense, I'll give an example of the problem:

A user opens a new spreadsheet from the template file by double clicking it. The template file is in 'C:\My Stuff' as an example. They fill in the cells, then click a button that creates a text file after asking them what they'd like to call it. The code then uses 'Application.ActiveWorkbook.path' to save the file in the same place as the spreadsheet is open (C:\My Stuff). The causes a permission error, as the spreadsheet hasn't yet been saved, and as such, doesn't have a path. 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.

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 them messing the formatting of the file up, and I don't really 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.

Hope this makes sense, and thanks for any answers in advance.

Answer 1

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 2

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