前回「マクロの記録」のコードをそのまま使ってスリム化しました。でも、まだまだ汎用化して使えるようにするには足りないことがたくさんあります。
今回は、ピボットテーブルを更新する方法について解説します。
ピボットテーブルを作成できるようになりましたが、まだまだ知っておくべきことはたくさんあります。今回は、更新する方法を勉強しましょう(^^)
わかりました(^^) よろしくお願いします！
【この記事でわかることは】
・ピボットキャッシュをオブジェクト型変数にセットして再利用する方法
・ピボットテーブルを更新する複数の方法
・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("年齢（５歳階級）再掲有り_時系列").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("年齢（５歳階級）").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個ピボットテーブルを作っていても１回で済みます。
・この方が無駄が無くて速くなります！ これが正解だよ！
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)したかを返してくれます。
まとめ（おわりに）
・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)
まとめと感想など
ピボットキャッシュの動作確認ができましたね。ピボットテーブルの更新方法を複数勉強しました。実際に動作確認するとどうすれば効率的なのかが良くわかりますね(^^)
はい！ すごくおもしろかったです(^^) ピボットテーブルの更新方法がどのようになっているのかが良くわかりました(^^; よく復習してみます！
今回は元データがテーブルだったので、データ追加を考えなくてよかったけど、セル範囲だった場合は元データの範囲を変更する必要があります！ では次回はその部分をもう少し詳しく勉強していきましょう(^^)/
★★★ ランキング参加中！ クリックしてね(^^)/ ★★★
今後の記事について
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/
記事のサンプルファイルをダウンロードできます
今回の記事のサンプルをダウンロードできるようリンク先に登録しています！
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください
【今回分かったことは】
・ピボットテーブルをオブジェクト型変数にセットする方法
・PivotCache でピボットテーブルを更新する方法
・PivotCaches コレクションで PivotCache の数だけ更新する方法
・RefreshTable でピボットテーブルを更新する方法