Excel VBA - функция QueryTable AfterRefresh, которая не вызывается после завершения обновления

Я разрабатываю приложение Excel (2010+) с помощью VBA и сталкиваюсь с проблемой, когда функция события AfterRefresh не вызывается после завершения запроса.

Мне не удалось найти много достойных ресурсов или документации о том, как запустить эту функцию события в модуле класса. Я решил использовать маршрут проектирования модуля класса вместо того, чтобы помещать обработчики событий в рабочий лист после получения ответа на более ранний вопрос о QueryTables (найденный здесь Excel VBA AfterRefresh).

Вот код моего модуля класса CQtEvents

Option Explicit

Private WithEvents mQryTble As Excel.QueryTable
Private msOldSql As String

' 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
Public Property Let OldSql(ByVal sOldSql As String): msOldSql = sOldSql:
End Property
Public Property Get OldSql() As String: OldSql = msOldSql:
End Property

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

' Resets the query sql to the original unmodified sql statement
' This method is invoked when the Refresh thread finishes executing
Private Sub mQryTble_AfterRefresh(ByVal Success As Boolean)
    ' Problem is here
    ' This function is never called :( Even if the query successfully runs
    Me.QryTble.CommandText = Me.OldSql

End Sub

Вот быстрый снимок кода, который создает экземпляр этого класса, находит соответствующий QueryTable, а затем вызывает Refresh

Option Explicit

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

'From MGLOBALS
cacheSheetName = "Cache"
Set cacheSheet = Worksheets(cacheSheetName)

Dim querySheet As Worksheet
Dim interface As Worksheet
Dim classQtEvents As CQtEvents

Set querySheet = Worksheets("QTable")
Set interface = Worksheets("Interface")
Set classQtEvents = New CQtEvents

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 '''
Dim sqlQueryString As String
sqlQueryString = qt.CommandText
Set classQtEvents.QryTble = qt
classQtEvents.OldSql = sqlQueryString ' Cache the original query string


QueryBuilder.BuildSQLQueryStringFromInterface interface, sqlQueryString

' Test message
MsgBox sqlQueryString
qt.CommandText = sqlQueryString

If Not qt Is Nothing Then
    qt.Refresh
Else
    ' ... Error handling code here... 
End If


''' CLEAN UP '''

' Free the dictionary
Set qtDict = Nothing

End Sub

Также приведен скриншот структуры модуля http://imgur.com/8fUcfLV

Моя первая мысль о том, что может быть проблемой, заключается в передаче значения QueryTable по значению. Я не самый опытный разработчик VBA, но я решил, что это создаст копию и вызовет событие в несвязанной таблице. Однако это было не так, и переход по ссылке также не устранил проблему.

Также подтверждается, что запрос успешно запущен, так как данные корректно отображаются и обновляются.

ИЗМЕНИТЬ Я добавил функцию события BeforeRefresh в класс класса CQtEvents и подтвердил, что эта функция вызывается после того, как Refresh называется

Private Sub mQryTble_BeforeRefresh(Cancel As Boolean)
    MsgBox "Start of BeforeRefresh"
End Sub

Как я могу изменить этот код, получить мой QueryTable из подпрограммы QTableModule RefreshDataQuery() Sub, чтобы активировать функцию AfterRefresh, когда запрос успешно запущен?

+5
источник поделиться
2 ответа

Как поймать AfterRefresh event запроса QueryTable?

Объяснение: в вашей ситуации до того, как событие было уволено, вы потеряли ссылку на свой QueryTable, установив его ничем при завершении очистки или завершении процедуры.

Общее решение: вы должны быть уверены, что ваш код все еще запущен и/или вам нужно сохранить ссылки на ваш QueryTable.

1-е решение. При вызове QT.Refresh method установите параметр false следующим образом:

qt.Refresh false 

который остановит дальнейшее выполнение кода, пока ваш qt не будет обновлен. Но я не считаю это решение лучшим.

2-е решение. Сделайте свой classQtEvents variable общедоступным и после завершения RefreshDataQuery sub проверьте статус с помощью другого кода.

  • в CQtEvents class module добавьте следующую общедоступную переменную:

    Public Refreshed As Boolean
    
  • в BeforeRefresh event добавить следующее:

    Refreshed  = False
    
  • в AfterRefresh event добавить эту строку кода:

    Refreshed = True
    
  • Сделайте объявление classQtEvents variable общедоступным. Поместите это перед Sub RefreshDataQuery()

    Public classQtEvents as CQtEvents
    

но удалите соответствующее объявление из своего суб.

Теперь, даже ваш подзаголовок будет завершен, вы сможете проверить статус обновления, проверив .Refreshed property. Вы можете сделать это в Immediate или в пределах другого Sub. Это должно работать для Immediate:

Debug.Print classQtEvents.Refreshed

3-е решение. (немного похож на 1-й). Выполните шаги с 1 по 3 из второго решения. После вызова QT.Refresh method вы можете добавить этот цикл, который остановит дальнейшее выполнение кода до тех пор, пока qt не будет обновлен:

'your code
If Not qt Is Nothing Then
    qt.Refresh
Else
    ' ... Error handling code here... 
End If
'checking
Do Until classQtEvents.Refreshed
    DoEvents
Loop

Заключительное замечание. Надеюсь, я не перепутал qt variable с classQtEvents variable. Я не пробовал и не тестировал какое-либо решение, используя ваши переменные, но писал все выше, ссылаясь на код, который я использую.

+3
источник

Ниже приведено описание gythub repo, демонстрирующего минимальный код, необходимый для получения этой работы здесь.

Как уже упоминалось, если ваш обработчик событий не находится в области видимости или потеряна ссылка на запрос QueryTable, вы не поймаете событие. Ключевыми факторами, позволяющими вам поймать событие, являются:

  • Объявите глобальную переменную типа вашего класса обработки событий вне любых подпрограмм/методов в верхней части файла (я выбрал файл ThisWorkbook).

  • Добавьте обработчик события Workbook_Open и создайте экземпляр этой переменной, чтобы он был доступен сразу и остался в области видимости (поскольку он глобальный).

  • В этот момент или в любой нисходящей точке, когда у вас есть интересующая вас QueryTable, передайте этот QueryTable глобальному экземпляру, чтобы связать его события.

(Мне потребовалось пару попыток выяснить это сам, когда кто-то указал мне в этом направлении как ответ на этот вопрос.)

+1
источник

Связанные вопросы


Похожие вопросы

Посмотрите другие вопросы по меткам или Задайте вопрос