template path (2000 SP3)

**ralphad** · 2004-05-24 11:49

Does anyone know if there is a way of retreiving the path of the template used to create a workbook using VBA?

If it makes any difference, this code will be running before the workbook has been saved for the first time.

**HansV** · 2004-05-24 11:57

Once a workbook has been created, there is no link whatsoever to the template it was created from. Unlike a Word document, an Excel workbook is a complete copy of the template.

The only hint you have is that before it is saved for the first time, the name of the workbook is something like "TemplateName1" if the template is called TemplateName.xlt. So you can reconstruct the name of the template, and you might use the Application.FileSearch object to try and find its location.

**ralphad** · 2004-05-24 12:02

OK, that's what I suspected.

Unfortunately searching for the template is not feasible. This is a general solution for VBA programmers and there could be many different templates of the same name over many different network drives, etc.

It seems I will have to withhold functionality until the workbook has been saved.

Thanks again,

**pieterse** · 2004-05-24 12:13

You might consider storing the template's full name and path in a custom document property and read that to determine what to do.

**LegareColeman** · 2004-05-24 13:15

Can you tell us what you need the path for? There may be another way around it.

**JohnBF** · 2004-05-24 13:37

The OP stated that there could be many different template paths, but at least if the OP used the FSO as Hans suggested, using the Application Template Paths of each user would constrain the breadth of file searches.

**ralphad** · 2004-05-24 14:04

The project I am writing is a code management tool for VBA programmers.

One of the features is the multiple export of modules and I want the default path to be that of the project the VBA programmer is working on.

Sometimes VBA programmers work on templates (as we do here). If you open a template for alteration through explorer then what you are actually working on is a new workbook and not the saved file. Granted, that as soon as the developer saves the file overwriting the .xlt then I will know the path, but it would be nice to be able to present the default path to the programmer before this time. It's not a big issue, just a nice to have.

**pieterse** · 2004-05-24 15:51

<hr>The OP stated that there could be many different template paths<hr>

I know, but I wanted to suggest to have the path the template is in be in the template itself.

**LegareColeman** · 2004-05-24 18:42

Unfortunately, I don't see an easy way to accomplish this.

**pieterse** · 2004-05-25 08:51

If a template is opened by doubleclicking in explorer, you indeed get a copy opened in Excel. This may be detected by checking the path property, which will be empty in that situation. SO you might add this to the thisworkbook object:

<pre>Private Sub Workbook_Open()
If ThisWorkbook.Path = "" Then
MsgBox "You have opened a copy of a template instead of the template itself!" _
, vbExclamation + vbOKOnly
End If
End Sub
</pre>

**ralphad** · 2004-05-25 12:15

LIke I say, this is a general solution for developers to use when working on any VBA project (workbook, template, addin, etc.) they like. I can't go telling them all to put in a line of code into their projects to make my add-in work.

**JohnBF** · 2004-05-25 14:24

I'm reaching here, and the more experienced Excel coders may see a flaw in my logic, but what if you were to e-mail the template and ask (or demand) the users to open it in e-mail when it contains Open Event code a bit like this untested example:

Private Sub Workbook_Open()
Dim strUserTempatePath As String
Dim varTemp As Variant
Dim boolInstalled As Boolean

Application.ScreenUpdating = False
Application.EnableEvents = False

strUserTempatePath = Application.TemplatesPath

For Each varTemp In ThisWorkbook.CustomDocumentProperties
If varTemp = "CurrentUserTemplatePath" Then boolInstalled = True
Next

If Not boolInstalled Then
ThisWorkbook.CustomDocumentProperties.Add _
Name:="CurrentUserTemplatePath", _
LinkToContent:=False, _
Type:=msoPropertyTypeString, _
Value:=strUserTempatePath
ThisWorkbook.SaveAs Filename:=strUserTempatePath & ThisWorkbook.Name, _
FileFormat:=xlTemplate
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

**ralphad** · 2004-05-25 16:32

OK, just to stop any further replies regarding playing with the templates I'll reiterate what I'm doing.

1. I am writing an add-in for the VBA editor which programmers will use to manage the code in their VBA projects.

2. One of the features of my add-in is to export VBA components (Modules, Class Modules, Forms, Documents) in bulk.

3. When the programmer exports the modules in bulk for the first time, my add-in asks them for a path to do so.

4. My add-in uses the path where the VBA project is saved as the default path for export.

5. There are many different types of VBA project which programmers may be working on and one of them is an Excel template.

6. When an excel template is opened from explorer, it is a new workbook (unsaved) and not the template itself.

7. In this case, if the user tries to export all the modules for the first time before the edited template has been saved, there is no default path to be had, since the unsaved, new workbook has no path.

I hope this explains the problem. I don't supply the VBA projects that the programmers are working on. They are the programmer's own, private projects and I have no control over them. The only thing I am supplying is an add-in for general use.

**pieterse** · 2004-05-25 17:30

Have you seen my AutosafeVBE on my site listed below?

**ralphad** · 2004-05-26 11:07

That looks interesting. Although it's only providing a small subset of the functionality of my add-in.

Btw - when I opened Autosafe VBE.xla it threw up "Error 5" and told me to contact support in an endless loop.

Thread: template path (2000 SP3)

Thread Tools