VBAでPowerQueryとピボットテーブルの更新を制御する

一般的にクエリやピボットテーブルを更新する場合 [全て更新] をクリックして全体を更新します。
でも、ピボットテーブルを複数のクエリから作成している場合 [全て更新] で更新が反映されないケースが多々発生します。

それは、クエリの更新順によって反映されない場合が生じてしまうことが原因です。クエリが別のクエリを参照している場合などで発生します。参照元の更新が完了する前に参照先が更新されてしまうケースです。

複数のクエリやピボットテーブルの更新をVBAで制御する方法を検討したいと思います。

くるみこ
くるみこ

Pivot Table と Power Pivoto を勉強してきてクエリやピボットテーブルの更新方法が面倒でわかりにくいと思いました。VBAを使って更新できるように検討していきましょう。

確かに更新する手順がわかりにくいので、VBA のコマンドボタンで [全て更新] ができたらいいなと思っていました。よろしくお願いしますm(__)m

【この記事でわかることは】
・Power Query と ピボットテーブルの更新をVBAで行う方法がわかります
・更新する順番を制御する方法がわかります

VBA に関する前回記事はこちらです。ブログカードをクリックすれば開きます(^^ゞ

・VBA で別ブックの保護されたワークシートを一括解除する方法についての記事です。

記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
Excel で外部データ接続を更新する

更新の動作を確認します

Power Query や ピボットテーブルを更新する際の動作を確認してみます。

「マクロの記録」で確認

いろいろなパターンの動作を「マクロの記録」を使って確認してみます。

[全て更新] を実行

[データ] > [すべて更新] をクリックした場合の記録です。


※以下、記録されたコードの余白部分は削除して表記します。

Sub Macro1()
'[データ] > [クエリと接続] > [すべて更新]
    ActiveWorkbook.RefreshAll
End Sub

ActiveWorkbook.RefreshAll はブック内のすべてのクエリやピボットテーブルを更新します。

ピボットテーブルを [更新]

ピボットテーブルのあるシートで [データ] > [クエリと接続] > [すべて更新] > [更新]

または、

ピボットテーブル上で右クリック > [更新]

・下が実行GIF画像です。参照元のクエリも同時に更新されるのがわかります。

・記録作成されたコードがこちらです。

Sub Macro2()
'ピボットテーブルのあるシートで [データ] > [クエリと接続] > [すべて更新] > [更新] 
    Sheets("Pivot").Select
    Selection.ListObject.TableObject.Refresh
    Selection.ListObject.TableObject.Refresh
    Selection.ListObject.TableObject.Refresh
    ActiveSheet.PivotTables("ピボットテーブル1").PivotCache.Refresh
End Sub

ActiveSheet.PivotTables(“ピボットテーブル名”).PivotCache.Refresh

・Selection.ListObject.TableObject.Refresh が記録されていますが、これはクエリをテーブルに表示している場合、テーブルをリフレッシュする動作が入っているようです。

・テーブルに表示せず「接続のみ」にした場合はこの部分は記録されません。

※以下、テーブルは表示しないようにして記録しました。

クエリを個別に [最新の情報に更新]

今度は、各クエリを個別に更新してみましょう。
[クエリと接続] の各クエリリスト上でアクティブになったクエリに表示される [最新の情報に更新] という小さいアイコンをクリックします。

記録されたコードがこちらです。

Sub Macro3()
'[クエリと接続] の各クエリリスト上で表示される [最新の情報に更新] をクリック 
    ActiveWorkbook.Connections("クエリ - 販売クエリ").Refresh
    ActiveWorkbook.Connections("クエリ - 店舗マスタ").Refresh
    ActiveWorkbook.Connections("クエリ - 単価マスタ").Refresh
End Sub

・ActiveWorkbook.Connections(“クエリ – 販売クエリ”).Refresh

・個別のクエリだけ更新する場合はこれを使うようにすればいいわけですね!

VBA でクエリとピボットテーブルの更新を制御します

それでは、VBAで使えるように調整していきます。設定にあたっては、外部データ接続の ODBC と OLEDB にも考慮して設定していきます。

右画像の WorkbookConnection オブジェクトのプロパティに二つが表示されています。

バックグラウンドで更新にチェックが入っている場合 、 結果の反映が待機状態になってしまうのを回避するように考慮します。

バックグラウンドの更新設定がオンだった場合、設定をオフに切り替えて次のアクションが実行される前にクエリが完全に実行されるように設定していきます。

全てのクエリ更新後にピボットテーブルを更新する

まずは [すべて更新] をVBAで設定してみましょう。
「クエリ更新」完了後に「ピボット更新」するようにプロシージャを分けて設定します。

'全てのクエリ更新
Sub AllQueryRefresh()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim wbc As WorkbookConnection
    Dim bolOLEDB As Boolean 'OLEDBのBackgroundQuery設定保存用
    Dim bolODBC As Boolean  'ODBCのBackgroundQuery設定保存用
    
    Set wb = ActiveWorkbook
    'OLEDBとODBCでない場合エラーになるので回避のためのおなじない
    On Error Resume Next
    For Each wbc In wb.Connections  'コネクションの全てをループ
        'BackgroundQueryの状態を変数に保存
        bolOLEDB = wbc.OLEDBConnection.BackgroundQuery
        bolODBC = wbc.ODBCConnection.BackgroundQuery
        wbc.OLEDBConnection.BackgroundQuery = False
        wbc.ODBCConnection.BackgroundQuery = False
        Debug.Print wbc.Name        'クエリ名確認用に
        '更新実行
        wbc.Refresh
        'BackgroundQueryの状態を元に戻す
        wbc.OLEDBConnection.BackgroundQuery = bolOLEDB
        wbc.ODBCConnection.BackgroundQuery = bolODBC
    Next
    On Error GoTo 0
    'ピボットテーブル更新へ
    Call piv_CacheRefresh
End Sub

外部データ接続の ODBC または OLEDB ではない場合はエラーが発生するので、On Error Resume Nextを入れて、マクロが停止せずスルーするようにしています

・BackgroundQuery の設定を一旦変数に保存しておき、False に設定してから更新します。

・更新後に BackgroundQuery の設定を元に戻します。

・For Each ループで、ブック内の全クエリの更新完了後に「Call piv_CacheRefresh」で次のピボットテーブルの更新に処理を移します。

'ブック内全てのピボットテーブル更新
Sub piv_CacheRefresh()
    Dim sh As Worksheet
    Dim pivTable As PivotTable
    
    'ブックの全ワークシートをループ
    For Each sh In Worksheets
        'ワークシート内のピボットテーブルを更新
        For Each pivTable In sh.PivotTables
            Debug.Print pivTable.Name   'Pivotテーブル名
            pivTable.PivotCache.Refresh 'Pivot更新
        Next pivTable
    Next sh
End Sub

・こちらも For Each ループで、ブック内に存在するの全ピボットテーブルを更新しています。

更新順序を設定できるように改造します

ピボットテーブルの元データに複数の PowerQuery データを使っている場合は、先に元のクエリを更新しておきたいですよね。クエリが別のクエリを参照している場合も同様です。

更新順序を設定できるようにできれば解決できそうです。でも、設定するためにはどうすればよいでしょうか。

マクロの記録で、クエリを個別に更新した際のコードにはクエリ名が表示されていました。

であれば、クエリ名を更新前に取得しておけば順番を設定できそうですね。

クエリ接続名をセルに書き出します

WorkbookConnection の名前をワークシートのセルに書き出すコードです。

'ブック内の全クエリ接続名取得
Sub GetConnectionName()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim wbc As WorkbookConnection
    Dim i As Long
    
    Set wb = ActiveWorkbook
    Set sh = wb.ActiveSheet
    i = 2
    'シートのセルデータを一旦クリアする
    sh.Range(Cells(i, 7), Cells(50, 7)).ClearContents
    'ブック内のクエリ接続名を全て書き込む
    For Each wbc In wb.Connections
        sh.Cells(i, 7) = wbc.Name   'クエリ接続名を書き込む
        i = i + 1
    Next
End Sub

・実行しているGIF画像です。

取得クエリ接続名に実行順を設定します

シートのセルに書き出した「クエリ接続名」の更新順を設定しましょう。

先ほど取得した G列の「クエリ接続名」の前 F列に更新順に数字を書き込みます。

指定順にクエリ接続を更新するコード

クエリ接続名を取得するコードを次のように改造しました。

'指定順にクエリ更新
Sub QueryRefresh()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim wbc As WorkbookConnection
    Dim bolOLEDB As Boolean
    Dim bolODBC As Boolean
    Dim i As Long, n As Long, tg As Long
    Dim cname As String
    
    Set wb = ActiveWorkbook
    Set sh = wb.ActiveSheet
    n = WorksheetFunction.Max(sh.Columns(6))
    'OLEDBとODBCでない場合エラーになるので回避のため
    On Error Resume Next
    For i = 1 To n
        tg = WorksheetFunction.Match(i, sh.Columns(6), 0)
        cname = sh.Cells(tg, 7).Value
        Set wbc = wb.Connections(cname)
        With wbc
            'BackgroundQueryの状態保存
            bolOLEDB = .OLEDBConnection.BackgroundQuery
            bolODBC = .ODBCConnection.BackgroundQuery
            'BackgroundQueryをFalseに設定
            .OLEDBConnection.BackgroundQuery = False
            .ODBCConnection.BackgroundQuery = False
            '更新実行
            .Refresh
            'BackgroundQueryの状態を戻す
            .OLEDBConnection.BackgroundQuery = bolOLEDB
            .ODBCConnection.BackgroundQuery = bolODBC
        End With
    Next
    On Error GoTo 0
    
    Call piv_CacheRefresh   'ピボット更新へ
 
End Sub

・13行目で、F列に入力された最大数値(最大更新クエリ数)を取得しています。

・16行目、1~取得最大値でループ処理開始です。

・17行目で、F列に入力された数値と該当順が一致する行番号を取得しています。

・18行目で、取得した行番号から設定更新順のG列「クエリ接続名」を取得しています。

・19行目で、取得した「クエリ接続名」を WorkbookConnection 変数にセットしています。

・28行目で、当該「クエリ接続名」のクエリを個別に更新しています。

・36行目で、全てのクエリ更新完了後にピボットテーブル更新処理へジャンプしています。

・下のGIF画像が、このコードを実行した様子です。

・少しわかりずらいかもしれませんが、設定した順番どおりに更新が実行されています。

更新実行用のコマンドボタンを設置します

ワークシートにコマンドボタン(ここではActiveX]コントロール使用)を設置します。

