Solved

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

Posted on 2014-10-15
8
814 Views
Last Modified: 2014-10-15
Hi

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.
Bernard
0
Comment
Question by:bthouin
  • 4
  • 3
8 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40381571
Hello,

did you try using "Application.ActiveWorkbook.Path"
0
 
LVL 1

Author Comment

by:bthouin
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.
0
 
LVL 15

Accepted Solution

by:
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"...
0
 
LVL 1

Author Comment

by:bthouin
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 ?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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.
0
 
LVL 15

Expert Comment

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

Author Comment

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

Author Comment

by:bthouin
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.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

747 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now