Excel VBA Wait until Connection Refreshes before refreshing the next

[r4ymond88]

Hello Forum,

I hope you're all doing well.

I've built a set of Power Queries that take in two data sources, complete some calculations which are attached to Query number 2 and then outputs an Import template. The obvious issue is that each time the workbook template gets new data, I need to refresh all the data but it needs to happen in a sequence otherwise the calculations won't populate in the right order and I'd have to click refresh all several times which is a silly experience for the user.

I'm not a VBA expert but was able to put together the below with some online searches, this works perfectly from the sequencing perspective but I'm missing a critical point. How can I wait until each query finishes the refresh before moving onto the next. I tried application.wait but this method isn't working well and if I use this approach for other workbooks, the wait time could be different.

Any help with this would be greatly appreciated.

Thanks!

Sub LoopForNextDynamic()
'declare a variant array
   Dim strNames() As String
   Dim wb As Workbook
   Set wb = ThisWorkbook
'initialize the array
   ReDim strNames(1 To 3)
'populate the array
    strNames(1) = "Query - Query1"
    strNames(2) = "Query - Query2"
    strNames(3) = "Query - IMPORT_Template"

'declare an integer
   Dim i As Integer
'loop from the lower bound of the array to the upper bound of the array - the entire array
   For i = LBound(strNames) To UBound(strNames)
'show the name in the immediate window
    
    ThisWorkbook.Connections(strNames(i)).Refresh
    Application.Wait (Now + TimeValue("00:00:20"))
    
    Next i
End Sub

 

[Kaper]

if your queries are OLEDB (probably they are) you may try:

    ThisWorkbook.Connections(strNames(i)).OLEDBConnection.BackgroundQuery = False
    ThisWorkbook.Connections(strNames(i)).Refresh
' may be also
'    DoEvents
    Next i

 

[r4ymond88]

if your queries are OLEDB (probably they are) you may try:

    ThisWorkbook.Connections(strNames(i)).OLEDBConnection.BackgroundQuery = False
    ThisWorkbook.Connections(strNames(i)).Refresh
' may be also
'    DoEvents
    Next i

Thanks! I've already disabled background refresh from the Query properties. I tried using this way but it yields the same result as the original code where Query# 3 is left with just 7 rows instead of 85 rows. Query #3 populates from data off of Query #2.

 

[Kaper]

Have you tried also DoEvents?

 

[r4ymond88]

Have you tried also DoEvents?

Yeah, it's still doing the same thing. What's strange is that even if I use Application.Wait for 20 seconds there are times that Query#3 just refreshes with 7 rows instead of 85.

 

[r4ymond88]

Just updating here - the only way I got this to work, unfortunately, was to re-run the refresh after a 10 second wait. I tried to do a Do Until Loop where it refreshed until refreshing on connections was false but it still didn't yield the same results. Frustrating how there isn't a run order for the queries. Anyways, I also added a tab where the connection names are listed and sorted so that it uses that array as the string values rather than having them hard coded into the VBA code. Here's my final code for now.

Sub RefreshData()
'declare a variant array

On Error GoTo Error_Handler
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

Dim strNames() As String
Dim LRow As Long
Dim wb As Workbook
Dim rng As Range

Set wb = ThisWorkbook
LRow = wb.Sheets("Conns").Range("A" & Rows.Count).End(xlUp).Row

Set rng = wb.Sheets("Conns").Range("A2:A" & LRow)

'Filter Based on Run Order
    wb.Sheets("Conns").ListObjects("WorkbookConnections_Table"). _
        Sort.SortFields.Clear
      
    wb.Sheets("Conns").ListObjects("WorkbookConnections_Table"). _
        Sort.SortFields.Add2 Key:=Range( _
        "WorkbookConnections_Table[[#All],[Run Order]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
      
    With wb.Sheets("Conns").ListObjects("WorkbookConnections_Table" _
        ).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Dim CellValue As Integer

CellValue = 0

For Each Row In rng
   
    For Each Cell In Row
    Dim strName As String
   
    strName = Cell.Value
   
        wb.Connections(strName).OLEDBConnection.BackgroundQuery = False
       
        wb.Connections(strName).OLEDBConnection.Refresh
       
        Application.Wait (Now + TimeValue("00:00:10"))
       
        wb.Connections(strName).OLEDBConnection.Refresh
    DoEvents
   
    Next
Next
   
Error_Handler_Exit:
    On Error Resume Next
   
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With

Exit Sub

Error_Handler:
    MsgBox "MS Excel has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: LoopAsExcel" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit

End Sub