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

Posted on 2014-10-15
Last Modified: 2014-10-15

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.
Question by:bthouin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 15

Expert Comment

by:Haris Djulic
ID: 40381571

did you try using "Application.ActiveWorkbook.Path"

Author Comment

ID: 40381597
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.
LVL 15

Accepted Solution

Haris Djulic earned 500 total points
ID: 40381610
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"...
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 40381626
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 ?
LVL 85

Expert Comment

by:Rory Archibald
ID: 40381646
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.
LVL 15

Expert Comment

by:Haris Djulic
ID: 40381654
To get name of the template you can use the Application.ThisWorkbook.Name

Author Comment

ID: 40381669
Yes, that's hat I found out. And it works, so I have my solution. Thank you, samo. You get the points.

Author Comment

ID: 40381678
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.

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question