AFTER_REFRESH doesn't work correctly when distributing

[Former Member]

Hi Experts,

I have a document that has to be distributed for many CENTERs. The reports have dynamic row members depending on the CENTER for which it will be distributed. The document has some extra formulas changed with AFTER_REFRESH function; and plenty of these formulas are based on the EPM.GetDataBottomRightCell of the reports.

The AFTER_REFRESH works perfectly when refreshing the whole workbook for a different CENTER.

The problem appears when opening the distributed document and the AFTER_REFRESH function is exected incorrectly. It seems that when calculating the EPM.GetDataBottomRightCell of the reports, it calculates always for the last CENTER refreshed in the document distributed.

To understand my example, I'll explain the sample with which I have been testing this functionality:

     Create a report with a dimension in rows that expands dynamically depending on another dimension (the one you will distribute for)

     In a new Module write a code similar to:

               Dim EPM as New FPMXLClient.EPMAddInAutomation

               Sub AFTER_REFRESH()

                    Dim bottomRightCell_Str as String

                    bottomRightCell_Str = EPM.GetDataBottomRightCell(Sheets("Sheet1"),"000")

                    Sheets("Sheet1").Range(bottomRightCell_Str).Value = "lastCell"

               End Sub

Then try to distribute this document for another CENTER that varys the number of members per row and open the result of the distribution.

Every time I try it I get the string "lastCell" in a place where it shouldn't appear!

Any help would be appreciated. I hope that if you try this simple example you will understand my issue.

Thanks,

j.Ignacio

[former_member186338]

Hi Ignacio,

I am unable to test the issue now, but we use different approach - not to use BPC distribution at all. Just not very complex VA macro that will perform generation of distributed files based on our requirements with full control of refresh etc.

Vadim

[Former Member]

Hi Vadim!

Where can I find the VBA code for the file distribution? What differences are there between the BPC distribution and this one?

Thank you very much.

J.Ignacio

[former_member186338]

It depends on what do you want from file distribution!

Scenario:

1. Template for distribution.

2. Generation of target files based on template with replacement of some members and with data refresh.

3. Storage of target files in some folder or folder structure.

4. Mail distribution (optional).

All steps can be done in VBA code that you can write yourself

Vadim