title: How to wait for a Power Query refresh to finish?
body: <p>Setup:</p>
  <ul>
  <li>Windows 7 (at work)</li>
  <li>Windows 10 (at home)</li>
  <li>Excel 2016 (build 4627 at work)</li>
  <li>Excel 2016 (build 8730 at home)</li>
  <li>Power Query is setup to import, append, and transform a folder of Excel Files. This step works.</li>
  </ul>
  <p>After using any technique to wait for a Power Query to finish refreshing as described in the &quot;Things I have tried:&quot; section shown below, a message box can be displayed and any other code can be executed before the Power Query tables have finished updating according to the refreshing indicator (spinner?) icon shown in the Queries and Connections pane.</p>
  <p>The exception to the above statement is the <code>OnTime</code> method of the Application class, shown in the &quot;Code&quot; section below, which doesn't appear to interrupt the polling for a power query refresh. The problem is that it uses a hard coded amount of time to pause VBA code, and this will not always work since the size, amount, and duration of data being queried will change over time.</p>
  <p>I tried:</p>
  <ul>
  <li>I have read all the StackOverflow (and other website resources) that state the use of the <code>DoEvents</code>, <code>BackgrgoundQuery = False</code>, and <code>CalculateUntilAsyncQueriesDone</code> methods and properties.</li>
  <li>I tried creating a class to <a href="http://www.excelandaccess.com/create-beforeafter-query-update-events/" rel="nofollow noreferrer">Create Before/After Query Update Events</a> as suggested at this link (not shown in the code example below).</li>
  <li>I tried using Do Until/While Loops with the .Refreshing = True/False property of the QueryTable method to wait for a refresh to finish.</li>
  <li>I tried setting the BackgroundQuery property in the Excel menu (menubar --&gt; Data --&gt; Connections --&gt; Properties) to False as suggested by &quot;subro&quot; here: <a href="https://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba">Wait until ActiveWorkbook.RefreshAll finishes - VBA</a>, with an image of the Menu here:</li>
  </ul>
  <p><a href="https://i.stack.imgur.com/P17qU.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/P17qU.jpg" alt="Excel menu for setting the BackgroundQuery property" /></a>.</p>
  <p>Code:</p>
  <pre class="lang-vb prettyprint-override"><code>Private Sub sht_sub_Refresh_AllConnections_dev()
      'Name: sht_sub_Refresh_AllConnections_dev
      'Purpose: An attempt at using VBA to wait for Queries to finish updating before displaying a message.
      'Description: Waits for a hard coded period of time before dislpaying the message box.
      'State: WIP.
      'Dev: Needs a way to look at the connection stream to somehow detect when its finished.
      
      'DECLARATIONS:
      '------------'
      Dim procName As String              'Stores this procedure's name.
      Dim qTblLst As QueryTables          'A query table collection object.
      Dim qTblObj As QueryTable           'A query table object.
      Dim conLst As Connections           'A connection collection object.
      Dim conObj As WorkbookConnection    'A connection object.
      Dim idx As Long                     'A loop counter.
  
      'INITIALIZATIONS:
      '---------------'
      procName = &quot;sht_sub_Refresh_AllConnections_dev&quot;    'Store this procedure's name.
      Linit.ini_Setup_Project                            'Setup the project if needed.
      Set conLst = ThisWorkbook.Connections              'Set the connections list object.
      Set conObj = conLst.Item(conLst.Count)             'Set an initial connection object.
      idx = 0                                            'As an exit if the do loop continues without end.
      
      'MAIN CODE BODY:
      '--------------'
      'Turn off backgroundquery for each connection type.
      For Each conObj In conLst                           'For each connection object,
          With conObj
              Select Case .Type                               'Check the connection type,
              Case 1                                        'If its an OLEDB connection then,
                  .OLEDBConnection.BackgroundQuery = False    'Set it's backgroundquery property to false.
              Case 2                                        'If its an ODBC connection the,
                  .ODBCConnection.BackgroundQuery = False     'Set it's backgroundquery property to false.
              End Select
          End With
      Next conObj
      
      ThisWorkbook.RefreshAll                             'Refresh all connections.
      
      'DEV: Using loops, DoEvents and a query name starting with the letters &quot;zzzz&quot; as suggsted here:
      'https://social.technet.microsoft.com/Forums/en-US/bc3f7748-8a52-498d-951c-4566b8adf45a/in-excel-2016-power-queries-dont-refresh-in-the-background-anymore?forum=powerquery
      'and here:
      'https://www.myonlinetraininghub.com/excel-forum/vba-macros/pause-macro-until-power-queries-finished-refreshing
      'Attempt to wait until the last connection has finished refreshing.
      Do Until Linit.gvTbl_ZZZZZ.QueryTable.Refreshing = True   'Wait until the last table starts refreshing,
          idx = idx + 1                                           'Icrement a loop count,
          If idx &gt; 3000 Then Exit Do                              'If the loop goes longer then 3000 iterations exit,
      Loop                                                      'otherwise continue waiting.
      VBA.DoEvents                                              'Do events before continueing (doens't work).
      Do Until Linit.gvTbl_ZZZZZ.QueryTable.Refreshing = False  'Wait until the last table finishes refreshing,
          idx = idx + 1                                           'Icrement a loop count,
          If idx &gt; 3000 Then Exit Do                              'If the loop goes longer then 3000 iterations exit,
      Loop                                                      'otherwise continue waiting.
      VBA.DoEvents                                              'Do events before continueing (doens't work).
      'DEV: The following is an attempt to get connections to
      '     finish refreshing before code continues as suggested here:
      'https://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba
      Application.CalculateUntilAsyncQueriesDone         'This is placed here as well as after the refresh.
      VBA.DoEvents                                              'Do events before continueing (doens't work).
      Application.EnableEvents = False                          'Maybe turning off events helps? (nope...),
      Application.ScreenUpdating = False 'This is reset in the procedure called as an argument to the next line:
      Application.OnTime DateAdd(&quot;s&quot;, 3, Now), _
                         &quot;Lwksh.sht_sub_Msg_RefreshDone&quot;        'The called procedure just displays a message box.
      Application.EnableEvents = True                           'Restore events,
      Application.ScreenUpdating = True                         'Restore screen updating.
      
      'MEMORY CLEANUP:
      '--------------'
  EXIT_CLEAN:
      procName = Empty                                     
      Set qTblLst = Nothing
      Set qTblObj = Nothing
      Set conLst = Nothing
      Set conObj = Nothing
      idx = 0
  End Sub
  </code></pre>
  <p>Code notes:</p>
  <ul>
  <li>Anything in the code preceded by &quot;Linit.&quot; is an object or variable that is set globally outside the procedure by the &quot;Linit.ini_Setup_Project&quot; procedure call in the &quot;INITIALIZATIONS:&quot; section of the code.</li>
  <li>For example &quot;Linit.gvTbl_ZZZZZ&quot; is an object variable that points to an empty one row Table which has a name that stars with the characters &quot;zzzz&quot; and is generated by Power Query and loaded to an Excel sheet. The code shows the link to the website where the suggestion for using an empty table like this was made.</li>
  </ul>
  <p>Questions:</p>
  <ol>
  <li>Is this a lost cause due to Power Query not having a built in callback to let Excel know that it has finished updating any refresh processes?</li>
  <li>If this is not a lost cause, is there any other way, not described here, that could be used to trigger an error to occur in some way if a connection has not yet finished refreshing, or to trigger the error when a connection is finished? (The thinking here being that this error could be trapped as a possible way of detecting weather or not the refresh has completed provided the error doesn't stop the query from finishing).</li>
  <li>Is there any way to probe the connection stream directly using VBA to look for the connection closed or finished state?</li>
  <li>Is there any way to directly access the refresh process via some call to a program outside of Excel written in some other language such as C# or Python?</li>
  <li>Can you think of anything else that might be tried or tested to make this work? I'll keep searching for an answer on my own, but after a full year of searching I'm feeling a bit out of luck.</li>
  </ol>