クエリ更新時のイベントプロシージャ（VBA × PowerQuery）

2022年2月20日 18:31

Excelのパワークエリの更新をきっかけに実行されるイベントプロシージャがありVBAで扱うことができます。このイベントプロシージャを使うには、ブックやシートのイベントと違い、クラスモジュールにWithEvents キーワードを用いた事前設定をする必要があります。

クエリ更新前…BeforeRefresh
クエリ更新後…AfterRefresh

ほぼ以下の公式ドキュメントどおりですが、ごく一部そのまま動かない部分があったので備忘録として設定方法を記しておきます。

1. クラスモジュールの設定

新規クラスモジュールを挿入し、モジュール名を「ClsModQT」とする。
（好きな名前でよい）

クラスモジュールClsModQTに以下のコードを入力。

Option Explicit

Public WithEvents qtQueryTable As QueryTable

Sub InitQueryEvent(QT As Object)
   Set qtQueryTable = QT
End Sub

Private Sub qtQueryTable_BeforeRefresh(Cancel As Boolean)
   'クエリ更新前処理
End Sub

Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)
   'クエリ更新後処理
   If Success Then
       '更新成功時の処理
   Else
       '更新失敗またはキャンセル時の処理
   End If
End Sub

2. 標準モジュールの設定

クラスモジュールをつくっても、まだクエリ更新イベントを使えません。予めClsModQTの初期化と、その中のQueryTableオブジェクトを参照します。

そのために以下のコードを適当な標準モジュールに入力します。

Option Explicit

Public clsQueryTable As New ClsModQT

Public Sub RunInitQTEvent()
   clsQueryTable.InitQueryEvent _
   QT:=ActiveSheet.ListObjects(1).QueryTable '適宜シート指定を
'    QT:=ActiveSheet.QueryTables(1) '←Docsはこうだがダメだった
End Sub

3. クラスモジュールの初期化（設定完了）

標準モジュールに定義したRunInitQTEventプロシージャを実行すると初期化され、その後にアクティブシートのクエリを更新（1限定）するたびにイベントプロシージャが動くようになります。

その他の設定

このままですと、わざわざ標準モジュールのRunInitQTEventプロシージャを実行しないとイベントが動いてくれないので不便です。

ブックのオープン時やシートのアクティベート時のイベントと組み合わせて自動発生するようにするとより使い勝手がよくなると思います。

以下は1つのアイディアです。

ブックオープン時にイベント発生可能にする

事前に2つの設定を行います。

VBAの設定
ThisWorkbookのWorkbook_OpenイベントプロシージャでRunInitQTEventを実行します。

クエリの設定
ブックオープン時にクエリが更新されるように設定をしておきます。

これでブックがオープンするとクエリの更新とクラスモジュールに設定したVBAの更新イベントが同時に発生します。また、その後に手動でクエリ更新した際もVBAの更新イベントが発生します。