Excel created by a template: how to find in Excel VBA where the template was located ?


I have a simple, yet frustrating problem. I developed an Excel (2013) template with quite a bit of VBA code (.xltm), which, when double-clicked, creates an Excel .xlsm.

The template itself can be in different directories which can be production or test. In the VBA code of the main sheet Activate event of the created Excel, I should find out if I am in prod or in test, because I should get data from a DB either in prod or test, depending of where the template which "spawned" the Excel xlsm was. But because the Excel has been created and not yet stored, and therefore is kind of hanging in the air "in limbo", I failed so far to find a method or property which would tell me  where I am, let alone "where I was started from".

Is there a way for the Excel VBA in the created Excel to find out in which directory the template which created it was located ? Or do I have to put a hard-coded value in a cell of the template to say "I'm the prod template" or "the test template", and therefore have 2 different templates ?

Thanks for help.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicCommented:

did you try using "Application.ActiveWorkbook.Path"
bthouinAuthor Commented:
Yes, and that is desperately empty. I tried ThisWorkbook.Path, ActiveWorkbook.Path, both are empty. Parent is "Microsoft Excel" (not helpful...).

So, still no clue.
Haris DulicCommented:
If you use Excel File-Open and then select template than you can reference the Application.ActiveWorkbook.Path . If you double click than the file is in the as you said "limbo"...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

bthouinAuthor Commented:
Hi samo

Thanks for that !

My users will certainly not use the file open menu. They, as everybody else, just double-click on an Excel when they want to open it, so no chance then. I'll do 2 templates then, with as only difference a hidden text in a cell that says "Prod" or "Test", and put these in the corresponding directories. Not ideal, but it should work.

Or can I put that word in the template file name, and, as the created Excel bears the same name as the template (just with a "1" at the end), if I can find the name of the created file (how?), I would then "know" if I am in the prod or the test environment ?
Rory ArchibaldCommented:
The code in the template can use ThisWorkbook.Path to determine where it is, and then populate either a range or document property in the created workbook accordingly. Unlike Word, there is no connection between an Excel template and workbooks created from it.
Haris DulicCommented:
To get name of the template you can use the Application.ThisWorkbook.Name
bthouinAuthor Commented:
Yes, that's hat I found out. And it works, so I have my solution. Thank you, samo. You get the points.
bthouinAuthor Commented:
Hi Rory

The code in my template is intended t be used exclusively when the template has been double-clicked and a nex xlsm has been created, so using the template to populate some area in the created Excel is just too complex/cumbersome for me to develop. I prefer using the file name of the template and created Excels, that's trivially simple to use and it works fine. And it's also a document property, and one which is easily passed... :-)

Thanks the same.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.