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]

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.

Sorry, I don't know enough about Power Query to know if that is possible and how you would do it with the QueryTable events.

I found Power Query: Trying to Extract Errors, but that is purely PQ and I don't know how you could get the error counts with VBA.

 

[Mr Denove]

Thank you, looks like I have alot to learn but will get there.

 

[florismk]

Would there be a similar possibility for connection-only queries? I have a workbook with four connection-only queries that pull data from the web, and then a NestedJoin query that combines them all into a QueryTable. When I refresh the NestedJoin, the four underlying queries are also refreshed, and I'd love to monitor/display the overall progress of the five.

 

[John_w]

Would there be a similar possibility for connection-only queries? I have a workbook with four connection-only queries that pull data from the web, and then a NestedJoin query that combines them all into a QueryTable. When I refresh the NestedJoin, the four underlying queries are also refreshed, and I'd love to monitor/display the overall progress of the five.

I tried the code with 2 connection-only queries to a SQL Server database and it works correctly.

 

[RoryA]

A connection-only query will not have a querytable associated with it, so this approach couldn't monitor those specifically, only the final result query.

 

[John_w]

I thought that might be true, but this code creates a connection-only query and it has a querytable associated with it. Maybe it's different for connections to SQL databases.

Public Sub vEmployee_Update()

    'strConnection = "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ";Trusted_Connectionection=Yes"
    'strConnection = "Provider=MSOLEDBSQL;Server=" & Server_Name & ";Database=" & Database_Name & ";Trusted_Connectionection=Yes"

    'For Oracle
'    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="OLEDB;Provider=OraOLEDB.Oracle;Data Source=myDB;OSAuthent=1;", _
'        Destination:=Range("$A$1")).QueryTable
'    End With
    
    Dim ServerName As String, DatabaseName As String
    Dim table As ListObject
    Dim Connection As String, CommandText As String
    Dim p1 As Long, p2 As Long
    
    ServerName = Environ("COMPUTERNAME")
    DatabaseName = "AdventureWorks2019"
    
    With ThisWorkbook.Worksheets("vEmployee")
    
        Set table = Nothing
        On Error Resume Next
        Set table = .ListObjects(1)
        On Error GoTo 0
        
        If table Is Nothing Then
        
            Set table = .ListObjects.Add(SourceType:=0, _
                Source:="OLEDB;Provider=MSOLEDBSQL; Server=" & ServerName & "; Database=" & DatabaseName & "; Trusted_Connection=Yes", _
                Destination:=.Range("$A$1"))
            With ThisWorkbook.Connections(1)
                .Name = "vEmployee"
                .Description = "Connection to vEmployee view"
            End With
            With table.QueryTable
                .CommandText = "SELECT vEmployee.FirstName, vEmployee.LastName, vEmployee.JobTitle, vEmployee.EmailAddress FROM " & DatabaseName & ".HumanResources.vEmployee vEmployee"
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = "Table_Query_from_SQL_Server_" & DatabaseName
            End With
           
        Else
        
            Connection = table.QueryTable.Connection
            p1 = InStr(Connection, "Server=") + Len("Server=")
            p2 = InStr(p1, Connection, ";")
            Connection = Left(Connection, p1 - 1) & ServerName & Mid(Connection, p2)
            p1 = InStr(Connection, "Workstation ID=") + Len("Workstation ID=")
            p2 = InStr(p1, Connection, ";")
            Connection = Left(Connection, p1 - 1) & ServerName & Mid(Connection, p2)
            p1 = InStr(Connection, "Database=") + Len("Database=")
            p2 = InStr(p1, Connection, ";")
            Connection = Left(Connection, p1 - 1) & DatabaseName & Mid(Connection, p2)
            table.QueryTable.Connection = Connection
            
            CommandText = table.QueryTable.CommandText
            p1 = InStr(CommandText, "FROM ") + Len("FROM ")
            p2 = InStr(p1, CommandText, ".")
            CommandText = Left(CommandText, p1 - 1) & DatabaseName & Mid(CommandText, p2)
            table.QueryTable.CommandText = CommandText
            
            table.QueryTable.ListObject.DisplayName = "Table_Query_from_SQL_Server_" & DatabaseName
            
        End If
        
        table.QueryTable.Refresh BackgroundQuery:=False
        
    End With
         
End Sub

 

[RoryA]

That code creates a table. That is not a connection only query.

 

[John_w]

Ok thanks Rory. I've misunderstood the Queries & Connections part of the UI.

 

[RoryA]

They do make it confusing!
Connection only queries appear like this: