Refresh Power Queries Then Pivot Tables

Question

I am attempting to update about a dozen Power Queries, wait until those are completed, and then refresh all the Pivot Tables attached to them.

I have this VBA Script, I have put notes around the section that I am struggling with:

Public Sub UpdatePowerQueries()

Application.DisplayAlerts = True

For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect
Next Worksheet

'Start of trouble section
Dim StatusCheck As Long, Connection As WorkbookConnection, RefreshCheck As String, RefreshCheckLoop As Integer

On Error Resume Next
For Each Connection In ThisWorkbook.Connections
    StatusCheck = InStr(1, Connection.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
    If Err.Number <> 0 Then
        Err.Clear
        Exit For
    End If
If StatusCheck > 0 Then Connection.OLEDBConnection.BackgroundQuery = False
If StatusCheck > 0 Then Connection.Refresh

RefreshCheckLoop = 1

While RefreshCheckLoop < 15
    RefreshCheck = Connection.OLEDBConnection.Refreshing
    If RefreshCheck = True Then Application.Wait (Now + TimeValue("00:00:01"))
    RefreshCheckLoop = RefreshCheckLoop + 1
Wend
Next Connection

DoEvents

ActiveWorkbook.RefreshAll
'End of trouble section

For Each Worksheet In ActiveWorkbook.Worksheets
    If Worksheet.Visible Then
        Worksheet.Activate
        Range("A1").Select
        ActiveWindow.ScrollRow = 1
        ActiveWindow.ScrollColumn = 1
    End If
Next Worksheet
    
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Protect
Next Worksheet

Worksheets("Table Of Contents").Activate

ActiveWorkbook.Save

Application.DisplayAlerts = True

End Sub

Everywhere I read, it says to set the BackgroundQuery to false and it should wait until that finishes before moving onto the next section of refreshing the pivot tables, but I am still getting the annoying little yellow triangle telling me that the "Download did not complete." next to each query. I have all the Power Queries set to not update via the "RefreshAll" command.

Am I missing something?

I would suggest to check if it is still Refreshing before moving to the next one. docs.microsoft.com/en-us/office/vba/api/… and then DoEvents — Sorceri
On the first error in the loop you break out of the loop - what about the other connections after the one with the error? — Tim Williams
@TimWilliams, they all error...even though the data appears to refresh. — Femmer
@Sorceri, I have updated the logic above. I have added a loop to see if the connection is still refreshing and then a DoEvents after the Power Query refresh and before the RefreshAll. I am still having the same yellow triange. — Femmer
What I mean is that as soon as one connection errors within your For Each Connection In ThisWorkbook.Connections loop, you exit the loop. Unless there's only one connection per workbook? — Tim Williams