Search code examples
exceltemplatesexcel-2007vba

Determine Excel Template Path

I have an Excel 2007 template that I want to allow others to be able to use. As part of the code I need to know where the template was originally located, since the location can be different based upon where he user places it. When a new workbook is created form the template, it has no value in the ActiveWorkBook.Path value, this is not populated until it is saved somewhere.

Is there any way to determine the path of the template from which the workbook was created?

Solution

  • If the template has a unique name, and you know what drive it is stored on you could use something like:

    Function GetTemplatePath(drive As String, templateName As String) As String

    Dim cmd As String, result As String
    cmd = "CMD /C DIR """ & drive & ":\*" & templateName & """ /S /B /A:-D"

    On Error Resume Next
    result = Split(CreateObject("WScript.Shell").Exec(cmd).StdOut.ReadAll, vbCrLf)(0)
    On Error GoTo 0

    If InStr(result, "\") Then
        GetTemplatePath = Left(result, InStrRev(result, "\"))
    Else
        GetTemplatePath = vbNullString
    End If

End Function

    You would use it like so:

    Dim filePath As String

filePath = GetTemplatePath("C", "MyTemplate.xltx")

Debug.Print filePath