如何等待Power Query刷新完成? [英] How to wait for a Power Query refresh to finish?

问题描述

设置:

• Windows 7(正在工作)
• Windows 10(在家)
• Excel 2016(内部版本4627)
• Excel 2016(在家构建8730)
• Power Query已设置为导入，附加和转换Excel文件的文件夹.此步骤有效.

使用任何技术等待Power Query完成刷新后，如我尝试过的事情:"中所述.在下面显示的部分中，可以根据查询和连接"窗格中显示的刷新指示符(旋转器?)图标显示一个消息框，并可以在Power Query表完成更新之前执行任何其他代码.

上述语句的例外是Application类的 OnTime 方法，如代码"部分中所示.下方的部分，似乎不会中断电源查询刷新的轮询.问题在于，它花费了硬编码的时间来暂停VBA代码，但是由于要查询的数据的大小，数量和持续时间会随着时间而变化，因此这并不总是有效.

我尝试过:

• 我已经阅读了所有说明使用 DoEvents ， BackgrgoundQuery = False 和 CalculateUntilAsyncQueriesDone 的StackOverflow(和其他网站资源).方法和属性.
• 我尝试创建一个类来.

  代码:

   私有Sub sht_sub_Refresh_AllConnections_dev()'名称:sht_sub_Refresh_AllConnections_dev目的:尝试使用VBA等待查询完成更新，然后再显示一条消息.'描述:等待一段硬编码的时间，然后再显示消息框.'状态:WIP.'Dev:需要一种查看连接流的方法，以某种方式检测连接完成的时间.'声明:'------------'Dim procName As String'存储此过程的名称.Dim qTblLst As QueryTables'查询表集合对象.Dim qTblObj As QueryTable'查询表对象.Dim conLst As Connections'连接收集对象.Dim conObj As WorkbookConnection'一个连接对象.Dim idx As Long'A循环计数器.'初始化:'---------------'procName =" sht_sub_Refresh_AllConnections_dev"'存储此过程的名称.Linit.ini_Setup_Project'根据需要设置项目.Set conLst = ThisWorkbook.Connections'设置连接列表对象.Set conObj = conLst.Item(conLst.Count)'设置初始连接对象.idx = 0'如果do循环继续无休止，则作为出口.'主要代码正文:'--------------''关闭每种连接类型的backgroundquery.对于每个conLst'对于每个连接对象，与conObj选择Case .Type'检查连接类型，情况1:如果是OLEDB连接，.OLEDBConnection.BackgroundQuery = False'将它的backgroundquery属性设置为false.情况2:如果它是ODBC连接，.ODBCConnection.BackgroundQuery = False'将其backgroundquery属性设置为false.结束选择结束于下一个conObjThisWorkbook.RefreshAll'刷新所有连接.'DEV:使用循环，DoEvent和以字母"zzzz"开头的查询名称如此处建议:'https://social.technet.microsoft.com/Forums/zh-CN/bc3f7748-8a52-498d-951c-4566b8adf45a/in-excel-2016-power-queries-dont-refresh-in-the-background-moremore?forum = powerquery'和这里:'https://www.myonlinetraininghub.com/excel-forum/vba-macros/pause-macro-until-power-queries-finished-刷新试图等到最后一个连接完成刷新.直到Linit.gvTbl_ZZZZZ.QueryTable.Refreshing = True'等待，直到最后一个表开始刷新，idx = idx + 1'增加循环计数，如果idx>3000然后退出执行如果循环时间更长，则退出3000次迭代，循环'，否则继续等待.VBA.DoEvents'在继续之前执行事件(不起作用).直到Linit.gvTbl_ZZZZZ.QueryTable.Refreshing = False'等待直到最后一个表完成刷新，idx = idx + 1'增加循环计数，如果idx>3000然后退出执行如果循环时间更长，则退出3000次迭代，循环'，否则继续等待.VBA.DoEvents'在继续之前执行事件(不起作用).'DEV:以下是尝试建立与'在代码继续执行之前完成刷新，如下所示:'https://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vbaApplication.CalculateUntilAsyncQueriesDone'此放置在此处以及刷新后.VBA.DoEvents'在继续之前执行事件(不起作用).Application.EnableEvents = False'也许关闭事件会有所帮助吗?(不...)，Application.ScreenUpdating = False'这在过程中被重置为下一行的参数:Application.OnTime DateAdd("s"，3，Now)，_"Lwksh.sht_sub_Msg_RefreshDone"'被调用的过程仅显示一个消息框.Application.EnableEvents = True'还原事件，Application.ScreenUpdating = True'恢复屏幕更新.'内存清理:'--------------'EXIT_CLEAN:procName =空设置qTblLst = Nothing设置qTblObj =否设置conLst = Nothing设置conObj = Nothingidx = 0结束子 

  代码注释:

  • 代码中任何以"Linit"开头的内容.是"Linit.ini_Setup_Project"在过程外部全局设置的对象或变量.步骤"INITIALIZATIONS:"中的过程调用.代码部分.
  • 例如，"Linit.gvTbl_ZZZZZ"对象变量"是指向空的一行表的对象变量，该表的名称以字符"zzzz"开头.并由Power Query生成并加载到Excel工作表中.该代码显示了指向网站的链接，并提出了使用此类空表的建议.

  问题:

  1. 这是由于Power Query没有内置的回调来使Excel知道它已完成更新任何刷新过程而导致的丢失的原因吗?
  2. 如果这不是丢失的原因，还有其他方法(此处未介绍)可以用于在连接尚未完成刷新的情况下以某种方式触发错误发生，或者在以下情况下触发错误:连接完成了吗?(这里的想法是，如果错误不会阻止查询结束，则可能会将该错误捕获为检测天气或刷新未完成的一种可能方式.)
  3. 是否可以使用VBA直接探测连接流以查找连接的关闭状态或完成状态?
  4. 是否可以通过对以C#或Python等其他语言编写的Excel外部程序的某些调用来直接访问刷新过程?
  5. 您能想到可以尝试或测试过的其他任何方法吗?我将继续自行寻找答案，但是经过整整一年的搜索，我感到有点不走运.

  解决方案

  我了解您@neurojelly的痛苦.我去过那里.但事实证明，该解决方案非常简单，并且没有使用VBA.在查询属性"窗口中，您需要取消选中启用后台刷新"然后使用 DoEvents .我肯定知道这是可行的，因为我已经使用这种方法已有一年多了.

  请找到包含代码的示例文件的链接.
  

  对于第二个问题，可以使用 Iferror/OnEror 方法检测查询是否返回错误，但不一定检查查询中是否存在错误.它确定查询本身是否返回错误弹出窗口，在运行VBA代码时默认会跳过该错误弹出窗口.此方法在大多数时间有效，但并非总是如此.

  Setup:

  • Windows 7 (at work)
  • Windows 10 (at home)
  • Excel 2016 (build 4627 at work)
  • Excel 2016 (build 8730 at home)
  • Power Query is setup to import, append, and transform a folder of Excel Files. This step works.

  After using any technique to wait for a Power Query to finish refreshing as described in the "Things I have tried:" 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.

  The exception to the above statement is the OnTime method of the Application class, shown in the "Code" 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.

  I tried:

  • I have read all the StackOverflow (and other website resources) that state the use of the DoEvents, BackgrgoundQuery = False, and CalculateUntilAsyncQueriesDone methods and properties.
  • I tried creating a class to Create Before/After Query Update Events as suggested at this link (not shown in the code example below).
  • I tried using Do Until/While Loops with the .Refreshing = True/False property of the QueryTable method to wait for a refresh to finish.
  • I tried setting the BackgroundQuery property in the Excel menu (menubar --> Data --> Connections --> Properties) to False as suggested by "subro" here: Wait until ActiveWorkbook.RefreshAll finishes - VBA, with an image of the Menu here:

  .

  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 = "sht_sub_Refresh_AllConnections_dev"    '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 "zzzz" 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 > 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 > 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("s", 3, Now), _
                         "Lwksh.sht_sub_Msg_RefreshDone"        '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 notes:

  • Anything in the code preceded by "Linit." is an object or variable that is set globally outside the procedure by the "Linit.ini_Setup_Project" procedure call in the "INITIALIZATIONS:" section of the code.
  • For example "Linit.gvTbl_ZZZZZ" is an object variable that points to an empty one row Table which has a name that stars with the characters "zzzz" 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.

  Questions:

  1. 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?
  2. 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).
  3. Is there any way to probe the connection stream directly using VBA to look for the connection closed or finished state?
  4. 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?
  5. 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.

  解决方案

  I understand your pain @neurojelly. I have been there. But as it turns out the solution is quite simple and is not using VBA. In the Query properties window, you need to uncheck the "Enable background refresh" and then use DoEvents. I know for sure this works as I have been using this method for over a year now.

  Please find the link to a sample file that has the code in it.
  https://drive.google.com/uc?export=download&id=1ZLxSMEXPLda3QhaQoTyGGv3_sC-tpN-X

  As for your second question, it is possible to use Iferror/OnEror method's to detect if a query returns an error, but it doesn't necessarily check for errors in the query. It identifies if the query itself is returning an error pop-up which is skipped by default while running the VBA code. This method works most of the time, but not always.

  这篇关于如何等待Power Query刷新完成?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！