Macro for refreshing particular report.

[Former Member]

Hello,
Could any help me how to refresh a particular report using VBA code?

I'm using EPM 10 and working with lots of Excel files. I want to make a VBA code which can refresh the certain report and which could be anywhere in Workbook. Right now I've to open each Excel file and go to Worksheet and find the report and refresh it manually which is a lengthy process, so I want to make VBA code which can refresh report anywhere in Workbook, it could be done by entering Report Id, Sheet_Name, and refresh it or any other methods. I've found EPM functions for refreshing active report, worksheet, and workbook but haven't found any EPM function to refresh a particular report. I would be very grateful if I can help to resolve this issue, also any other methods would be a great help! Thank you.

[former_member186338 · Accepted solution]

Unable to understand, what's the issue:

Option Explicit

Dim epm As New EPMAddInAutomation

Public Sub TestRefresh()
    RefreshReport ThisWorkbook.Worksheets("Sheet1"), "000"
End Sub

Public Sub RefreshReport(wshSheet As Worksheet, strReportID As String)
    wshSheet.Activate
    wshSheet.Range(epm.GetDataTopLeftCell(wshSheet, strReportID)).Activate
    epm.RefreshActiveReport
End Sub

We activate sheet with report

then we activate top left cell of required report

now it will be active report.

[former_member186338 · Accepted solution]

P.S. before doing it we can save current active sheet and active range and restore after refresh!

Public Sub RefreshReport(wshSheet As Worksheet, strReportID As String)
    Dim wshCurrent As Worksheet
    Dim rngCurrent As Range
    
    Set wshCurrent = ThisWorkbook.ActiveSheet
    Set rngCurrent = Application.ActiveCell
    wshSheet.Activate
    wshSheet.Range(epm.GetDataTopLeftCell(wshSheet, strReportID)).Activate
    epm.RefreshActiveReport
    wshCurrent.Activate
    rngCurrent.Activate
End Sub

[Former Member · Accepted solution]

Hello Mr. Kalinin,

Thank you very very much sir, it worked!