はじめに
ひらちんです。
以下の記事でVBAでピボットテーブルを作成する方法を紹介しました。
今回は、作ったピボットテーブや既に存在しているピボットテーブルを取得したり更新する方法を紹介します！
多分、VBAではピボットテーブル作るよりも、既に作ってあるピボットテーブルを更新する方が多いかなと思います(^o^)
ピボットテーブルを捕まえる
VBAからピボットテーブルを更新するには、まずは更新したいピボットテーブルを捕まえる必要がありますね。
まずはガサッと一気に取得してみましょう！
ピボットテーブルを捕まえるには、ターゲットのシートオブジェクトのPivotTablesメソッドを使って、シート上にあるすべてのPivotTableオブジェクトを取得します。
set 変数 = シートオブジェクト.PivotTables
以下のようなサンプル（”sample”シート）にある3つのピボットテーブルを取得してみましょう！
Sub sample()
Dim ピボットテーブル達 As PivotTables
Set ピボットテーブル達 = Sheets("sample").PivotTables
Dim ピボットテーブル As PivotTable
For Each ピボットテーブル In ピボットテーブル達
Debug.Print ピボットテーブル.Name
Next
End Subきちんと取得出来たことが分かるように、For Eachで回してピボットテーブルの名前をイミディエイトウィンドウに出力しています。
※PivotTablesオブジェクトはコレクションになっているので、For Eachで回せます(^o^)
実行します。
シート上にあるピボットテーブルの名前が出力出来ました。
ピボットテーブルオブジェクト.Name
これは、ピボットテーブルの名前を格納しているプロパティです
EXCELブック上にある複数のシートすべてのピボットテーブルを取得する場合は、WorkSheetsコレクションも回して、2重で行けばいけますね(^o^)
Sub sample()
Dim シート As Worksheet
Dim ピボットテーブル達 As PivotTables
Dim ピボットテーブル As PivotTable
For Each シート In ThisWorkbook.Worksheets
Set ピボットテーブル達 = シート.PivotTables
For Each ピボットテーブル In ピボットテーブル達
Debug.Print ピボットテーブル.Name
Next
Next
End Subちなみに、シート上にピボットテーブルがあるかどうか確認するためには、PivotTablesオブジェクトのCountプロパティで判定出来ます(^o^)
PivotTablesオブジェクト.Count
Sub sample()
Dim ピボットテーブル達 As PivotTables
Set ピボットテーブル達 = Sheets("sample").PivotTables
'ピボットテーブルの数
Debug.Print ピボットテーブル達.Count
End Subこんな感じですね(^o^)
ピボットテーブル名を指定して取得する
ピボットテーブルをピボットテーブル名で個別に取得する場合は、以下のように書きます。
Set 変数 = シートオブジェクト.PivotTables(”ピボットテーブル名”)
Sub sample()
Dim ピボットテーブル As PivotTable
Set ピボットテーブル = Sheets("sample").PivotTables("ピボットテーブル2")
Debug.Print ピボットテーブル.Name
End Subここでも、取得したピボットテーブルが合っているか名前を表示させて確認してみましょう！
出来ましたね！
他にも、要素番号で取得する方法もありますが、1シートに複数ピボットテーブルがある場合、どれが何番目やねん！？っていう問題も発生すると思うので、とりあえず省略します笑
一気に取得して、For Eachで回してIFで名前を判定するのは行けそうですね(^o^)
ピボットテーブルを更新する
では、取得したピボットテーブルを更新しましょう！！
個別に更新する
ここまででやったように、取得したピボットテーブルを個別に更新する場合は、
ピボットテーブルオブジェクト.PivotCache.Refresh
とします。
Sub sample()
Dim ピボットテーブル As PivotTable
Set ピボットテーブル = Sheets("sample").PivotTables("ピボットテーブル2")
'個別にピボットテーブルを更新する
ピボットテーブル.PivotCache.Refresh
End Sub一気に更新する
色々取得の話をしてきましたが、ブック全体のピボットテーブルを更新する方法もあります(^o^)
ワークブックオブジェクト.RefreshAll
Sub sample()
ThisWorkbook.RefreshAll
End Subこれで全部更新出来ます!
コメント
ピボットテーブルは、データキャッシュで共有しているので、
「更新」クリック１回だけで、完了です。
個別に更新する必要はありません。
また、ブック全体であれば、「すべて更新」で可能です。
元データと連携する時に、マクロでの操作は便利です。
ピボットテーブルには、データキャッシュに「デ―タ」が隠れています。
新規に作成する際には・・オプション設定で対策が必要です。
私の３０年の経験から、ＶＢＡマクロで新規作成はお勧めしませんが、
多くの方が、知らずに、データ流出のリスクを抱えています！
是非、セキュリティ対策を含めた紹介をお願いします。
ｎｏｔｅに記事を掲載しました
https://note.com/meguseed/n/ne325b855ddbf
https://note.com/meguseed/n/n2370b11af546
コメントありがとうございます！
すごい深掘りされてるんですね(^o^)
勉強になります！