BPC Refresh then Filter when Open

[Former Member]

Hey Experts,

I need to perform Automatic Filter on Column when Open the workbook, but after the Refresh execution (because the values are retrieved from another intersection that is updated in another report).

The filter is based on having a value = "3" in the first column (1270_D) which retrieve the data.

On the other column the (1270_EERP) the user insert data, but only for those" Account" that having the value "3".

I developed this Code for the Filter.

I = 27

X = False

While X = False

If Cells(I, 19).Value <> "3" Then

    Rows(I).Hidden = True

End If

I = I + 1

If Cells(I, 18).Value = "" Then

   X = True

End If

Wend

I tried to include it in the Workbook Open like that

Private Sub Workbook_Open()

Dim EPM As New FPMXLClientt.EPMAddInAutomation

Dim myreport As New FPMXLClient.EPMAddInAutomation

    myreport.RefreshActiveWorkBook

I = 27

X = False

While X = False

If Cells(I, 19).Value <> "3" Then

    Rows(I).Hidden = True

End If

I = I + 1

If Cells(I, 18).Value = "" Then

   X = True

End If

Wend

End Sub

But it doesn't work after i added the Refresh Part.

Do you have some Ideas to make it work?

Thanks

[former_member186338 · Accepted solution]

Hi Mohammad,

First of all ensure that you correctly referenced the FPMXLClient:

Then the code will work - tested:

Private Sub Workbook_Open()

Dim myreport As New FPMXLClient.EPMAddInAutomation

myreport.RefreshActiveWorkBook

Rows(9).Hidden = True 'hide single row for test

End Sub

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thank you for your answer.

FPMXClient is correctly referenced.

The Part of the Refresh is working Now correctly.

That Line "

Rows(9).Hidden = True 'hide single row for test

doesn't work.

I put my code under/ instead of that line, but it didn't work.

any suggestion where to put my code to start directly after the Refresh in Automatic way.

Thanks

Mohammad

[former_member186338 · Accepted solution]

Sorry, but I tested this code on my system. It's working absolutely correctly. No issues!

The only requirement - the file have to be saved locally, not on the BPC server.

May be your file is protected by password?

Vadim

[Former Member · Accepted solution]

Yes. If the File Saved locally it work correctly.

But is there a mode to make it work on the BPC server?

Mohammad

[former_member186338 · Accepted solution]

No way... Workbook_Open event will not trigger when opened from BPC server!

[dinesh_veluru · Accepted solution]

Hi Mohammad,

Please Try using  AFTER_WORKBOOK_OPEN will trigger the code both locally and from  the BPC Server.

Thanks,

Dinesh.V

[former_member186338 · Accepted solution]

Hi Dinesh,

You are 100% correct For some strange reason the authors of EPM Add-in disabled the Workbook_Open() when the file is opened using EPM Open from the server, but created the Custom event AFTER_WORKBOOK_OPEN that will trigger in any Excel with EPM Add-in installed (connected to BPC or not).

Vadim

P.S. The Workbook_Open() event is disabled when the file is opened using EPM Ribbon Open button from any location, even from local folder!

[gajendra_moond · Accepted solution]

That is great Dinesh. Both events can be used together to take care of online and offline versions of the application to provide a uniform behavior on opening workbook.

[former_member186338 · Accepted solution]

Ups, AFTER_WORKBOOK_OPEN will trigger in both cases - online and offline. It will not trigger if EPM Add-in is not installed....

[gajendra_moond · Accepted solution]

That is what I meant by "Offline" - No EPM Add-in installed.

[gajendra_moond]

Put this code in your module.

Public Function AFTER_REFRESH()

       AFTER_REFRESH = True

       'Put your code here

End Function

[former_member186338]

Not relevant, the code in Workbook_Open() has to work properly without AFTER_REFRESH() event!

Vadim

[gajendra_moond]

I have seen Workbook_Open not working correctly when opened from the server. However, save the file on the machine and open, it works.

[former_member186338]

The topic author has mentioned that the code in Workbook_Open() - "But it doesn't work after i added the Refresh Part." It means that the issue is with FPMXLClient.

Workbook_Open is not triggered when the file is opened from the server - this is correct. But it's not related to refresh - the whole procedure will not launch!