PowerQueryとピボットテーブルの更新順序を制御する方法

はじめに

今回はExcel VBAでPowerQueryとピボットテーブルを連携させる際の「非同期処理」の問題に焦点を当ててみます。

直面した問題

PowerQueryで取得したデータをピボットテーブルで分析するVBAマクロを作成していました。コード自体はシンプルで、以下のような内容です。

Sub UpdatePowerQueryAndPivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    
    ' クエリの更新
    ThisWorkbook.RefreshAll
    
    ' ピボットテーブルの更新
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
End Sub

このコードの概要は以下のとおりです。

1. まずPowerQueryのデータを更新する
2. 次にピボットテーブルを更新して、最新のデータを反映させる

ところが実行してみると、PowerQueryの更新が完全に終わる前に、ピボットテーブルの更新処理が実行されてしまいました。

その結果、ピボットテーブルには古いデータが表示されたままでした。

なぜこの問題が発生するのか？

この問題の核心は「非同期処理」にあります。デフォルト設定では、PowerQueryの更新はバックグラウンドで実行されます。つまり、RefreshAllコマンドはデータの更新を「開始」するだけで、更新が「完了」するのを待たずに次の処理に進んでしまうのです。

これでは当然、次の行のピボットテーブル更新命令がPowerQueryの更新完了前に実行されてしまいます。

解決策

この問題を解決するには、PowerQueryの更新を「同期処理」に変更する必要があります。以下に2つの効果的な解決策をご紹介します。

解決策1：クエリのプロパティを変更する

1. クエリと接続 > 該当クエリを右クリック
2. プロパティを選択
3. 「バックグラウンドで更新する」のチェックを外す

この設定により、PowerQueryの更新が完了するまでVBAコードの次の行は実行されなくなります。

解決策2：VBAコードで明示的に指定する

もう一つの方法は、VBAコード内で直接バックグラウンド更新をオフにすることです。

ListObject.QueryTable.Refresh BackgroundQuery:=False

BackgroundQuery:=False パラメータを追加することで、クエリの更新が完了するまで次の処理を待機させることができます。

まとめ

Excel VBAでPowerQueryとピボットテーブルを連携させる際は、更新の順序と同期に注意が必要です。今回ご紹介した解決策を使えば、期待した順序でデータが更新され、ピボットテーブルに最新情報が反映されるようになります。