vba 如何检查连接刷新是否成功
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43548543/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check whether Connection Refresh was successful
提问by Greg Lovern
In Excel 2016 VBA, I'm refreshing several queries like this:
在 Excel 2016 VBA 中，我刷新了几个这样的查询：
MyWorkbook.Connections(MyConnectionName).Refresh
After the code is done, and no errors are encountered, I see that the hourglass icons for most of the queries are still spinning for several seconds.
代码完成后，没有遇到错误，我看到大多数查询的沙漏图标仍在旋转几秒钟。
Is it possible to check for success AFTER all the refreshes are completed? I'm concerned that my code isn't going to know if an error happens after the code finishes but before the queries are done refreshing.
是否可以在所有刷新完成后检查是否成功？我担心我的代码不会知道在代码完成之后但在查询完成刷新之前是否发生错误。
BTW I don't want to do a RefreshAll, because some of the queries are dependent on others (uses them as a source). I refresh them in a certain sequence so that dependent queries are refreshed after the queries they are dependent on.
顺便说一句，我不想执行 RefreshAll，因为某些查询依赖于其他查询（将它们用作源）。我按照一定的顺序刷新它们，以便在它们所依赖的查询之后刷新依赖查询。
UPDATE:
更新：
I see that the Connection objects have a read-only RefreshDate property, which at first glance looked like it could be used to do this check:
我看到 Connection 对象有一个只读的 RefreshDate 属性，乍一看似乎可以用来做这个检查：
MyWorkbook.Connections(MyConnectionName).OLEDBConnection.RefreshDate
HOWEVER, it doesn't seem to be getting set. I get an error trying to check it. If I set a Variant variable to that RefreshDate property, the variable shows as "Empty". The source is a SQL server database.
然而，它似乎并没有被设置。我在尝试检查时出错。如果我将 Variant 变量设置为该 RefreshDate 属性，则该变量显示为“空”。源是 SQL 服务器数据库。
回答by Mathieu Guindon
The QueryTableobject exposes two events: BeforeRefreshand AfterRefresh.
该QueryTable对象公开两个事件：BeforeRefresh和AfterRefresh。
You need to change your paradigm from procedural/imperative to event-driven.
您需要将您的范式从程序/命令式更改为事件驱动式。
Say you have this code in ThisWorkbook(won't work in a standard procedural code module, because WithEventscan only be in a class):
假设你有这个代码ThisWorkbook（不能在标准的程序代码模块中工作，因为WithEvents只能在一个类中）：
Option Explicit
Private WithEvents table As Excel.QueryTable
Private currentIndex As Long
Private tables As Variant
Private Sub table_AfterRefresh(ByVal Success As Boolean)
Debug.Print table.WorkbookConnection.Name & " refreshed. (success: " & Success & ")"
currentIndex = currentIndex + 1
If Success And currentIndex <= UBound(tables) Then
Set table = tables(currentIndex)
table.Refresh
End If
End Sub
Public Sub Test()
tables = Array(Sheet1.ListObjects(1).QueryTable, Sheet2.ListObjects(1).QueryTable)
currentIndex = 0
Set table = tables(currentIndex)
table.Refresh
End Sub
The tablesvariable contains an array of QueryTableobjects, ordered in the order you wish to refresh them; the currentIndexvariable points to the index in that array, for the QueryTableyou want to act upon.
该tables变量包含一个QueryTable对象数组，按照您希望刷新它们的顺序排列；该currentIndex变量指向该数组中的索引，用于QueryTable您要对其进行操作。
So when Testruns, we initialize the tablesarray with the QueryTableobjects we want to refresh, in the order we want to refresh them.
所以当Test运行时，我们tables用QueryTable我们想要刷新的对象初始化数组，按照我们想要刷新它们的顺序。
The implicit, event-driven loop begins when table.Refreshis called and the QueryTablefires its AfterRefreshevent: then we report success, and update the event-provider tableobject reference with the next QueryTablein the array (only if the refresh was successful), and call its Refreshmethod, which will fire AfterRefreshagain, until the entire array has been traversed or one of them failed to update.
隐式的、事件驱动的循环在table.Refresh被调用时开始并QueryTable触发其AfterRefresh事件：然后我们报告成功，并table使用QueryTable数组中的下一个更新事件提供者对象引用（仅当刷新成功时），并调用其Refresh方法，这将AfterRefresh再次触发，直到遍历整个数组或其中之一未能更新。
回答by Donald Li
Just found this solution at Execute code after a data connection is refreshed
刚刚在刷新数据连接后的执行代码中找到了这个解决方案
The bottom line is: Excel refreshes data connection in the background and thus the rest of the code is executed without interruption.
底线是：Excel 在后台刷新数据连接，因此其余代码不会中断执行。
Solution: set BackgroundQueryproperty to False
解决方案：将BackgroundQuery属性设置为False
Example:
例子：
For Each cnct In ThisWorkbook.Connections
cnct.ODBCConnection.BackgroundQuery = False
Next cnct
Possible problem: don't know which connection it is...
可能的问题：不知道是哪个连接...
Remedy: case... when...
补救措施：情况...当...
Dim cnct as WorkbookConnection ' if option explicit
' ODBC and OLE DB
For Each cnct In ThisWorkbook.Connections
Select case cnct.type
case xlconnectiontypeodbc
cnct.ODBCConnection.BackgroundQuery = False
case xlconnectiontypeoledb
cnct.OledbConnection.BackgroundQuery = False
end select
Next cnct
As you can see, code above only deals with ODBCand OLE DB. Depending on what types of data connection you are using, you can expand the select case clause. Unless changed, once run, connection's BackgroundQuerywill remain off.
如您所见，上面的代码仅处理ODBC和OLE DB。根据您使用的数据连接类型，您可以展开 select case 子句。除非更改，否则一旦运行，连接BackgroundQuery将保持关闭状态。
