VBA to refresh multiple background queries, then wait for completion

[cr731]

I have a file with 2 sets of queries (2 queries per set for a total of 4 queries), where the first 2 queries must be refreshed first, then the next 2 refreshed after that. They are Power Query queries where the second 2 use the data from the first 2.

If I do this manually, I right-click and refresh the first two, wait for those to finish, then refresh the next 2 queries.

I'd like to automate this with VBA but what I would really like to do is refresh the first 2 queries and allow background refresh to take place, but then wait until they are complete before proceeding. The only way I know how to do this with VBA is the following

Sheet1.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False

This works, but then it means query #1 must refresh, then #2, but I would really prefer to have background refresh enabled to allow both to refresh at the same time (they take a few minutes each, so it's nice to allow them to work at the same time).

Is there any way to refresh a grouping of queries with background refresh enabled, then wait for that to finish before proceeding?

 

[Fazza]

An untested idea. Use a simple loop

do while SheetA.ListObjects(1).refreshing or SheetRef.ListObjects(2).refreshing
loop

If this causes problems, first thought is to put a doevents in the loop