Calculating BPC10 EPMContextMember() functions on workbook open

[Former Member]

Hello,

Is there an easy way to have the BPC10 function EPMContextMember() immediately recalculate when opening a workbook, without fully refreshing the file?

We have built a number of reports that display a 'summary' page with these functions as a reminder to the user what data they will be retrieving.  But it seems that during a refresh BPC updates these functions only after retrieving data.

We've tried the Excel "Calculate Now" and "Calculate Sheet" options, but they don't seem to work.  Editing the EPMContextMember() cell (eg., with pressing F2) does force the update, but that's a manual step we'd like to avoid.

Don't know if anyone has a better solution?

Thanks,

[Former Member · Accepted solution]

I am not sure if I fully understand your purpose here, but have you tried setting the option to refresh on file opening? Or refresh on context change? These settings can be found in user options rather than sheet options.

[Former Member · Accepted solution]

Thanks, yes, we have tried those but they don't entirely fit the problem.  The issue is that we want to read the context's current values when the file opens (without retrieving any data).

The first option (refresh on file open) immediately retrieves data, which we don't want to do on opening the file... we just want any cell that has an EPMContextMember() function to update and pull in the current context member.  The second option (refresh on context change) again pulls in data when the context changes, but I'm looking just to read the context's value when the file is opened.

[Former Member · Accepted solution]

You can do this with a simple API macro, but you need to add the FPMXLClient reference in VBA (ALT+F11) under Tools-->References first.  Then add the following macro under "Workbook" and save file as macro-enabled type (".xlsm)

Dim EPM As New FPMXLClient.EPMAddInAutomation

Sub Workbook_Open()

  ActiveSheet.Range("A1:A2").Select

  EPM.RefreshSelectedCells

  ActiveSheet.Range("A1").Select

End Sub

Regards,

Di~An

[Former Member · Accepted solution]

forgot to mention you will need to adjust the "ActiveSheetRange("??:??).Select" to reflect the ranges of cells you need to update on file open.  the second range just ends the macro at cell A1 so the range is not selected at the end of the macro.