Accessing Hyperion Essbase is no problem directly from their MS Excel plugins. Though what happens if one wants to import on-the-fly and put that into MS Access so you can deliver your MI: Management Information, alongside current data without having to analyse and alter Hyperion Essbase output in Excel before exporting to textfile format or CSV before manipulation?
Well, why not try the below code!
The below function run within MS Access will write an “Essbase connection plugin” before instantiating a copy of MS Excel to run Essbase from. Unfortunately if you don’t write out and pull in the textfile to create a module in Excel as is within the code Microsoft’s security permissions now deny access. Give them their due – it was a good move for certain issues.
After the “plugin” is pulled into the MS Excel workbook it fires a connection to Essbase and in this case draws down and partitions the relevant info to worksheets. Note the code will require setting up against whatever Essbase fields you require from your system.
The code as you will see at the bottom will clear down any previous retrieval before using MS Access’ TransferSpreadsheet import functionality to pull in the retreived workbook. The code also requires a table within MS Access to hold your Hyperion Essbase login details. In this case the table is called tbl_essbase_credentials and can be seen within the code. The Excel workbook is cleared down and you are left with your Hyperion Essbase MS Excel retrieval in your MS Access database.
Way to go!
So the process from MS Access is to run function and -> create a module for import into MS Excel -> start a virtual copy of MS Excel -> import the module into the virtual copy of MS Excel -> run virtual module to connect to Hyperion Essbase and download required data -> save MS Excel workbook and transfer the spreadsheet into MS Access -> clean up. All from within MS Access!
