Refresh Hidden Sheet data for Chart

[former_member208963]

Hello people, I'm trying to expand a hidden sheet data to refresh a chart using these data, (in BPC 10 nw sp 17 patch 2)

The problem is that Data sheet is not refreshed, don't know the reason. Maybe someone can help me.

If I unhide the data sheet, and refresh it using standard refresh buton, it works. But I want to hide data sheet.

I remember doing this with EVDRE in BPC 7 and the procesdure was more or less the same:

Disable events/ screenupdating -> Unhide Data Sheet -> refresh -> Hide Data sheet -> Enable events/ screenupdating

Here is the VBA function I'm using:

   Application.EnableEvents = False
   Application.ScreenUpdating = False

    Dim EPMOB  As EPMAddInAutomation

    Set EPMOB = New EPMAddInAutomation

    Sheets("Chart Data").Visible = True


    ActiveWorkbook.Sheets("Chart Data").Select


    EPMOB.RefreshActiveSheet


    Sheets("Chart Data").Visible = False

    Sheets("Chart").Select    

    Application.EnableEvents = True
    Application.ScreenUpdating = True

Thanks for your help.

[former_member186338 · Accepted solution]

Hi Robert,

Just tested your code (with one extra line for test) - working fine:

Public Sub Test()

Application.EnableEvents = False

Application.ScreenUpdating = False

Sheets("Chart Data").Range("B8:E10").Clear 'Clear the EPM report data area to ensure refresh!

Dim EPMOB  As EPMAddInAutomation

Set EPMOB = New EPMAddInAutomation

Sheets("Chart Data").Visible = True

ActiveWorkbook.Sheets("Chart Data").Select

EPMOB.RefreshActiveSheet

Sheets("Chart Data").Visible = False

Sheets("Chart").Select

Application.EnableEvents = True

Application.ScreenUpdating = True

End Sub

Vadim

[former_member208963 · Accepted solution]

Hi Vadim than, Thanks for your reply, I'm still getting blank results using the macro. but there is data in report.

The problem was that I was calling the refresh chart info from an existing EVDRE report/sheet.

I wanted to use this report because it's the classic menu report with diferent steps and calls.

If i cut/paste the button to the CHART sheet (EPM 10), it's working... strange....

At least I can refresh in the chart sheet, but I cannot call macro from this MENU Evdre sheet.

I'm getting some Clear Cache errors and i have to reconnect to. see again reports... maybe that's because the EVDRE/EPM mixing reports...

thanks

[former_member186338 · Accepted solution]

Hi Robert,

Always test on the new simple report. I have done it and everything is working properly. I have no idea how this code can coexist with other procedures in your report...

B.R. Vadim