左側のコマンドボタン「更新実行」のクリックイベントに次のコードを設定します。

Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim n As Long
    
    Set wb = ActiveWorkbook
    Set sh = wb.ActiveSheet
    n = WorksheetFunction.Max(sh.Columns(6))
    'F列に数値設定があるかどうかで処理を分岐
    If n <> 0 Then
        Call QueryRefresh       '指定順で更新処理へ
    Else
        Call AllQueryRefresh    'すべて更新する処理へ
    End If
End Sub

・更新順を設定しているかどうかで処理を分岐する設定にします。

・F列を調べて更新順の設定があった場合は、指定順で更新する処理へジャンプします。

・設定がなかった場合は、すべて更新する処理へジャンプします。

・右側のコマンドボタン「接続名取得」には次の呼び出しコードを設定します。

Private Sub CommandButton2_Click()
    Call GetConnectionName
End Sub

・以上で設定は終了です。

スポンサーリンク

まとめ(おわりに)

以上、Power Query と ピボットテーブルの更新についてVBAで実行する方法を紹介しました。

サンプルファイルをダウンロードできるように登録していますのでご利用ください。

外部データ接続の ODBC や OLEDB については、動作テストを行っていないので、万一動作しない場合はその部分をコメントアウトしてください。

まとめと感想など

くるみこ
くるみこ

これで Power Query と ピボットテーブルの更新を順序を指定して VBAから実行できるようになりました。動作検証テストが不十分なので不具合があるかもしれませんが、考え方等はおおむね間違っていないのではないかと思っています。いかがでしたか?

これで、何度も更新をかける必要がなくなって時短になりそうです。

実際の作業で試していきたいと思います。

【今回わかったことは】
・Power Query と ピボットテーブルの更新をVBAで行う方法
・更新する順番を制御する方法

★★★ ランキング参加中! クリックしてね(^^)/ ★★★

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/

過去記事のサンプルファイルをダウンロードできます

今回記事のサンプルファイルを登録しています
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら(このリンク先)からご利用ください