如何检查连接刷新是否成功

Question

在Excel 2016 VBA中，我刷新了几个这样的查询：

MyWorkbook.Connections(MyConnectionName).Refresh

代码完成后，没有遇到任何错误，我看到大多数查询的沙漏图标仍在旋转几秒钟。

是否可以在所有刷新完成后检查是否成功？我担心我的代码不会知道错误是否在代码完成后但在查询完成刷新之前发生。

顺便说一句，我不想做 RefreshAll，因为某些查询依赖于其他查询(将它们用作源)。我按特定顺序刷新它们，以便在它们所依赖的查询之后刷新相关查询。

更新：

我看到连接对象具有只读 RefreshDate 属性，乍一看它可以用来执行此检查：

MyWorkbook.Connections(MyConnectionName).OLEDBConnection.RefreshDate

但是，它似乎还没有被设置下来。我在尝试检查时遇到错误。如果我将变量设置为该 RefreshDate 属性，则该变量将显示为"空"。源是 SQL 服务器数据库。

Answer 1

QueryTable对象公开两个事件：BeforeRefresh和AfterRefresh。

您需要将范式从程序/命令式更改为事件驱动型。

假设您在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

tables变量包含一个QueryTable对象的数组，按您希望刷新它们的顺序排序;currentIndex变量指向该数组中的索引，用于您要对其执行操作QueryTable。

因此，当Test运行时，我们按照要刷新的顺序，使用要刷新的QueryTable对象初始化tables数组。

隐式的事件驱动循环在调用table.Refresh时开始，QueryTable触发其AfterRefresh事件：然后我们报告成功，并使用数组中的下一个QueryTable更新事件提供程序table对象引用(仅当刷新成功时)，并调用其Refresh方法，该方法将再次触发AfterRefresh， 直到遍历整个阵列或其中一个阵列更新失败。

Answer 2

刚刚在刷新数据连接后执行代码中找到了此解决方案

底线是：Excel 在后台刷新数据连接，因此其余代码不会中断地执行。

解决方案：将BackgroundQuery属性设置为False

例：

For Each cnct In ThisWorkbook.Connections
cnct.ODBCConnection.BackgroundQuery = False
Next cnct

可能的问题：不知道它是哪个连接...

补救措施：案例...什么时候。。。

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

如您所见，上面的代码只处理ODBC和OLE DB。根据所使用的数据连接类型，可以展开 select case 子句。除非更改，否则一旦运行，连接的BackgroundQuery将保持关闭状态。

Answer 3

虽然不是一个精确的解决方案，但我确定了一个古怪的解决方法来显示 Power Query 通常会显示的错误消息，即使在从 VBA 触发刷新时也是如此。需要注意的是，它将刷新所有查询，但我提出了一种稍后处理的方法。

我们都知道，当您从VBA调用Workbook.RefreshAll方法时，出于某种原因，将其与Application.CalculateUntilAsyncQueriesDone一起使用时，所有Power Query错误消息都将被抑制。

但是，当您单击Excel功能区的"数据"选项卡中的"全部刷新"按钮时，将显示错误消息。幸运的是，您可以使用CommandBars.ExecuteMso方法从 VBA 触发功能区命令调用。所以我尝试调用按钮，它起作用了 - 显示错误消息。

您需要"全部刷新"按钮的idMso属性来触发命令。这是"RefreshAll"- 没有惊喜。

下面是一个帖子的链接，可帮助您获取常用命令的idMso属性。

调用这样的东西对我来说是诀窍：

Application.CommandBars("Queries and Connections").Visible = True
ThisWorkbook.Activate
Application.CommandBars.ExecuteMso "RefreshAll"
Application.CalculateUntilAsyncQueriesDone

请注意，我打开"查询和连接"窗格只是为了让用户在发生状态时知道它。这是可选的。而且我没有设置Application.ScreenUpdating = False，因为这也会冻结窗格。

我注意到在某些Excel版本中，您可能必须将所有查询连接的BackgroundQuery属性设置为True，Application.CalculateUntilAsyncQueriesDone方法才能正常工作。

现在，Power Query 本机显示的错误消息在发生错误时显示。

我还想用最后一个技巧来解决这种方法的一个缺点。如果不想刷新所有查询，可以编写一些代码来更改不想执行的连接WorkbookConnection.RefreshWithRefreshAll属性，以便在单击"全部刷新"按钮时阻止其执行。这是指向该属性文档的链接。

此方法的优点是，在刷新所有查询时，Power Query 在使用共享缓存时处理查询依赖项 - 因此它比使用代码触发单个查询刷新更好/更快。