Excel VBA Wait until Connection Refreshes before refreshing the next

r4ymond88

New Member
Joined
Jul 26, 2013
Messages
30
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!

VBA Code:
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
if your queries are OLEDB (probably they are) you may try:


VBA Code:
    ThisWorkbook.Connections(strNames(i)).OLEDBConnection.BackgroundQuery = False
    ThisWorkbook.Connections(strNames(i)).Refresh
' may be also
'    DoEvents
    Next i
 
Upvote 0
if your queries are OLEDB (probably they are) you may try:


VBA Code:
    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.
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,177
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top