如何检查连接刷新是否成功 [英] How to check whether Connection Refresh was successful

问题描述

在Excel 2016 VBA中，我正在刷新几个查询，如下所示:

In Excel 2016 VBA, I'm refreshing several queries like this:

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.

顺便说一句，我不想​​执行RefreshAll，因为某些查询依赖于其他查询(将它们用作源).我按一定顺序刷新它们，以便在依赖查询之后刷新依赖查询.

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.

更新:

我看到Connection对象具有只读的RefreshDate属性，乍一看看起来可以用于执行此检查:

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:

MyWorkbook.Connections(MyConnectionName).OLEDBConnection.RefreshDate

但是，似乎没有被设置.尝试检查时出现错误.如果我为该RefreshDate属性设置了Variant变量，则该变量将显示为"Empty".源是一个SQL Server数据库.

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.

推荐答案

QueryTable对象公开两个事件:BeforeRefresh和AfterRefresh.

The QueryTable object exposes two events: BeforeRefresh and AfterRefresh.

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

You need to change your paradigm from procedural/imperative to event-driven.

假设您在ThisWorkbook中拥有此代码(将无法在标准过程代码模块中使用，因为WithEvents只能在一个类中):

Say you have this code in ThisWorkbook (won't work in a standard procedural code module, because WithEvents can only be in a class):

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对象的数组，按您希望刷新它们的顺序排序；对于要操作的QueryTable，currentIndex变量指向该数组中的索引.

The tables variable contains an array of QueryTable objects, ordered in the order you wish to refresh them; the currentIndex variable points to the index in that array, for the QueryTable you want to act upon.

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

So when Test runs, we initialize the tables array with the QueryTable objects we want to refresh, in the order we want to refresh them.

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

The implicit, event-driven loop begins when table.Refresh is called and the QueryTable fires its AfterRefresh event: then we report success, and update the event-provider table object reference with the next QueryTable in the array (only if the refresh was successful), and call its Refresh method, which will fire AfterRefresh again, until the entire array has been traversed or one of them failed to update.

这篇关于如何检查连接刷新是否成功的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！