TM1 Forum

Posted: **Thu Sep 26, 2019 9:55 am**

Hi all,

I have a little VBA project that integrates PPT and Perspectives, ...in short..., from a meny in PPT I open a Perspectives report, I filter it, and then refreshes it by using 'xlApp.Application.Run ("TM1REFRESH")'.

I now want to make this to work with PAX, but I run into a problem when refreshing the report. 'TM1REFRESH' is not available as Perspectives is not loaded, so I use the PAX API instead, in this case 'xlApp.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSheet'. This works if I run the syntax from within VBA in Excel, but not from PPT, I get an error: 'Automation Server available only on STA'. Not yet sure what that means.

I have also tried 'xlApp.Run("ONKEY_ALT_F9")', i.e. keys Alt + F9, to refresh the sheet. It works, but it takes 10 times longer than a 'Rebuild Sheet'.

1: should I be able to use the PAX API to interact with Excel from 'outside' Excel, e.g. PPT?
2: is there another smart way to do this, e.g. standard Excel functionality, but that is as fast as PAX's 'Rebuild Sheet'?

Posted: **Fri Oct 04, 2019 9:07 am**

Hello,

Whats happen if you make a VBA Public Sub in the Excel Workbook for refresh the TM1 Values like

Code: Select all

Public Sub RefreshTM1Values()

      Application.Run"TM1REFRESH"  'Or TM1RECALC


End Sub

then you cann call this routine from Powerpoint.

BR

TM1 Forum

PAX API: Refresh Sheet

PAX API: Refresh Sheet

Re: PAX API: Refresh Sheet