How to get folder path for xltm file

[niuyuer]

For example, I have a file called test.xltm, if I double click the file, a new file test1.xltm is opened, but it seems this new file does not share same folder path as original file. How can I know the folder path of original file?

If I use test.xlsm, then there is no such issues, but I would choose to use xltm.

Thanks.

[niuyuer]

I used thisworkbook.fullname and thisworkbook.name, but this workbook is not the same workbook test.xltm

[Norie]

When you create a new file from a template the new file doesn't store any information about the template so there's no straightforward way to get the template's path.

What you could do is add the path to the template as a CustomDocumentProperty of the template.

That can be done like this.

Please Login or Register  to view this content.

In the new document you can then retrieve the path like this.

Please Login or Register  to view this content.

[niuyuer]

When you create a new file from a template the new file doesn't store any information about the template so there's no straightforward way to get the template's path.

What you could do is add the path to the template as a CustomDocumentProperty of the template.

That can be done like this.

Please Login or Register  to view this content.

In the new document you can then retrieve the path like this.

Please Login or Register  to view this content.

Thanks, how to "add the path to the template as a CustomDocumentProperty of the template"? When I double click, it creates a new file, the code does not even know the original template file path, how to change the property of original file?

Where to put the code "ThisWorkbook.CustomDocumentProperties.Add "TemplatePath", False, msoPropertyTypeString, ThisWorkbook.Path" ?

Thanks.

[Norie]

This would be run in the template workbook, not a workbook created from the template.

Please Login or Register  to view this content.

This would be code for the new workbook, and is just really an example.

Please Login or Register  to view this content.

[niuyuer]

This would be run in the template workbook, not a workbook created from the template.

Please Login or Register  to view this content.

This would be code for the new workbook, and is just really an example.

Please Login or Register  to view this content.

I am confused. Actually, I only built one single file, and that file is xltm file. I did not have "new workbook" unless I double click the file, there is no such way to put the code for new workbook. By saying that, all I can do is putting code in original template file, but when I double click the template file, it does not even open the original template file, instead, it opens a new workbook.

My point is:
Original Template File: has some properties.
New workbook: it is a different file. When it is opened, it does not have path.

[Norie]

When you double click a template a new workbook is created based on the template.

That new workbook has no information, eg path, pertaining to the template workbook.

Where/when exactly do you want to access/use the path of the template?

PS Have you tried opening the template file by right clicking and selecting Open?

That will open the actual template file, not create a new workbook.

[niuyuer]

Let me be clear about my question:

For example, I have a file "C:\Users\Mike\Documents\Test.xltm" , and I have below code. I expect it to return the message "C:\Users\Mike\Documents\Test.xltm". But when I double click the file and run the code, it returns "Test1", that is not what I want.

Please Login or Register  to view this content.

[Norie]

That's because the workbook hasn't been saved yet, so it has no path only a name which is derived from the template file.

To get the path for the template you could run this code in the template file itself, then save and close the template.

Please Login or Register  to view this content.

What that code does is create a custom property in the template file which contains the tempate's path.

That property will be carried over to all the workbooks you then create from the template.


So if you create a new workbook from the template by double clicking and run this code you should get what you want.

Please Login or Register  to view this content.

[niuyuer]

That's because the workbook hasn't been saved yet, so it has no path only a name which is derived from the template file.

To get the path for the template you could run this code in the template file itself, then save and close the template.

Please Login or Register  to view this content.

What that code does is create a custom property in the template file which contains the tempate's path.

That property will be carried over to all the workbooks you then create from the template.


So if you create a new workbook from the template by double clicking and run this code you should get what you want.

Please Login or Register  to view this content.

Okay, thanks. It seems working BUT there is error message. I have below two parts of code, one is in Module, another is in Workbook. When I double click the file, there is error message, I click "End", then run the code FindFilePath, it does return the folder path, which is what I need. But how can I get rid of the error message window?

Thanks.

Please Login or Register  to view this content.

Error.jpg

[Norie]

This code should be run once in the template file, personally I just ran it from the Immediate Window (CTRL+G).

Please Login or Register  to view this content.

Once it has been run every new workbook created from the template file will have access to the custom property 'TemplatePath'.

If you put this code in the Open event it will run every time a new workbook is created from a template and will try and create another custom property named 'TemplatePath' and Excel/VBA is not going to like that.

[niuyuer]

This code should be run once in the template file, personally I just ran it from the Immediate Window (CTRL+G).

Please Login or Register  to view this content.

Once it has been run every new workbook created from the template file will have access to the custom property 'TemplatePath'.

If you put this code in the Open event it will run every time a new workbook is created from a template and will try and create another custom property named 'TemplatePath' and Excel/VBA is not going to like that.

