Execute macro when opening Report in BPC 10.0

[former_member285525]

Hi all,

I need to execute a macro to allow grouping and ungrouping some rows and columns, in a protected EPM Report.

I have developed the following routines, but they continue not to be executed when I open the report.

Sub After_Workbook_Open()

Call grouping

End Sub

Sub grouping()

  With Worksheets("IS HEPS BEPS")

  .Unprotect "123"

  .EnableOutlining = True

  .Protect "123", contents:=True, userInterfaceOnly:=True

  End With

End Sub

If I execute the macro directly in Excel it works as expected.

Can you help me identifying what may be missing?

Best regards

João Arvanas

[former_member523998]

This is an old question, but there's not a correct answer that properly addresses the correct steps.

• The typical method of running a macro using excel's Workbook_Open() method will NOT work in EPM.

Solution

Create a macro called after_workbook_Open and make sure it is in the a regular MODULE (i.e. Module1) NOT the workbook module, as one would do when using the standard excel method.

See below (note this particular approach is one I use on almost all templates to ensure that context member formulas are properly updated).

[former_member186338]

"excel's Workbook_Open() method will NOT work in EPM" - not 100% correct!

It will work if the file is opened from the regular folder and will not work if opened from BPC server

[Former Member]

Hello Joao,

Try the below code it should work...

Private Sub Workbook_Open()

Call grouping

End Sub

Sub grouping()

Sheets("IS HEPS BEPS").Unprotect Password:="123"

    With Worksheets("IS HEPS BEPS")

       .Protect Password:="123", UserInterfaceOnly:=True

       .EnableOutlining = True

   End With

End Sub

[former_member285525]

Hi,

Your code and and mine are working perfectIy, but getting executed whan opening EPM Addin Reports. Nevertheless I discovered what was the problem.

While trying the classical "WORKBOOK_OPEN" String I noticed that some of the actions were being overwritten by EPM Add-In, as the Layout was not yet loaded when the code was executed. The reason for that is, that EPM Add-In functionalities run after the "WORKBOOK_OPEN".

Insert a SUB in a Module that contains the string "AFTER_REFRESH" instead of "WORKBOOK_OPEN" and now it works.

Thanks for your support

João Arvanas