Excel VBA ピボットテーブルを操作する【更新編】

前回「マクロの記録」のコードをそのまま使ってスリム化しました。でも、まだまだ汎用化して使えるようにするには足りないことがたくさんあります。
今回は、ピボットテーブルを更新する方法について解説します。

くるみこ
くるみこ

ピボットテーブルを作成できるようになりましたが、まだまだ知っておくべきことはたくさんあります。今回は、更新する方法を勉強しましょう(^^)

わかりました(^^) よろしくお願いします!

【この記事でわかること
・ピボットキャッシュをオブジェクト型変数にセットして再利用する方法
・ピボットテーブルを更新する複数の方法

・PivotCaches コレクションでPivotCache の数だけ更新する方法

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

くるみこ
くるみこ

前回記事は、ピボットテーブル作成を「マクロの記録」のコードをスリム化して汎用で使えるようにする方法の解説記事でした。覗いてみてね(^^)/

ピボットキャッシュをオブジェクト型変数に入れる

前回の最終コードを確認します。
変数は使っていません。Withブロックを使用しているだけですね。

Sub Macro1()
    ActiveWorkbook.PivotCaches.Create(xlDatabase, "テーブル1") _
                              .CreatePivotTable Sheets.Add.Range("A3")
    With ActiveSheet.PivotTables(1)
        .PivotFields("時間軸(調査年)").Orientation = xlRowField
        .PivotFields("総数,男及び女_時系列").Orientation = xlRowField
        .PivotFields("総数,男及び女_時系列").PivotItems("総数").Visible = False
        .PivotFields("年齢(5歳階級)再掲有り_時系列").Orientation = xlColumnField
        .PivotFields("value").Orientation = xlDataField
    End With
End Sub

ピボットキャッシュは使い回しができます。同じ元データから複数のピボットテーブルを作成することができます。

その場合は、上記コードのようにピボットテーブルの作成までを一文で書かずに、ピボットキャッシュを変数に格納できるように、コードを二つに分けます。

ピボットキャッシュをオブジェクト変数に代入する

・変更したコードがこちらです。テスト用にピボットテーブルをループで3回作成させてみました
・見やすくするためにテーブル見出し文字列を短くしています。

'PivotCache変数に代入して再利用のテストをしてみる
Sub PivotTbleSample()
    Dim pvc As PivotCache
    Dim pvt As PivotTable
    Dim i As Long
    'PivotCachesとPivotTableを別けてオブジェクト変数に代入
    Set pvc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "テーブル1")
    'テストのため3回ループさせる
    For i = 1 To 3
    pvc.CreatePivotTable Sheets.Add.Range("A3")
    With ActiveSheet.PivotTables(1)
        .PivotFields("調査年").Orientation = xlRowField
        .PivotFields("男女別").Orientation = xlRowField
        .PivotFields("男女別").PivotItems("総数").Visible = False
        .PivotFields("年齢(5歳階級)").Orientation = xlColumnField '6
        .PivotFields("value").Orientation = xlDataField '.Function = xlSum
    End With
    Next
End Sub

7行目で、ピボットキャッシュをオブジェクト変数に代入しています
・9行目にテストのため3回ループして3つのピボットテーブルを作成するようにしています。
・実行してみてください! ピボットキャッシュが再利用できることを確認出来ます!

ピボットテーブルを更新する

ピボットテーブルは、元データの情報が変わっても自動で変更を適用してくれません。
ですから変更を反映させるため、更新処理を実行する必要があります。

「マクロの記録」のコード

Sub Macro1()
    Range("A3").Select
    ActiveSheet.PivotTables("ピボットテーブル1").PivotCache.Refresh
End Sub

PivotCacheオブジェクトのRefreshメソッドで更新しているのがわかりました!

PivotCache.Refresh で更新する

どうせならアクティブシート上のピボットテーブルをすべて更新するように書いてみます。

'ピボットテーブルをすべて更新する
Sub PivotCacheRefresh()
  Dim pvt As PivotTable
  For Each pvt In ActiveSheet.PivotTables
    pvt.PivotCache.Refresh
  Next
End Sub

・For Each~Next ループでアクティブシート内のピボットテーブル数で更新を繰り返します。
・ほとんどのサイトでこのコードが提示されています。
でも、ちょっと待ってください!
PivotCacheを更新すれば、同じPivotCacheで作ったピボットテーブルは全て更新されます。
・それなら、PivotCache の数だけ更新した方が効率的ですよね!

PivotCache の数でループ処理する方法

ループをピボットキャッシュの数で回すように変更してみます

'ワークブック内のすべてのピボットキャッシュを更新する
Sub RefreshAllPivotCache()
    Dim pvc As PivotCache
    For Each pvc In ActiveWorkbook.PivotCaches
        pvc.Refresh
    Next
End Sub

・サンプルはワークブック内のピボットキャッシュ数でループ更新しています。
pivotcachesコレクションは、Workbookオブジェクトのpivotcachesメソッドを使用するため、ワークブック全体で考える必要があります。
これなら一つのピボットキャッシュで10個ピボットテーブルを作っていても1回で済みます
この方が無駄が無くて速くなります! これが正解だよ!

PivotTable.RefreshTableで更新する

もう一つ更新方法があります。次のコードをご覧ください。

'すべてのピボットテーブルを更新する
Sub RefreshAllPivotTables()
    Dim pt As PivotTable
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            If pt.RefreshTable Then
                MsgBox pt.Name & "を更新しました!"
            Else: MsgBox pt.Name & "を更新できませんでした!"
            End If
        Next pt
    Next ws
End Sub

PivotTableオブジェクトには RefreshTableメソッドというものが用意されています。
RefreshTableメソッドは、更新に成功(True)したか失敗(False)したかを返してくれます。

RefreshTableを実際にステップ実行で動作確認してみると、同じピボットキャッシュのテーブルが一緒に更新されています!
ということは、
RefreshTableでピボットキャッシュも動作しているみたいです。
ということなら、やっぱりピボットキャッシュの数だけで更新方が良いのではないでしょうか!

スポンサーリンク

まとめ(おわりに)

・いかがでしたでしょうか?
「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

ピボットキャッシュの動作確認ができましたね。ピボットテーブルの更新方法を複数勉強しました。実際に動作確認するとどうすれば効率的なのかが良くわかりますね(^^)

はい! すごくおもしろかったです(^^) ピボットテーブルの更新方法がどのようになっているのかが良くわかりました(^^; よく復習してみます!

くるみこ
くるみこ

今回は元データがテーブルだったので、データ追加を考えなくてよかったけど、セル範囲だった場合は元データの範囲を変更する必要があります! では次回はその部分をもう少し詳しく勉強していきましょう(^^)/

【今回分かったことは】
・ピボットテーブルをオブジェクト型変数にセットする方法
PivotCache でピボットテーブルを更新する方法
PivotCachesコレクションでPivotCacheの数だけ更新する方法
RefreshTableでピボットテーブルを更新する方法

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

今後の記事について

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

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

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

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