Run and show status of Power Query Refresh

[Mr Denove]

I have 5 power queries that I would like to trigger with vba and show the status of when thy have completed their update (not just started to refresh). Is it possible to capture the completion of each refresh and then show it on a sheet for example. This would also allow other users to use it.

 

[John_w]

You could do this with a class which handles the BeforeRefresh and AfterRefresh events of the query table associated with the table of each Power Query. The event handlers update a 'Status' sheet which shows the following values of each query: Table Name. Destination Cell, Refresh Start Time, Refresh End Time, Refresh Status.

First, add a new sheet to your workbook and name it "Status".

Add a new class module and name it clsQuery and paste in the following code:

'Class module - clsQuery

'Each instance of this class contains details about a specific query table and handles
'the BeforeRefresh and AfterRefresh events when the query table is changed.

Option Explicit

Private WithEvents pQueryTable As QueryTable

Private pStatusBaseCell As Range
Private pRefreshStartTime As Date
Private pRefreshEndTime As Date

Public Property Get QueryTable() As QueryTable
    Set QueryTable = pQueryTable
End Property

Public Property Set QueryTable(ByVal qt As QueryTable)
    Set pQueryTable = qt
End Property

Public Property Get StatusBaseCell() As Range
    StatusBaseCell = pStatusBaseCell
End Property

Public Property Set StatusBaseCell(baseCell As Range)
    Set pStatusBaseCell = baseCell
End Property


Private Sub pQueryTable_BeforeRefresh(Cancel As Boolean)
 
    pRefreshStartTime = Now
    With pQueryTable
        pStatusBaseCell.Resize(, 5).Value = Array(.ListObject.Name, "'" & .Destination.Worksheet.Name & "'!" & .Destination.Address, pRefreshStartTime, "", "Refresh Running")
    End With
 
End Sub


Private Sub pQueryTable_AfterRefresh(ByVal Success As Boolean)
 
    pRefreshEndTime = Now
    With pQueryTable
        If Success Then
            pStatusBaseCell.Resize(, 5).Value = Array(.ListObject.Name, "'" & .Destination.Worksheet.Name & "'!" & .Destination.Address, pRefreshStartTime, pRefreshEndTime, "Succeeded")
        Else
            pStatusBaseCell.Resize(, 5).Value = Array(.ListObject.Name, "'" & .Destination.Worksheet.Name & "'!" & .Destination.Address, pRefreshStartTime, pRefreshEndTime, "Failed")
        End If
    End With

End Sub

Add a new standard module and paste in the following code:

Option Explicit

'Dictionary which holds multiple clsQuery objects
Public queryTablesDict As Object 'Scripting.Dictionary

Public Sub Refresh_All_Queries_With_Status_Updates()
 
    Dim statusSheet As Worksheet, baseCell As Range
    Dim ws As Worksheet
    Dim table As ListObject
    Dim qt As QueryTable
    Dim thisQuery As clsQuery
    Dim key As Variant
    Dim i As Long
 
    Set statusSheet = ThisWorkbook.Worksheets("Status")
   
    If queryTablesDict Is Nothing Then
   
        'Create dictionary of queries and refresh them
 
        With statusSheet
            .Cells.ClearContents
            .Range("A1:E1").Value = Array("Table Name", "Destination Cell", "Refresh Start Time", "Refresh End Time", "Refresh Status")
            Set baseCell = .Range("A2")
            .Activate
        End With
 
        Set queryTablesDict = CreateObject("Scripting.Dictionary")
       
        i = 0
        For Each ws In ThisWorkbook.Worksheets
            For Each table In ws.ListObjects
                On Error Resume Next
                Set qt = table.QueryTable
                On Error GoTo 0
                If Not qt Is Nothing Then
                 
                    'Create a new instance of a clsQuery with this query table and add it to the dictionary
                 
                    Set thisQuery = New clsQuery
                    Set thisQuery.QueryTable = qt
                    Set thisQuery.StatusBaseCell = baseCell.Offset(i)
                    queryTablesDict.Add key:=table.Name, Item:=thisQuery
                    i = i + 1
                 
                    'Refresh this query
                 
                    qt.Refresh BackgroundQuery:=True
                    Set qt = Nothing
                End If
            Next
        Next

    Else
   
        'Refresh queries in dictionary
   
        statusSheet.Activate
        For Each key In queryTablesDict.Keys
            Debug.Print "key: "; key
            Set thisQuery = queryTablesDict.Item(key)
            thisQuery.QueryTable.Refresh BackgroundQuery:=True
        Next
       
    End If
   
End Sub

Now run the Refresh_All_Queries_With_Status_Updates procedure to refresh the queries in your workbook and see the updates on the "Status" sheet.

Edit 22-Aug-2024: updated the code in the standard module to check if the queryTablesDict dictionary exists and if so refresh the queries stored in it. This means you can run Refresh_All_Queries_With_Status_Updates multiple times, first to create the dictionary and run the initial queries and subsequently to refresh the queries.

 

[Mr Denove]

Thanks for the reply. Will need to wait til Monday to try it out, and possibly to understand it.
Will let you know.
Thanks again.

 

[johnny51981]

Ok, I'm salivating but also have questions.

What would need to be updated to allow this to play in Mr Denove's file (and now mine), if anything? Workbook specific and/or needing something for each Power Query needing refreshed? Also, is there a way to show if there are any errors, and if so, the count of those errors?

 

[John_w]

What would need to be updated to allow this to play in Mr Denove's file (and now mine), if anything? Workbook specific and/or needing something for each Power Query needing refreshed? Also, is there a way to show if there are any errors, and if so, the count of those errors?

You shouldn't need to do anything more than the steps I've described.

The Success argument in the AfterRefresh event says whether the refresh succeeded or not. I don't think it's possible to capture the refresh error number or description, if it failed. You could add an error count property to the clsQuery class, initialise it in the main procedure and increment it in the AfterRefresh event if Success = False and output the count on the Status sheet.

 

[Mr Denove]

You shouldn't need to do anything more than the steps I've described.

The Success argument in the AfterRefresh event says whether the refresh succeeded or not. I don't think it's possible to capture the refresh error number or description, if it failed. You could add an error count property to the clsQuery class, initialise it in the main procedure and increment it in the AfterRefresh event if Success = False and output the count on the Status sheet.

Morning, finally got a chance to check this and it works like a charm. This is the first code that I have found anywhere that actually does this.
Thank you John_w

Mr D

 

[johnny51981]

Oh my goodness. @John_w , this is absolutely brilliant!!! I am in love.

I'm not super great at VBA (yay plagiarism), so if you could throw some guidance my way on how to get the number of Errors (the blue numbers on Power Queries when there are errors) as a part of the Status table, that would be fantastic!

Thank you SO much @Mr Denove for asking this question at the most perfect time!

 

[Mr Denove]

Is it possible to ask further what is a Class Module as Ive never came across it before.

 

[johnny51981]

Ok, I was able to tailor this code to work exactly as I need it for all of my stuff. Truly GENIUS!

Now when I was asking about the error count, I was actually talking about the numbers in blue that are displayed in the Queries and Connections pane (pic provided).

Would it be possible to update the VBA to also show the queries errors, so it would include the 292 and 65 respectively.

Any idea?

 

[John_w]

Is it possible to ask further what is a Class Module as Ive never came across it before.

A class module is used to define custom objects. In this case, one of the members of the class is Private WithEvents pQueryTable As QueryTable which means instances of the class (objects) receive the BeforeRefresh and AfterRefresh events generated by the Excel QueryTable object.