VBA for Excel AfterRefresh事件 [英] VBA For Excel AfterRefresh Event

问题描述

我正在使用以下QueryTable查询。在 .Refresh 执行VBA过程结束后。查询工作，但我需要执行代码完成后。

I am using the following QueryTable Inquiry. After the .Refresh executes the VBA procedure ends. The inquiry works but I need to execute code after it completes.

.AfterRefresh 事件似乎是我需要，但我不能让它执行。

The .AfterRefresh event seems to be what I need, but I can't get it to execute.

With ActiveSheet.QueryTables.Add(Connection:="URL;" & sUrl, Destination:=ActiveSheet.Range("a1"))

             .RefreshStyle = xlOverwriteCells
             .SaveData = True
             .Refresh
             .AfterRefresh (Success)
End With

这是不执行的AfterRefresh子。

This is the AfterRefresh sub that does not execute.

Sub QueryTable_AfterRefresh(Success As Boolean)

        If Success Then
                 Debug.Print "Success"
        Else
                 Debug.Print "Failed"
        End If
End Sub

需要什么来触发子程序查询是否完成？在 .Refresh 之后，在End With之后，我尝试调用子例程，但没有工作。

What is needed to trigger the subroutine after the query is done? I tried a call to the subroutine after the .Refresh and after the End With but neither worked.

谢谢。

推荐答案

很难找到一个很好的答案。我知道这个线程是老的，但是有一个很好的替代方案。

I ran across this same issue recently and it was very difficult to find a good answer. I realize this thread is old, but there is a decent alternative to the other solution posted.

您可以使用的一种模式是将QueryTable回调事件保留在单独的类模块，而不是嵌入到工作表中。这允许更多的模块化，可重复使用的代码。当您的Excel工作簿有多个QueryTable时，它变得特别有用。

One pattern you can utilize is keeping the QueryTable callback events in a separate Class Module instead of embedded within a worksheet. This allows for more modular, reusable code. It becomes especially useful when your Excel Workbook has multiple QueryTables.

这是类模块中类似于 CQtEvents / p>

Here is what the class module might look like in a class module called CQtEvents

Option Explicit

Private WithEvents mQryTble As Excel.QueryTable
' Add variables you may want to cache here such at the query or connection settings

' Properties
Public Property Set QryTble(ByVal QryTable As QueryTable): Set mQryTble = QryTable:
End Property
Public Property Get QryTble() As QueryTable: Set QryTble = mQryTble:
End Property
' Add other potential properties here

Private Sub Class_Initialize()
    ' Constructor
    MsgBox "CQtEvents init"
End Sub

Private Sub mQryTble_BeforeRefresh(ByVal Cancel as Boolean)
    'Insert logic you want to run before a refresh
End Sub   

Private Sub mQryTble_AfterRefresh(ByVal Success As Boolean)
    'Insert logic you want to run after a refresh

End Sub

键上面要注意的是 WithEvents 关键字以及BeforeRefresh和AfterRefresh的声明/定义。

The key thing to note above is the WithEvents keyword and the declarations/definitions for BeforeRefresh and AfterRefresh.

下面是代码可能使用上面定义的类模块的代码

Below is what the code might look like to leverage the Class Module defined above

Option Explicit

Sub RefreshDataQuery()
'Dependencies: Microsoft Scripting Runtime (Tools->References) for Dictionary (HashTable) object

Dim querySheet As Worksheet
Dim classQtEvents As CQtEvents

Set querySheet = Worksheets("QTable")
Set interface = Worksheets("Interface")
Set classQtEvents = New CQtEvents ' Instantiate the Class

Dim qt As QueryTable
Dim qtDict As New Scripting.Dictionary

Set qtDict = UtilFunctions.CollectAllQueryTablesToDict
Set qt = qtDict.Item("Query from fred2")

''' Building SQL Query String '''
qt.CommandText = "Select * From someTable" 

If Not qt Is Nothing Then
    qt.Refresh False ' See link at bottom of post for alternatives to this
Else
    ' ... Error handling code here... 
End If


''' CLEAN UP '''

' Free the dictionary
Set qtDict = Nothing

End Sub

这种方法的一个注意事项是，如果这是异步运行并且离开，AfterRefresh将不会被调用按原样这样做的原因是当模块完成执行时，查询表的引用将消失，这可能在查询完成执行之前完成。要解决这个问题，您可以通过设置

The one caveat with this approach is that the AfterRefresh will not be called if this is run asynchronously and left as is. The reason for this is the reference to the query table will disappear when the module finishes executing, which is likely to finish before the query finishes executing. To get around this, you can run it synchronously by setting

 qt.Refresh False

但是，这不是最好的方法，但是如果您不介意在子模块中的任何其他代码运行之前等待查询，则可以工作。看到这篇文章对于这个 Excel VBA - QueryTable在KazJaw之后，Refresh Refresh完成后没有调用AfterRefresh函数。

However, this is not the best approach but will work if you don't mind waiting on the query before any other code in the Sub Module runs. See this post for a really good answer on alternatives to this Excel VBA - QueryTable AfterRefresh function not being called after Refresh completes by KazJaw.

希望这有助于这是一个很好的替代方法，写入工作表中嵌入的这些事件处理程序

Hope this helps as this is a good alternative to writing these event handlers embedded in a worksheet

这篇关于VBA for Excel AfterRefresh事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！