PAX RefreshAllData()

Post Reply
RonLat
Posts: 19
Joined: Tue May 02, 2017 7:49 am
OLAP Product: TM1, Planning Analytics
Version: 2.0
Excel Version: 365

PAX RefreshAllData()

Post by RonLat »

There is a function in PAX that allows to refresh the DBRW formula in a excel worksheet using VBA.

Preparations
  • The file CognosOfficeAutomationExample.bas is imported into the VBA project
  • The file CognosOfficeMessageSuppressor.cls is imported into the VBA project
  • PAX is connected to the TM1 server
  • A view from the Sdata cube is present in the worksheet (DBRW formulas)
Example 1

Code: Select all

Sub refreshData()
    CognosOfficeAutomationObject.RefreshAllData    
End Sub
Result: Run time error '424': object required

Example 2

Code: Select all

Sub refreshData()
  ActiveWorkbook.Worksheets("TAB1").CognosOfficeAutomationObject.RefreshAllData 
End Sub
Result: Run-time error 438: Object doesn't support this property or method

Example 3

Code: Select all

Sub CalculateOneWorksheet()
  ActiveWorkbook.Worksheets("TAB1").Calculate
End Sub
Result: The normal VBA function Calculate runs without errors, does not update the DBRW formulas though.


Does anybody have a hint how to use the RefreshAllData function with PAX in VBA?
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: PAX RefreshAllData()

Post by gtonkin »

Example 1 works in my books.

I had an issue previously with the IBM Framework for Office clashing with something when I updated to PAW 2.0.65

What version are you on? Did you previously enable the IBM Framework for Office as a COM add-in as well?

With PAfE 2.0.65 onwards, make sure that all PAfE and the IBM framework have been uninstalled.
I had an issue where my installation was not showing in Add or Remove Programs and I had to run the Uninstall from the folder in C:\Program Files\ibm\cognos\. Once this was removed and I restarted Excel with PAfE, in my case 2.0.67, all worked as expected.
flecky
Posts: 4
Joined: Wed Oct 06, 2021 4:17 pm
OLAP Product: TM1
Version: PAoC
Excel Version: Office 365

Re: PAX RefreshAllData()

Post by flecky »

I was having issues with refreshing data using VBA but I found something on the ibm git page https://ibm.github.io/paxapi/#refreshsheet

Code: Select all

Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSheet
I use as a simple refresh and also for dynamic report rebuilds in PAfE 2.0.68. In the VBA itself I have never imported CognosOfficeAutomationExample.bas or CognosOfficeMessageSuppressor.cls into the project but it seems to work fine and has done for the last few versions at least.
RonLat
Posts: 19
Joined: Tue May 02, 2017 7:49 am
OLAP Product: TM1, Planning Analytics
Version: 2.0
Excel Version: 365

Re: PAX RefreshAllData()

Post by RonLat »

Environment
  • Microsoft Excel for Microsoft 365 MSO (16.0.13127.21210) 32-Bit
  • IBM Planning Analytics for Microsoft Excel Version 2.0.68.4
  • Aktive COM-Add-In: IBM Planning Analytics for Excel Addin
  • Aktive COM-Add-In: IBM Planning Analytics for Excel Addin (Custom Task Pane Helper)
What I tried so far:

Code: Select all

Sub refreshData()    
    ' Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshBook
    Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSheet
    ' Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshAllData
    ' Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.RefreshAllData
End Sub
When I hit the F5 key within the Sub in the VBA editor, in all cases I get the error

Runtime error '91': Object variable or With block variable not set"

What do they mean by "Object variable" or "With block variable" that needs to be set?
I wonder also, how VBA would know which sheet to refresh, if I only say RefreshSheet?
RonLat
Posts: 19
Joined: Tue May 02, 2017 7:49 am
OLAP Product: TM1, Planning Analytics
Version: 2.0
Excel Version: 365

Re: PAX RefreshAllData()

Post by RonLat »

I tried to check, what COMAddins are installed on my system.

Code: Select all

Sub refreshData()    
    Dim var1    
    For Each var1 In Application.COMAddIns
    
        Debug.Print var1
    Next
End Sub
Result
  • Microsoft Power View for Excel
  • IBM Framework for Office
  • IBM Planning Analytics for Excel Addin
  • Microsoft Power Map for Excel
  • IBM Planning Analytics for Excel Addin (Custom Task Pane Helper)
  • Microsoft Data Streamer for Excel
  • Inquire
  • Microsoft Power Pivot for Excel

There ist no CognosOffice12 in this list, so

Code: Select all

Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSheet 
can not work at all. What's the deal with the CognosOffice12? Where does it come from?
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: PAX RefreshAllData()

Post by gtonkin »

Still fairly certain that you need to uninstall IBM framework for Office as you are running PAfE 2.0.68
RonLat
Posts: 19
Joined: Tue May 02, 2017 7:49 am
OLAP Product: TM1, Planning Analytics
Version: 2.0
Excel Version: 365

Re: PAX RefreshAllData()

Post by RonLat »

There is a workaround regarding this problem and it is described only in one place on the internet.

IBM Framework for Office and PAfE 2.0.66 installs.

https://community.ibm.com/community/use ... gestviewer

Old PAX: CognosOffice12.Connect
New PAfE: CognosOffice12.ConnectPAfEAddin

Working calls of the refresh functions:

Code: Select all

Sub refreshData()

   ' Call over object variable
    Dim CognosOfficeAutomationObject As Object
    Set CognosOfficeAutomationObject = Application.COMAddIns("CognosOffice12.ConnectPAfEAddin").Object.AutomationServer
    CognosOfficeAutomationObject.RefreshAllData
    
    ' direct call
    Application.COMAddIns("CognosOffice12.ConnectPAfEAddin").Object.AutomationServer.Application("COR", "1.1").RefreshBook
    Application.COMAddIns("CognosOffice12.ConnectPAfEAddin").Object.AutomationServer.Application("COR", "1.1").RefreshSheet
    Application.COMAddIns("CognosOffice12.ConnectPAfEAddin").Object.AutomationServer.RefreshAllData
    
End Sub
So, for now the problem is kinda solved, till IBM changes something in the near future and it crashes again :-)
Paul Segal
Community Contributor
Posts: 306
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: TM1 11 and up
Excel Version: Too many to count

Re: PAX RefreshAllData()

Post by Paul Segal »

Although if you read the whole link, this shouldn't be necessary. I'd be more concerned with getting the install right rather than amending the VBA code to partially fix something that's broken (and might fix itself in future, then giving you an issue with the VBA code that now no longer works).
Paul
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: PAX RefreshAllData()

Post by Elessar »

Hi all,

I've had another RefreshAllData fault with "91" error recently: DAX add-in caused this. Disabling it fixed the error
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
Post Reply