Select sheet after Power Query Update

I have a macro that refreshes all my power query connections. After this is refreshed, the process quickly goes through each worksheet to apparently update the tables on each sheet. The problem is that I want to select the "Main" sheet after this refresh is done, but no matter what, the tables are updated as the last step. I've tried to call another macro to select the main sheet. I've also tried putting in the application.wait command. Nothing works. It always updates the tables last. Here's what I've got so far with the code...

Sub PQ_Refresh()



Dim lCnt As Long

    
    With ActiveWorkbook
        For lCnt = 1 To .Connections.Count
          
          If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then
            .Connections(lCnt).OLEDBConnection.BackgroundQuery = False
          End If
        Next lCnt
    End With
    
    ActiveWorkbook.Connections("Query - CK Elevation").Refresh
ActiveWorkbook.Connections("Query - AF Discharge").Refresh
ActiveWorkbook.Connections("Query - AF nflow").Refresh
ActiveWorkbook.Connections("Query - HP Elevation").Refresh
ActiveWorkbook.Connections("Query - BD Discharge").Refresh
ActiveWorkbook.Connections("Query - IN Elevation").Refresh
ActiveWorkbook.Connections("Query - BC Discharge").Refresh




Sheets("Main").Select
    
    
End Sub

Try

Sheets("Main").Activate

Originally Posted by rorya

Try:

Application.calculateuntilasyncqueriesdone
Sheets("Main").Select

That didn't work either. It does seem to finish refreshing the query connections themselves, then the program will select the main sheet, unfortunately after that it updates all the data table associated with the queries, still leaving me on the wrong worksheet.

I am surprised this is giving me this much trouble!

That is correct. I even tried a different piece of code, same result....

Public Sub UpdatePowerQueries()


Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ThisWorkbook.Connections
lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn

Application.CalculateUntilAsyncQueriesDone
Sheets("Main").Select

End Sub

Rorya,

I tried your code and it had the same result - it cycled through all the worksheets and did NOT return to the Main worksheet.