Macro to refresh multiple yet certain reports all on one tab?

[Former Member]

Hello,

I am looking to implement a macro that refreshes only select reports on a tab within BPC 10. Currently, I have set up a macro that can refresh all of them at once. In particular, I have 4 reports on 1 tab. I'd like to keep them all on that tab. However, I a macro that will refresh only Reports 000, 001, and 003 (not 002). I also want a macro that only refreshes report 002. Is refreshing multiple yet certain reports possible in macro's capabilities (working with VBA is new to me)?

Thanks,
EE

[Former Member]

Hi Eric,

Refer to below link.....

Refreshing Reports - What's New - SAP Library

Or you can use EPMExceuteAPI function(RefreshActiveReport) too for refreshing the specific reports.

Hope this helps..

Regards,

JP

[former_member186338]

Before using refreshactivereport in the macro you have to select some cell inside particular report.

[Former Member]

Yes.....specified in the link given above...thats why dint mentioned again...

[former_member186338]

I mean in macro code!

epmexecuteapi is not a good way to work with this api....

[Former Member]

Ohh Ok....Thanks!:)

[Former Member]

Hi Vadim,

In your opinion, what would be a good way to accomplish what I am trying to do?

Thanks,
EE

[Former Member]

... in regards to using a macro. I already know how to do the EPM way of refreshing manually. Looking to use a macro so that our users do not need to go in an put a cursor on each of the desired reports before clicking refresh.

[former_member186338]

Just create a VBA procedure that will do the following in the loop:

1. Select cell in report data arear to make the particular report active.

2. Execute refreshactivereport

[Former Member]

Hi Eric,

Apart from the solutions suggested already......one other solution would be to use "Freeze Data Refresh" option on report 002.

Active this option for Report 002 from the Edit report, so now you can use the normal Refresh button from the EPM tab which will refresh only report 000,001,003!

Create a macro button to refresh report 002 - Use the API "SetUserOptions" to deactivate the Freeze Data refresh....like"SetUserOptions(PauseRefresh,false) and then refresh the 002 report using the RefreshActive Report command or RefreshSelected report command in macro...

Sorry I dont have system right now to give you the detailed code...

Regards,

JP

[Former Member]

Sample code:

Sub Button23

Dim EPMObj As New FPMXLClient.EPMAddInAutomation

EPMObj.SetUserOption "PauseRefresh", False

EPMObj.RefreshSelectedReport

End Sub

P.S - While executing this button you will have to place the cursor on report 002!

Regards,

JP

[former_member186338]

I do not understand why do you need all this complex things...

You can get report cell with:

Dim rngCell as Range

Set rngCell = GetDataTopLeftCell(ThisWorkbook.Worksheets("SheetName"),"000")

rngCell.Select 'Data cell of report 000 will be selected.

api.RefreshActiveReport

...

same for other required reports...

[Former Member]

Yes Vadim...I understand! I just have suggested an alternative option.....which seems doable!

[former_member186338]

I do not understand how PauseRefresh in user options is related to the case (it's a global user option).

And what do you mean by RefreshSelectedReport?

P.S. And by the way there is no api to change report options, only sheet and user options can be changed using VBA...

[Former Member]

Got it Vadim! Thanks for correcting me......I thought the Freeze Data Refresh option would be disabled from the User options since we cant modify any report options from macro!. so I suggested the Useroptions API. No system to test so....

RefreshSelected Report is an option in the Refresh itself to refresh a slected report only..by placing the cursor.

Regards,

JP

[former_member186338]

There is no api RefreshSelectedReport