How can I refresh multiple sheets (in specific order) in an Excel workbook using EPM references?

Question

I have a workbook with about 50 sheets to be refreshed in a certain order (to avoid #rfr errors, since the sheets build off of one another).

The refresh is done via the EPM add in for Excel. I have activated the FPMXLclient functions and have attempted to write some code. I am very inexperienced with coding and logic. In the workbook the macro needs to start at the last tab, wait for the sheet to refresh, then move on to the next tab (and so on...). Below is an example of some of the VBA code I have written:

    Dim refreshList
        refreshList = Array("BS Analytic", "Balance Sheet")
        'There are more than just the 2 in the array (~50)
    Sub test_loop()
        Dim I
        For I = LBound(refreshList) To UBound(refreshList)
        MsgBox refreshList(I)
        Next I
    End Sub
    'Vba to refresh data
    Dim client As New EPMAddInAutomation
    Sub Refresh_Click()
        client.Refresh
    End Sub
    Sub AFTER_REFRESH()
        MsgBox "done"
    End Sub

Other info: This involves BPC and SAP too.

Answer 1

Assuming that your addin refreshes the active sheet, something like this in your loop might work:

Dim Sh As Worksheet
Set Sh = WorkSheets(RefreshList(I))
Sh.Activate
Client.Refresh