はじめに
ETL処理だけPowerQuery、他の部分はVBA。
両方触れるようになると、そのような発想になると思います。
PowerQuery × VBAの連携時に、スムーズに運ばない点がありますので
回避方法を備忘として残します。
VBAでETL処理は厳しいと思うに至った出来事
いきなり主旨から外れ、すみません。）
以前、他部署から業務（印刷費用の部署配賦）とフルVBAの集計ツールを移管されたことがありました。
いわゆる強い人(退職済み)が組んだソースコードで、1プロシージャ1責任のもと
保守性も意識されていましたが、集計要件の追加に苦労した痕跡があり、
処理平積みのVBAからはリレーションや変数の責任範囲を私も把握できず、
動くオーパーツと化したことがありました。
処理の可視化、ブロック化などETLツールが備える機能は必須に思いました。
事務畑の方が保守るので、GUIにより心理的な参入障壁を下げる側面も大きい。
では本題
以前、以下コードに近いデータ成形マクロを組みました。
Sub ExportFromTable()
'全てのデータを更新 ⇒ PowerQueryがETL処理
'（GUIだと：「データ」タブ → 「すべて更新」を押す操作に相当）
ThisWorkbook.RefreshAll
'テーブルを取得
Dim lo As ListObject
Set lo = Worksheets("Sheet1").ListObjects("Table1")
'新規ブックを作成
Dim wbNew As Workbook
Set wbNew = Workbooks.Add
'新規ブックの1枚目シートを取得
Dim wsNew As Worksheet
Set wsNew = wbNew.Worksheets(1)
'テーブルの値をコピー
wsNew.Range("A1").Resize( _
lo.Range.Rows.Count, lo.Range.Columns.Count _
).Value = lo.Range.Value
End Sub
デバッグしてみると、すぐに違和感がありました。
「RefreshAllしたのに、古いデータが出ている」
バグではありません。
検証して分かったことはシンプルでした。
画面右下は「更新中…」。
でも VBAは後続処理に進んでいる。
VBAはThisWorkbook.RefreshAllの完了を待っていないようです。
つまり、
まだ更新中のテーブルを、VBAは更新済みだと思って読み取っていました。
まあ、これはVBAとPowerQueryが別プロセスなので納得できる仕様です。
それだけで終われば良いのですが、そうは問屋が卸さず
他にもう1つ、厄介な仕様が見つかりましたので、厄介の方から
後述のステップ①で解説いたします。
忙しい方向け
まずは、これだけ貼れば動くコードとなります。
Sub powerQuery更新してから後続処理()
'① 接続のバックグラウンド更新を止める（同期実行に寄せにいく）
' ※GUI操作で代替する場合：
' データ > クエリと接続 > クエリプロパティ >
' 「バックグラウンドで更新する」のチェックを外す
Dim conn As WorkbookConnection
For Each conn In ThisWorkbook.Connections
On Error Resume Next ' OLEDB/ODBC以外の接続対策
conn.OLEDBConnection.BackgroundQuery = False
conn.ODBCConnection.BackgroundQuery = False
On Error GoTo 0
Next conn
'② すべて更新
ThisWorkbook.RefreshAll
'③ 非同期クエリの完了を待つ
Application.CalculateUntilAsyncQueriesDone
'以降、後続処理
End Sub
各コードについて解説
① 接続のバックグラウンド更新を止める
重要なのは、「データ」タブの「すべて更新」と、各接続の「バックグラウンドで更新する」設定が干渉していることです。（ネイティブ機能。。
作用の流れはこうです。
-
RefreshAllが各接続に「更新開始」を指示します。 - 各接続は、なんと自分の
BackgroundQuery設定に従って実行します。つまり、手動で「すべて更新」を押しても、その内部では「各接続のBackgroundQueryプロパティどおり」に更新が走ります。 -
BackgroundQuery = Trueの接続は、更新処理をバックグラウンドスレッドで実行するため、更新が完了する前に制御を呼び出し元（VBA）へ返します。
その結果、
- 更新処理はまだ続いている
- しかしVBAは次の行を実行している
という状態が発生します。
問題の本質は、更新の開始命令と実行方式の設定が別レイヤーで動いていることです。
更新完了を前提にするなら、BackgroundQuery は False に統一します。
それでも十分ではありません。
確実に順番どおり動かすには、ステップ③の待機処理まで必要です。
Dim conn As WorkbookConnection
For Each conn In ThisWorkbook.Connections
On Error Resume Next ' OLEDBでない接続対策
conn.OLEDBConnection.BackgroundQuery = False
conn.ODBCConnection.BackgroundQuery = False
On Error GoTo 0
Next conn
※接続の種類によって OLEDBConnection または ODBCConnection になります。
Excelの画面操作で代替する場合： （この設定をすべてOFFにできるなら、上記VBAコードでの設定は不要です）
データ > クエリと接続 > クエリプロパティ >
「バックグラウンドで更新する」のチェックを外す
② すべて更新
ThisWorkbook.RefreshAll
意味： このブック内のすべての外部データ接続（Power Query・ODBC・OLEDBなど）を一括で更新します。
※ただし、このメソッドは「更新完了」までは待ちません。更新開始の指示を出すだけです。
③ 非同期クエリの完了を待つ
Application.CalculateUntilAsyncQueriesDone
手前味噌ですが、全てのRefresh実行を待機するVBAコードは実は存在した、ということです。
ただ、ステップ①（設定変更）を挟まないと、②の更新が物理的に「裏」へ逃げてしまい完遂せず、結果としてこの③も本来の役割を全うできません。
この3つの連鎖があって初めて、同期が成立します。
なぜこの3ステップが必要か
- ①で「非同期実行」を止める
- ②で更新を実行する
- ③で完全終了を待つ
この順番が重要です。
まず RefreshAll だけでは足りません。
次に BackgroundQuery = False だけでも足りません。
両方入れて初めて、
「更新 → 完了 → 後続処理」
が保証されます。
まとめ
Power QueryとVBAを確実に連動させるには、Refresh関連メソッドの挙動を正しく理解し、設定を揃えることが不可欠です。
-
RefreshAll は完了を待たない
RefreshAllメソッドは、ブック内の接続に更新を指示しますが、更新完了までVBAの処理を停止する仕様ではありません。 -
BackgroundQuery = True の接続はバックグラウンド実行になる
各接続で「バックグラウンドで更新する」が有効な場合、その接続はバックグラウンド（非同期）で実行されます。VBAはその完了を自動では待ちません。
この2つの挙動が重なると、更新処理が完了する前にVBAの後続処理が実行される可能性があります。その結果、更新前のデータを参照してしまうことがあります。
対策は明確です。
① BackgroundQuery を False に揃え、② RefreshAll を実行し、③ 完了を待機する。
重要なのは、更新処理が確実に完了してから次の処理へ進ませることです。
この記事が、同様の挙動で悩んだ方の整理材料になれば幸いです。