Implementing interdependent Excel Power Query synchronous background queries

Question

I have 3 queries in Excel Power Query (PQ), 1 of which is external and the other 2 are derived from it. I wish to run the external query first, and then the other queries in turn. The difficulty I am having is applying VBA code after the queries have finished having each completed synchronously and as background queries to prevent Excel freezing. The reason for VBA (as described below) might be resolved through other means, so am happy to consider alternatives.

The queries have some columns that return text =(formula) (via M in PQ). There is no way that I have found that will automatically return the text as formulas and PQ has limitations for defining custom columns with formulas (e.g. no Excel functions permitted). The VBA code is to control the order of queries (e.g. query 1 once done, fires event to trigger query 2 etc.) and to execute .Formula = .Formula to resolve the formula issue.

Attempted solutions and the reason for failing:

1. Worksheet_TableUpdate() event only gets raised when you add the query to Data Model. This disables run as background query option if added to Data Model.
2. To overcome the Data Model limitation, I tried the Worksheet_Change() event, but that gets raised too early (most likely at the point of table dimensions creation before filling it with data) so query 2 and 3 happen before query 1 finishes causing unexpected results as they have interdependent formulas.
3. Using

Do Until OLEDBConnection.Refreshing = False
    DoEvents
Loop 

to control when one query has finished before the next is set off has an Excel bug presumably related to DoEvents that prevents the loop from exiting unless I interrupt the execution and step through / set it off again

4. Using QueryTable object and its events is an issue because the external query returns XML that is nicely transformed implicitly by PQ and is governed by OLEDBConnection object. QueryTable (AFAIK and tested) doesn't handle XML at all well.

5. Dabbled with Windows APIs for an alternative to DoEvents courtesy of Chat GPT (dangerous I know), but didn't get anywhere. That's not to say it wouldn't be possible so happy to consider.

So, fundamentally, if you can provide a solution that allows for synchronous background interdependent queries whose output contains in part Excel formulas with native Excel functions in columns of the tables, that should cover the problem.

Answer 1

One thing I did was to have a module like the following:

Option Explicit

Dim sheetsToWatch as Collection
Dim delay as Double

'A button to kick it all off should call this subroutine
Sub ButtonRefresh_Click()
  Application.DisplayAlerts = False
  ActiveWorkbook.queries.FastCombine = True
  ActiveWorkbook.RefreshAll

  DoStuffWhenRefreshCompletes
End Sub

'Set sheetsToWatch and starts watching them
Sub DoStuffWhenRefreshCompletes()
  Dim sht as Worksheet

  Set sheetsToWatch = New Collection

  For Each sht In ActiveWorkbook.Sheets
    If (code to determine if this is a sheet to watch) Then
      sheetsToWatch.Add sht
    End If
  Next sht

  delay = TimeValue("00:00:03")

  DoStuffWhenSheetsToWatchComplete
End Sub


Sub DoStuffWhenSheetsToWatchComplete()
  For i = sheetsToWatch.Count To 1 Step -1
    Set sht = sheetsToWatch.Item(i)
    If not fAnyQueryTablesRefreshing(sht) Then
      DoStuffNowThatSheetQueriesDone sht
      sheetsToWatch.Remove i
    End If
  Next i

  If sheetsToWatch.Count <> 0 Then
    Call Application.OnTime(Now + delay, "DoStuffWhenSheetsToWatchComplete")
  Else
    AllDone
  End If
End Sub

Function fAnyQueryTablesRefreshing(sht as Worksheet) As Boolean
  Dim lo as ListObject
  Dim qt as QueryTable

  'queries directly within the sheet
  For Each qt In sht.QryTables
    If qt.Refreshing Then
      fAnyQueryTablesRefreshing = True
      Exit Function
    End If
  Next qt

  'queries within ListObjects contained in the sheet
  For Each lo in sht.ListObjects
    Set qt = lo.QueryTable
    If qt.Refreshing Then
      fAnyQueryTablesRefreshing = True
      Exit Function
    End If
  Next lo

  fAnyQueryTablesRefreshing = False
End Function

Sub AllDone()
  Application.ScreenUpdating = True
  Set sheetsToWatch = Nothing
End Sub

Sub DoStuffNowThatSheetQueriesDone(sht As Worksheet)
  'code to execute when all queries on a sheet have completed
End Sub

Obviously, my need (apply special formatting to each sheet via VBA once their queries complete) differs from yours because you don't have to watch several queries or take next steps per sheet, but the logic above worked for me.

You would probably want to modify it so that it only watches a single query or ListObject instead of watching all queries per sheet they are loaded to. You probably don't want to do RefreshAll either, instead going after that specific first query.