Specify Power Query order of execution and force individual query refresh complete before next

andredl
Posts: 47
Joined: Wed Sep 23, 2015 9:56 pm

Specify Power Query order of execution and force individual query refresh complete before next

Postby andredl » Mon Feb 06, 2017 1:50 pm

Hi all,

I have been trying to specify the order of execution of my Power Queries using VBA, but can't seem to get it to work as intended. I found this suggestion https://social.technet.microsoft.com/Forums/en-US/d4e3816f-e2a6-49f5-83eb-30bf8c65ef48/is-it-possible-to-order-refresh-connections-within-power-query-?forum=powerquery by Gil Raviv, but when I copy the format I get a "Run-time error 9: Subscript out of range". My VBA skills are very limited, so I'm not having any success in fixing the error.

This is what I've tried, using a query named ReferenceDate that is activated by pressing a button:

Private Sub RefreshButton_Click()
ActiveWorkbook.Connections("ReferenceDate").Refresh
End Sub

...gives ther Run-time error

Private Sub RefreshButton_Click()
ActiveWorkbook.Connections(ReferenceDate).Refresh
End Sub

... (removed the quotation marks), also gives the same Run-time error

Private Sub RefreshButton_Click()
ThisWorkbook.Connections(ReferenceDate).Refresh
End Sub

...(changed ActiveWorkbook to ThisWorkbook), same error

Private Sub RefreshButton_Click()
ThisWorkbook.Connections("ReferenceDate").Refresh
End Sub

...(added quotation marks), same error

Private Sub RefreshButton_Click()
ThisWorkbook.Queries("ReferenceDate").Refresh
End Sub

...(changed Connections to Queries, out of desperation more so than good reason), gives Run-time error 438: Object doesn't support this property or method

Private Sub RefreshButton_Click()
ThisWorkbook.Queries(ReferenceDate).Refresh
End Sub

...(removed quotation marks), gives Run-time error -2147352565 (8002000b): The index is out of bounds.

I'm not really finding any of the error messages helpful, but according to Gil Raviv's post it seems it must be possible. The VBA module is linked to the workbook as a whole.

Does anyone know what I'm missing here?
André
Last edited by andredl on Tue Feb 07, 2017 2:06 pm, edited 2 times in total.

MattAllington
Posts: 873
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Specify Power Query order of execution

Postby MattAllington » Mon Feb 06, 2017 8:11 pm

André, where did you get the name of your connection? Is it possible you have the name wrong? I would try to turn on the macro recorder, then refresh one query, then turn off the macro recorder and see what you get. This is the standard pattern to learn vba.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

andredl
Posts: 47
Joined: Wed Sep 23, 2015 9:56 pm

Re: Specify Power Query order of execution

Postby andredl » Tue Feb 07, 2017 1:50 pm

Hi Matt, I tried the Macro recorder, but when I finish the recording and go back to view the code, it's blank. I'm using Excel 2016.

I have however, after extensive searching, been able to solve the issue thanks to a combination of forums (and a lot of trial and error!).

What I've been missing, was the fact that Power Query actually adds the string "Query - " to the front of each query name. You can see this by clicking on Data -> Connections, and looking at the Name field. I didn't realise this, so none of my permutations I listed in the original post worked.

I needed to type
Sub RefreshSequence()
ThisWorkbook.Connections("Query - ReferenceDate").Refresh
End Sub


for my query to refresh from within VBA.

The refresh order is then controlled by the sequence in which the connections are specified.
I soon ran into a hurdle, however, when I realised that VBA doesn't wait for each query to finish refreshing before continuing on to initiate the next refresh.

After even more extensive searching, I found that by adding a subroutine that makes the application wait a second after each refresh, you can basically force each query refresh (or groups of queries) to complete, before continuing on to the next.

The subroutine I used is
Sub Wait()

Dim PauseTime, Start, Finish, TotalTime
PauseTime = 1 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.

End Sub
.

Calling that up in my query sequence would then look like this:
Sub RefreshSequence()
ThisWorkbook.Connections("Query - ReferenceDate").Refresh
Wait
ThisWorkbook.Connections("Query - SecondQuery").Refresh
ThisWorkbook.Connections("Query - ThirdQuery").Refresh
End Sub


Just insert the Wait subroutine after each query that needs to completely refresh before continuing on to the next.


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 2 guests