Thanks. Now I removed the code Workbook_open, and it works now. So it returns the error message, because the property is already there?

[niuyuer]

Thank you very much!

It takes me a while to understand your response.

[Norie]

Yes that's right.

[niuyuer]

Yes that's right.

Sorry to mark it back as Unresolved. Since there is another issue with your code. You run the code and get the template path, but after I move the file to another location, it cannot get the new folder path for the template file. This is not exactly what I want. I have to share this file with many others. They will not get the correct template path.

I cannot ask each user to run the code once.

What is an easy way for users to set their own template path? Something like ctrl + a shortcut key?

Thanks.

[niuyuer]

Probably I will go with xlsm if there is not a good solution.

[Norie]

I'm sorry but there is no built-in way to get the path of the template a new workbook was created from.

Why do you need/want the path?

What is the purpose of the template?

[niuyuer]

I'm sorry but there is no built-in way to get the path of the template a new workbook was created from.

Why do you need/want the path?

What is the purpose of the template?

I need to create a new file and save it in the folder where template is.

[humdingaling]

dont really have a "proper" way to do it
could not use a document field to save the location in the template?

ie have something like this in the template
assuming it is book1.xltm

Please Login or Register  to view this content.

when you open the template it will have the path of the comments of the document property

like so

Please Login or Register  to view this content.

assuming you grabbing the file name from somewhere...like cell a1

[Doc.AElstein]

Hi niuyuer,

_!) I apologise if I am missing the point but this might be a partial solution,....

_ Assuming that the template was given to someone, then it would have been saved on that persons Computer using their Excel?? Correct??
_ If that is so , and in the meantime they have not opened too many files, then that template will appear in their last used: ( Note this is German XL 2007 – your version may look a little different. )
Attachment 466330


If they then open that Template by clicking on it there, and then select_....
Save As
_.......then they get this ........
Attachment 466333

_............If they then click on the small arrow indicated above...

_.....................Then they get this.
Attachment 466332


_..........................If they highlight that path and then hit Ctrl+C, then they have the Path of the Template,
Here it is
C:\Users\Elston\AppData\Roaming\Microsoft\Templates

_...................................................................


_2) The following is a side issue, not directly relevant to your problem. But it may help you understand some of what Norie said earlier in the Thread:

When you double click a template a new workbook is created based on the template.
the new workbook has no information, eg path, pertaining to the template workbook.......

_ When you are using Excel you can never actually work on a File. You may think you are, but you are not.
_ When you think you “open” a File, something else goes on.:-

_ Excel displays on the screen something looking like your file, ( A “copy” of it if you like,. Or just some “Thing” ). When you make changes, Excel reflects in that “copy” or “Thing” how changes would appear if they were done to your file.
_ But also Excel does all sorts of other things, so that it knows how those changes would look like in a file of a different kind.
_ So for example you can open a .xlsx file and add Code in code modules as if it were a .xlsm file.

_ At this point the “Thing” you are “working on” is held somewhere internally by Excel, so the “Path” is a bit tricky to visualise.
_ When you save, the File you select to be saved as will become A File of the type you Save As, And it will take the form of the current state of the “thing”. So only then does the “Path” take the form you expect, as this will then refer to the saved File. However as you continue to “work” or make changes , you are once again working on the “Copy” or “Thing”, not on your File.

(_... See Post 3 here:
http://www.mrexcel.com/forum/excel-q...cro-files.html
_.......)


Hope that may be of some small help

Alan




P.s.

I note I had to put this code from Norie in the ThisWorkbook Code module

Please Login or Register  to view this content.

_........

The other code

Please Login or Register  to view this content.

goes in any module in a Workbook created from the Template

[Doc.AElstein]

Hi niuyuer,

I had another thought,

_ Maybe the codes below better meet your requirement.

_ Both Codes should go in the ThisWorkbook Code Module of the Template

_ .............

The codes are all based on Norie’s ideas and codes.

Code 1:
When you open the Template a
Private Sub Workbook_Open()
Springs into action:
This does the following
Rem 1) Document Property of String Path where Template currently is
_ ‘1a) A check is done to see if the Document Property of the Path name Exists.
_’1b) If it does not exist, then it is made.

Rem 2) Output info
_’ 2a) The Current Path to the Template is pasted out to the Immediate window. ( So if you hit Ctrl+G when in the VB Editor ( Alt F11 ), then you will get displayed a copyable String
_’2b) As before a message box displays that path.

_.............................

Code 2

Before you share the Template you should run the code
Sub DeleteCustomDocumentProperty()
This code deletes the Current Path Property. So when someone opens the code from anywhere else, then the first code springs into action and gives the Current Path to where the Template is.

_.................

Let us know please how you get on

Alan

Codes:

Please Login or Register  to view this content.