设置:
- Windows 7 (在工作中)
- Windows 10 (在家中)
- Excel 2016 (版本4627，在工作中)
- Excel 2016 (版本8730，在家中)
- Power Query已设置为导入、追加和转换一个Excel文件夹。该步骤有效。
在使用任何等待Power Query完成刷新的技术后，根据下面展示的“我尝试过的事情”部分所述，可以显示消息框并执行任何其他代码，然而Power Query表格更新仍未完成，这可通过“查询和连接”窗格中的刷新指示器（旋转图标？）进行查看。
上述声明的例外是应用程序类的OnTime方法，如下面的“代码”部分所示，它似乎不会中断对Power Query刷新的轮询。问题在于，它使用硬编码的时间来暂停VBA代码，这将不总是有效，因为查询的数据大小、数量和持续时间会随时间变化而改变。
我尝试过：
- 我已经阅读了所有关于使用
DoEvents、BackgrgoundQuery = False和CalculateUntilAsyncQueriesDone方法和属性的StackOverflow（和其他网站资源）。 - 我尝试创建一个类来创建查询更新前/后事件，如这个链接所建议的（下面的代码示例中没有显示）。
- 我尝试使用Do Until/While Loops以及QueryTable方法的.Refreshing = True/False属性等待刷新完成。
- 我尝试根据"subro"在此处建议的方式（菜单栏 -> 数据 -> 连接 -> 属性）将Excel菜单中的BackgroundQuery属性设置为False，这里是菜单的图片：等待ActiveWorkbook.RefreshAll完成-VBA。
.
代码：
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://dev59.com/gGEh5IYBdhLWcg3wvVh3
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工作表中。代码显示了使用这样的空表格建议的网站链接。
问题：
- 由于Power Query没有内置回调来告诉Excel它已完成更新任何刷新过程，因此这是否是一个无望的事情？
- 如果这不是一个无望的事情，那么是否有其他方法（未在此处描述）可以用来触发错误，以某种方式使连接尚未完成刷新，或在连接完成时触发错误？（这里的想法是，如果错误不会停止查询完成，则可以将此错误作为可能检测到刷新是否已完成的一种方式进行捕获。）
- 是否有办法直接使用VBA探测连接流，以查找连接关闭或完成状态？
- 是否有办法通过调用外部程序（如C#或Python）直接访问刷新过程？
- 您能想到还有什么其他的方法可以尝试或测试以使其工作吗？我将继续自己寻找答案，但经过一整年的搜索，我感到有点运气不佳。
