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

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

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

Вот код моего модуля classа 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 

Вот быстрый снимок кода, который создает экземпляр этого classа, находит соответствующий 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, но я решил, что это создаст копию и вызовет событие в несвязанной таблице. Однако это было не так, и переход по ссылке также не устранил проблему.

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

EDIT Я добавил функцию события BeforeRefresh в class classа CQtEvents и подтвердил, что эта функция вызывается после вызова Refresh

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

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

Как поймать AfterRefresh event AfterRefresh в QueryTable?

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

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

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

 qt.Refresh false 

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

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

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

     Public Refreshed As Boolean 
  2. в вашем BeforeRefresh event добавьте следующее:

     Refreshed = False 
  3. в вашем AfterRefresh event добавьте эту строку кода:

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

     Public classQtEvents as CQtEvents 

но удалите соответствующую декларацию из своего суб.

Теперь, даже ваш .Refreshed property закончен, вы сможете проверить статус .Refreshed property проверив .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 . Я не пробовал и не тестировал какое-либо решение, используя ваши переменные, но писал все выше, ссылаясь на код, который я использую.

Здесь можно найти repository github, который демонстрирует минимальный код, необходимый для получения этой работы.

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

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

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

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

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

  • Скрытие всех выбранных строк данных
  • Объединение рабочих листов в один
  • Использование кода VBA, как экспортировать листы Excel в качестве изображения в Excel 2003?
  • Сохранение нескольких листов в .pdf
  • Индикатор выполнения в VBA Excel
  • Как повторить «диапазон копирования (A1: A5) в диапазон B с преобразованием (столбец в строку)?
  • VBA: использование WithEvents в UserForms
  • Это . in. Изменить, если это определено .Cells?
  • Скопировать код VBA с листа в одной книге на другую?
  • Сортировка словаря по ключу в VBA
  • Удаление файла в VBA
  • Давайте будем гением компьютера.