template path for active workbook?

  • Thread starter Thread starter Christoph Lutz
  • Start date Start date
C

Christoph Lutz

Hi there,

I have searched high and low but no joy - maybe it's not possible?

I need to find out the path of the template (xlt) for the currently
opened workbook. I need that path because I need to refer to folders
that are relative to the current workbook's template path. That path may
change so hard coding is not really an option.

Something like ActiveWorkbook.AttachedTemplate.path doesn't exist but
gets you the idea of what I'm after....

Anyone??

Thanks so much

Christoph
 
When you open a template you should set a variable to the template like this

Set Tempbk = Workbooks.Open("C:\temp\book1.xls")
TemplatePath = TempBk.Path
 
When you open a template you should set a variable to the template like this

Set Tempbk = Workbooks.Open("C:\temp\book1.xls")
TemplatePath = TempBk.Path
 
Hi,

Thanks for your reply but that doesn't do it. The user will just choose
to double click the template or go to File/New and choose from templates
on his computer. The new file is not opened through code although code
is run on Auto_open when the template is opened. Or am I getting the
wrong end of the stick here?

Christoph
 
Excel doesn't keep track of this kind of information.

Once the workbook has been created, there's no tie-back to the template that was
used.

If you wanted, maybe you could add something to the workbook (a value in a
hidden sheet, or a hidden name) that would help you do whatever you needed.
 
You can retrieve the full path to your application with the full path to your
application:
CurrentProject.FullName
and just the path:
CurrentProject.Path

Good luck,
Ryan---
 
Hi Ryan,

I have seen CurrentProject.FullName in MS Access before but not in
Excel? I get an error using this in Excel and there is no help reference
to that either?

Christoph
 
Hi Dave,

Thanks for that - I kind of guessed that would be the answer. Trouble is
that putting something in a hidden sheet or cell is as good/bad as
hard-coding it in the code, really. What a shame it seems not possible,
blows the process out of the water, really.

Christoph
 
You can use:
workbooks("someworkbook").fullname
to see the path and filename of the workbook.

You can use:
application.Path
to see where the excel.exe file is located.

But neither of these will help determine the location of the template that was
used to create a workbook.
 
Yep, that's the conclusion I arrived at. And even

workbooks("someworkbook").fullname

will only work once saved, otherwise it will be blank

Thanks for your help, though

Christoph
 
Back
Top