Excel created by a template: how to find in Excel VBA where the template was located ?
Posted on 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.