?
Solved

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

Posted on 2014-10-15
8
Medium Priority
?
1,219 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 2000 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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
 
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

588 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