Thursday, December 1, 2011

Classic Excel Add-in on 64-bit Windows 7

As I mentioned in my earlier blog post about getting the OlapUnderground Outline Extractor running on Essbase, the classic Excel add-in did not work on my laptop.   In my case, when I attempted to login to Essbase, the Essbase Login dialog did not display.  As a result, I could not connect to Essbase.  My laptop behaved like this for a number of months and, frankly, I didn't miss the add-in that much.  Dodeca has an add-in mode where I can do everything the classic add-in does and more.  Still, I wanted the classic add-in to run. I often use the classic add-in to create report templates for deployment in Dodeca.

Recently, when my QA team was working on support for the Outline Extractor in version, they discovered a new requirement for the PATH environment variable.  There is a new directory, the bin-32 directory installed under the EPM home directory, that is now required for the Essbase client API's to work.  This directory appears to contain dll's related to Oracle security.  The solution to get the classic add-in running on my machine was to add this new directory to the PATH environment variable.

I have an additional complication on my laptop as it also runs my Essbase server.  Needless to say, I am very careful when considering changes to environment variables.  I decided to create a command file to temporarily set the environment variables and then launch Excel.  Now, when I launch Excel using the following command script, the Essbase add-in works correctly.

set ARBORPATH=%EPMHOME%\common\ESSBAS~2\1112~1.0

set PATH=%ARBORPATH%\bin;%EPMHOME%\bin-32;%PATH%


start "C:\Progra~2\MIF5BA~1\Office11\EXCEL.EXE" %XLL_LOCATION%\bin\essexcln.xll

I find it more dependable to use the 8.3 filename format when creating command scripts.  Keep in mind that the 8.3 filenames may be different on your machine.