Planning Analytics for Microsoft Excel API for Macro Automation
It’s not uncommon to move an MS Excel workbook from Perspectives to Planning Analytics for Excel (PAfE) and find that there are buttons within the workbook that run macro(s) to perform automation tasks. If there are, you may encounter the following (or similar) error:
“Cannot run the macro <macro name>. The macro may not be available in this workbook, or all macros may be disabled.”
If you verified your security settings and have enabled MACROS and still receive this error, you should know that unfortunately, TM1 macro functions are not supported in Planning Analytics for Excel (PAfE). But don’t worry, without much effort, you can leverage the Planning Analytics for Microsoft Excel API for macro automation.
The following is an overview of how to get your macros working again.
Environment Setup
To leverage the Planning Analytics for Microsoft Excel API, you need to add two files to your Excel Workbook. They are CognosOfficeAutomationExample.bas and CognosOfficeMessageSuppressor.cls, which are really just text files containing Visual Basic for Applications (or VBA) commands (macro code) and saved as file types of “.bas” and “.cls” so that they will be “recognized” by Excel and your macros will run. You may already have these files on your system, but if not, they are free to download, add to your environment and share.
To download the files, you can:
On an MS Windows platform, use your web browser to navigate to IBM Fix Central, select Download files using HTTPS, and then Cognos, Cognos for Microsoft Office (11.0, All platforms). The file you want is coc_winx64h_11.0.1.11_ml.tar.gz (15.52 MB). When the download process finishes, the file will be in your local “downloads” folder where you can unpack it (to a suitable location) and then run: \ winx64h\ issetup.exe.
When the installation completes, you should have a local “C:\Program Files\ibm\cognos\IBM for Microsoft Office” folder, with an “Automation” sub folder holding these gems:
Prepping The Workbook
Before you can proceed any further, you need to have the “Developer tab” added to your MS Excel ribbon.
The Developer tab in Microsoft Excel is a feature that gives users access to advanced features, such as Visual Basic and Macro tools, ActiveX controls, and spreadsheet forms. It is usually hidden by default, but you can enable it by following these steps:
- Open your Excel workbook.
- Right-click on any ribbon tab
- Select Customize the Ribbon
- Highlight the Customize Ribbon tab on the left side
- On the right side, find the list of tabs in the ribbon and locate the Developer tab
- Left click the box to check the Developer tab
- Click OK
Adding Files the Automation Files
- Click the Developer tab, and then click Visual Basic.
- Right-click VBAProjectand click Import File.
- Browse to the location where the IBM Cognos Automation macro files are installed. The location we created above is “C:\Program Files\ibm\cognos\IBM for Microsoft Office\Automation.” Click the basfile and import it into the VBA project.
- Repeat steps 2 to 3 to import the clsfile. You should end up with a VBAProject that now looks like this:
Close the Visual Basic Editor and make sure that you save the file, close it, and then reopen the file – you can now call the macros contained in the Cognos automation macro files from the VBA code that you write in your Excel workbook!
Let Us Test It
Use an existing button or add a new one. I have added a new one for this exercise:
Next, you can assign a macro to the button. I have added one to the CognosOfficeAutomationExample.bas file:
Clicking the button shows that it is working:
Conclusion
You can see the following link to read more details on the steps I have outlined above: https://ibm.github.io/paxapi. In addition, the below documentation may be helpful when moving from Perspectives to Planning Analytics for Excel (PAfE):
Last but not least, if you would like further assistance with Excel macros use within PAfE, or help in migrating to Planning Analytics for Excel, please reach out to QueBIT!