【VBAリファレンス】VBA技術解説PowerQueryの強力な機能をVBAから利用する方法

VBAからPower Query（Get & Transform）を制御する技術的アプローチ

現代のExcel業務において、Power Queryはデータ収集・加工のデファクトスタンダードとなりました。GUIベースで複雑なETL（抽出・変換・読み込み）処理を構築できる点は強力ですが、実務の現場では「ボタン一つで更新したい」「動的にパラメータを変更してクエリを走らせたい」といった自動化のニーズが後を絶ちません。

多くのエンジニアが「Power QueryはGUI専用」と誤解していますが、実はVBAからその実行エンジンを制御することは十分に可能です。本記事では、VBAを活用してPower Queryの可能性を拡張するプロフェッショナルな手法を解説します。

Power QueryをVBAで制御する意義と仕組み

Power Queryのクエリは、Excelブック内に「クエリ定義」として保持されています。VBAからこれを操作する主な目的は、以下の3点に集約されます。

1. 動的なパラメータ変更：ユーザー入力や外部ファイルパスに応じて、クエリのソースやフィルタ条件を書き換える。
2. 実行の自動化と順序制御：複数のクエリを依存関係に基づいて順番に実行し、完了後にVBAで後続の加工を行う。
3. エラーハンドリング：クエリ更新が失敗した際の検知と、ログの記録。

Excelのオブジェクトモデルにおいて、Power Queryは「Workbook.Queries」コレクションと「WorkbookConnection」オブジェクトによって管理されています。これらを組み合わせることで、クエリの更新、定義の書き換えが可能となります。

クエリ更新をVBAから実行する標準的な手法

最も基本的なタスクは、特定のクエリを更新することです。VBAでは「WorkbookConnection」オブジェクトの「Refresh」メソッドを使用します。

Sub RefreshSpecificPowerQuery()
    Dim conn As WorkbookConnection
    ' クエリ名でコネクションを指定
    Set conn = ThisWorkbook.Connections("Query - MyDataQuery")
    
    ' バックグラウンド更新を無効にして同期実行する
    conn.OLEDBConnection.BackgroundQuery = False
    conn.Refresh
    
    MsgBox "更新が完了しました。", vbInformation
End Sub

ここで重要なのは「BackgroundQuery = False」の設定です。デフォルトではクエリはバックグラウンドで実行されます。VBAで更新終了を待たずに後続処理（例えば更新後のデータをVBAで加工する処理）が走ると、空のデータを参照してエラーになるため、同期実行は必須のテクニックです。

M式をVBAから動的に書き換える高度な手法

Power Queryの心臓部は「M言語」です。クエリ定義内のM式をVBAで直接書き換えることで、複雑な条件分岐や動的なファイルパス指定が可能になります。

Sub UpdateQueryFormula()
    Dim qry As WorkbookQuery
    Dim formula As String
    
    ' クエリ定義を取得
    Set qry = ThisWorkbook.Queries("MyDataQuery")
    
    ' 現在のM式を取得し、特定の文字列を置換する
    formula = qry.Formula
    formula = Replace(formula, "C:\OldPath\Data.xlsx", "C:\NewPath\Data.xlsx")
    
    ' 書き換えたM式をクエリに適用
    qry.Formula = formula
    
    ' 更新を実行
    ThisWorkbook.Connections("Query - MyDataQuery").Refresh
End Sub

この手法を使えば、例えば日付ごとにフォルダを切り替えるような処理や、ユーザーがフォームで入力した特定のIDをフィルタ条件としてM式に埋め込むことが可能です。ただし、M式の文字列操作には細心の注意が必要です。構文を誤るとクエリが壊れるため、必ず書き換え前のバックアップを保持する設計にしてください。

実務における設計のアドバイスとベストプラクティス

Power QueryとVBAを連携させる際、以下の設計指針を守ることがプロフェッショナルとしての品質を担保します。

1. M式に直接ロジックを詰め込みすぎない
M式は「データ変換」に専念させ、VBAは「パラメータの受け渡しと制御」に専念させるのが理想です。例えば、フォルダパスや抽出条件は「Excelのセル」に書き込み、Power Queryでそのセルを読み込むように設定します。VBAからはセルの値を書き換えるだけで済むため、M式の複雑な文字列置換を回避でき、保守性が劇的に向上します。

2. 更新完了の待機処理
Refreshメソッドを呼ぶだけでは、大規模なデータの場合に処理が追いつかないことがあります。以下のように、更新完了をループで監視する実装が推奨されます。

Sub RefreshAndWait()
    Dim conn As WorkbookConnection
    Set conn = ThisWorkbook.Connections("Query - MyDataQuery")
    
    conn.OLEDBConnection.BackgroundQuery = True ' 非同期で開始
    conn.Refresh
    
    ' 更新中であれば待機
    Do While conn.OLEDBConnection.Refreshing
        DoEvents
        Application.Wait (Now + TimeValue("0:00:01"))
    Loop
    
    MsgBox "完全な更新が終了しました。"
End Sub

3. エラーログの記録
Power Queryの更新失敗は、VBA側からはエラーとして捕捉しにくい場合があります。クエリの更新後に「データ行数」を確認する、あるいは最終更新日時が更新されているかをチェックするロジックをVBAに組み込み、異常時には即座にアラートを出す仕組みを構築してください。

Power QueryとVBAの共存がもたらす未来

Power Queryはデータ加工の「強力なエンジン」であり、VBAはそれを操る「司令塔」です。この二つを組み合わせることで、従来のVBAだけでは不可能だった数百万行レベルのデータ処理を、驚くほど軽量かつ高速に実行できるようになります。

特に、レガシーなVBAコードで書かれた複雑なループ処理をPower Queryに移行し、VBAは「データの取り出し」と「レポートの出力」に特化させるリファクタリングは、システムパフォーマンスを飛躍的に向上させます。

まとめ

VBAからPower Queryを制御する技術は、Excel業務自動化の次なるステージです。
– クエリのコネクションを特定し、同期実行（BackgroundQuery = False）を徹底すること。
– M式の書き換えは最小限にし、可能な限り「セル連携」を利用すること。
– 更新完了を監視し、エラーハンドリングを実装すること。

これらを習得することで、あなたのExcelツールは単なる自動化ツールから、堅牢で拡張性の高いデータプラットフォームへと進化します。ぜひ、次回のプロジェクトからこのアーキテクチャを導入し、その圧倒的な処理能力と柔軟性を体感してください。VBAの可能性は、Power Queryと結びつくことで、まだまだ広がり続けます。