Is there a way to tell which power query updated when updating multiple at a time?

[seniorjerry]

Hey I have an excel file that has a lot of connections and have a VBA program that gets all connections in the workbook and updates them. I have been able to successfully set up the AfterRefresh() event to figure out whether a query has been updated properly or not however I am unsure of if their is a way to know which one triggered the event unless I do them one at a time. Is their a way to get the query / query name in the AfterRefresh() event without having to run one query at a time?

Here is the current code that runs one query at a time. If I run multiple at once I could run into the issue of them finishing out of order and then not knowing which one just finished

Please let me know if you need any more information that I can provide. Thanks

Option Explicit
Private WithEvents table As Excel.QueryTable
Private currentIndex As Long
Private tables As Variant

Private Sub table_AfterRefresh(ByVal Success As Boolean)
    
    If Success Then
        currentIndex = currentIndex + 1
        'Then move on to next query
    Else
        ' Retry updating the query once
    End If
    
    If Success And currentIndex <= UBound(tables) Then
        Set table = tables(currentIndex)
        table.Refresh
    End If
End Sub

Public Sub UpdateTables()
    tables = 'List of all queries
    currentIndex = 0
    Set table = tables(currentIndex)
    table.Refresh
End Sub

 

[RoryA]

Depending on what info you actually want, you could output the table.Name or table.Workbookconnection.name somewhere - e.g. the immediate window.

 

[seniorjerry]

Depending on what info you actually want, you could output the table.Name or table.Workbookconnection.name somewhere - e.g. the immediate window.

Ya i am mainly looking to try and refresh it one more time if it did not succeeded since the API data source I am using can close the connect every once in a while and would like to retry the query if it failed. What would you do to output the name of the table that triggered the event?

 

[RoryA]

If you just want to refresh it again, then table.refresh should be all you need.

 

[seniorjerry]

For anyone in the future that has a similar issue to me I solved this by creating multiple with events objects IE

Private WithEvents table1 As Excel.QueryTable
Private WithEvents table2 As Excel.QueryTable

Private Sub table1_AfterRefresh(ByVal Success As Boolean)
    If Success Then
        'It Worked
    Else
        ' It Failed
    End If
End Sub
Private Sub table2_AfterRefresh(ByVal Success As Boolean)
    If Success Then
        'It Worked
    Else
        ' It Failed
    End If
End Sub

Public Sub UpdateTables()
    table1 = YOUR TABLE
    table2 = YOUR TABLE
    table1.Refresh
    table2.Refresh
End Sub