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!
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