VBA Code to Give Status of Refresh

[netspeedz]

I have the below VBA code that I have partially working but need some help to fully complete it.

The code loops through all Power Queries associated to the workbook. As it loops through the queries, text is displayed in a cell that gives the user what the current query being processed.

The code that I have (below) works and processes the first query in the query name array but then exits the loop (no errors returned). I know it exits the loop because the cell (I17 in this particular workbook) is never updated with the 'finish' text.

I ordinally tried to update the Excel status bar with the same information but couldn't find anything that would modify Excel status bar regarding query updates/status.

Would like assistance in the fix for the code to loop through all defined queries in the query array. Any help would be appreciated.

Here's the code:

Sub RefreshQueriesAndUpdateProgress()
    Dim wsCoins As Worksheet
    Dim queryNames As Variant
    Dim queryConnection As WorkbookConnection
    Dim queryName As Variant
    
    Set wsCoins = ThisWorkbook.Sheets("US Silver Coins") ' Name of sheet to place status on
    
    ' List query names to refresh
    queryNames = Array("Query - Precious Metal Spot Prices", "Query - Live Silver Price", "Query - Gold Ratio")
    
    For Each queryName In queryNames
        Set queryConnection = ThisWorkbook.Connections(queryName)
        
        wsCoins.Range("I17").Value = "Refreshing query: " & queryName
        queryConnection.Refresh
        
        ' Wait for the calculation to finish for the current query
        Do
            DoEvents ' Allow other processes to execute
        Loop Until Application.Ready And Not queryConnection.IsRefreshing
        
        ' Update the cell with the completion of the query refresh
        wsCoins.Range("I17").Value = "Query refreshed: " & queryName
    Next queryName
    
    wsCoins.Range("I17").Value = "All queries refreshed!"
End Sub

 

[Micron]

I can't see anything obvious except maybe your For Each loop. I've never used that against an array because it seems it's always used against a collection. Any array loop I can recall seeing used a counter instead. I suggest you put a break point at the start and F8 through the code to see what happens and check variable values and/or properties as you go.