Solved

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

Posted on 2014-10-15
8
966 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
[X]
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
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

734 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