Troubleshooting
Problem
In Planning Analytics for Excel (PAX), trying to refresh data through VBA macro or VBS script does not work : nothing happens or Excel may hang/freeze.
Resolving The Problem
Taking all these actions will allow the Refresh Data to work :
1) If using a VBS script, then it is recommended to update PAX version to 2.0.46 first.
2) In Excel, File/Options menu, Trust Center menu, Trust Center Settings button:
- in Trusted Locations, add "C:\Program Files\ibm\cognos\IBM for Microsoft Office" folder.
- in Macro Settings, check "Trust access to the VBA project object model"
- in Trusted Locations, add "C:\Program Files\ibm\cognos\IBM for Microsoft Office" folder.
- in Macro Settings, check "Trust access to the VBA project object model"
3) In the Excel VBA project, import this module and this class module from "C:\Program Files\ibm\cognos\IBM for Microsoft Office\Automation":
CognosOfficeAutomationExample.bas
CognosOfficeMessageSuppressor.cls
CognosOfficeAutomationExample.bas
CognosOfficeMessageSuppressor.cls
4) In your macro VBA code, add exactly these lines before starting to call the first PAX function (which will probably be "Logon"):
Dim test As Object
Set test = Reporting
This will initialize PAX the same way as when manually clicking on the IBM Planning Analytics tab in the Excel ribbon.
Dim test As Object
Set test = Reporting
This will initialize PAX the same way as when manually clicking on the IBM Planning Analytics tab in the Excel ribbon.
5) In either an existing module or a new module, add this line:
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Then add this line just after the "CognosOfficeAutomationObject.Logon" function:
Sleep 5000
This will give more time to PAX to properly connect to PAW.
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Then add this line just after the "CognosOfficeAutomationObject.Logon" function:
Sleep 5000
This will give more time to PAX to properly connect to PAW.
6) In the macro, use the RefreshAllData function of the CognosOfficeAutomationObject only :
CognosOfficeAutomationObject.RefreshAllData
CognosOfficeAutomationObject.RefreshAllData
Document Location
Worldwide
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSCTEW","label":"IBM Planning Analytics Local"},"Component":"PAX;Planning Analytics for Excel","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
10 October 2019
UID
ibm11085967