RefreshSheet macro is refreshing the whole book

Post Reply
antcharwill
Posts: 9
Joined: Mon Nov 18, 2019 9:50 am
OLAP Product: Planning Analytics
Version: PAx for Excel 2.0.77.3
Excel Version: Excel 365

RefreshSheet macro is refreshing the whole book

Post by antcharwill »

Hi there,

I've been experimenting with refreshing PAx data using VBA and I was pointed to this:

Code: Select all

Sub RefreshSheet()

    Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSheet

End Sub
But it's refreshing every sheet in the book, rather than just the active sheet as per the PAx documentation.

Any help appreciated!
antcharwill
Posts: 9
Joined: Mon Nov 18, 2019 9:50 am
OLAP Product: Planning Analytics
Version: PAx for Excel 2.0.77.3
Excel Version: Excel 365

Re: RefreshSheet macro is refreshing the whole book

Post by antcharwill »

After further investigation, this might be something to do with the 'Rebuild Sheet' function and not with the RefreshSheet macro. When I click 'Rebuild Sheet' I see the same behaviour: every worksheet is recalculated. This also seems to happen whenever I run any Cognos Office Automation functions.

I have a new workbook with two sheets both containing dynamic reports from different cubes. I change the parameters of both, then run this macro

Code: Select all

Sub test23()
    Reporting.DynamicReports.GetAt(Application.ActiveSheet.Name).Item(0).Refresh
End Sub
And both sheets are recalculated. Formulas are set to 'Manual' so it shouldn't be automatically recalculating things.
Post Reply