Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    template path (2000 SP3)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: template path (2000 SP3)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: template path (2000 SP3)

    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,

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: template path (2000 SP3)

    You might consider storing the template's full name and path in a custom document property and read that to determine what to do.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: template path (2000 SP3)

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

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: template path (2000 SP3)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: template path (2000 SP3)

    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.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: template path (2000 SP3)

    <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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: template path (2000 SP3)

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

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: template path (2000 SP3)

    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>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: template path (2000 SP3)

    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.

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: template path (2000 SP3)

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: template path (2000 SP3)

    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.

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: template path (2000 SP3)

    Have you seen my AutosafeVBE on my site listed below?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: template path (2000 SP3)

    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •