With all the automation posts this week, I feel I should change my name to Automation Queen instead of EPM Queen. 🙂
I get so heartbroken when people try to tell me that VBA is dead. No…it’s not! If you know how to use it for your good, it can make your life easier and processes more bulletproof. Let me give you an example…
Each month you are required to export a portion of a dimension from an outline for tracking purposes. It is the same process each month and can take sometime. What is the quickest and most reproducible way to do this? Allow me to suggest a(n automated) method.
Recently, Applied OLAP upgraded the old Outline Extractor and rebranded it as the Next Generation Outline Extractor. There are more options and the GUI is very user friendly. Here is the documentation so you can see all the options -> OutlineExtractorDocumentation . You have the option to extract as few as one or as many as all of the dimensions from a chosen outline. Also, you can choose hierarchies within the dimension instead the whole dimension (nice!). You can choose the extract format, location for the extract, file options, etc. LOTS OF OPTIONS. One thing that is my favorite is that you can create a Java properties file that can rerun with your selected options upon call. (The properties file is created once you originally choose the options the first time running through the steps.)
Applied OLAP even included command line version of the outline extractor that you can use with your properties file. The way you can set this up is to open the “olapunderground-outline-extractor.cmd” file for edit.
Properties File (highlighted the section that is interesting to me 🙂 ):
Windows Command Script:
At the bottom of the command script, there is a section labeled “:runGui”. Here is where you can enter the commands necessary to run the properties file from the command script. You will need to modify this to include your username, password, and location of the properties file.
From here you can execute the command script and it will run the file for you. You can add as many properties file lines as you would like to this section and they will be run in serial.
Now that you have the connection and parameters set for the outline extract, you now need to set up your VBA to call the command script (Excel will be used as an example).
The very first thing I do is validate that the Java Home environment location variable is set. I like to do this when the workbook is opened so that the user knows immediately if there is an issue and to give them the link of where to download the correct version of Java.
Next, you will need to create a routine to call the script from VBA. BUT, before you do this, I would advise creating a batch file in the Outline Extractor folder that runs the command script from the batch script. The commands should match what it would take from your machine to run the script if you were to enter the DOS commands manually. For example, here is my batch file called “RunExtract.bat”.
RunExtract.bat Contents:
The routine will call the batch file from VBA using a series of commands to run it as a batch file. There is built in testing if the file is not being created that allows you to see what the error is in DOS (performed by uncommenting a line from the code). Here is the code:
After this routine, you can enter the code necessary to import the file and manipulate it as you would like.
…And there you go. This is how you can automate running the Outline Extractor from VBA to make your life easier and more automated!
For my personal thing of the day…no surprise here, I am a runner. I recently started a journey to run 12 half or full marathons in 12 months. I’m 1/6 of the way there! Previously, my best was 3 half or full marathons in 1 year. I just bested that this past weekend with 4. If I am blessed to stay away from injury, I should finish this year with 7!
See you at #OOW14!
RealTriGeek 