如何等待Power Query刷新完成?我已经尝试过"DoEvents"与"BackgroundQuery = False"一起使用，而且什么也没用 [英] How to wait for a Power Query refresh to finish? I've tried "DoEvents" with "BackgroundQuery = False", and more, nothing works

问题描述

设置:

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

问题描述

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

After using any combination of 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.

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

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. However, the problem with this method 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.

我尝试过的事情:

• 我已经阅读了所有说明使用"DoEvents"，"BackgrgoundQuery = False"和CalculateUntilAsyncQueriesDone方法和属性的StackOverflow(和其他网站资源).
• 我尝试创建一个类来在查询更新事件之前/之后创建，如该链接所建议(在下面的代码示例中未显示).
• 我尝试将直到/同时循环"与QueryTable方法的.Refreshing = True/False属性一起使用，以等待刷新完成.
• 我尝试将Excel菜单(菜单->数据->连接->属性)中的BackgroundQuery属性设置为False，如"subro"在此处建议的那样:

• 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 have tried creating a class to Create Before/After Query Update Events as suggested at this link (not shown in the code example below).
• I have tried using Do Until/While Loops with the .Refreshing = True/False property of the QueryTable method to wait for a refresh to finish.
• I have 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:

.

代码:

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

代码注释:

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

问题

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

感谢您的时间和关注. 如果我忘记提供任何信息，或者可能需要重新措辞以使这个问题更清楚，请在评论中让我知道.

Thank you for your time and attention to this issue. If I have forgotten to include any information or might need to reword something to make this question more clear, please let me know in the comments.

推荐答案

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

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.

请找到包含代码的示例文件的链接.
https://drive.google.com/open?id=1ZLxSMEXPLda3QhaQoTyGGv3_sC-tpN- X

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

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

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刷新完成?我已经尝试过"DoEvents"与"BackgroundQuery = False"一起使用，而且什么也没用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！