【EXCEL VBA】VBAでピボットテーブルの取得と更新

スポンサーリンク

はじめに

ひらちんです。

以下の記事で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

これで全部更新出来ます!

コメント

  1. 志賀 朗 より:

    ピボットテーブルは、データキャッシュで共有しているので、
    「更新」クリック1回だけで、完了です。

    個別に更新する必要はありません。

    また、ブック全体であれば、「すべて更新」で可能です。

    元データと連携する時に、マクロでの操作は便利です。

  2. 志賀 朗 より:

    ピボットテーブルには、データキャッシュに「デ―タ」が隠れています。

    新規に作成する際には・・オプション設定で対策が必要です。

    私の30年の経験から、VBAマクロで新規作成はお勧めしませんが、

    多くの方が、知らずに、データ流出のリスクを抱えています!

    是非、セキュリティ対策を含めた紹介をお願いします。

    noteに記事を掲載しました

    https://note.com/meguseed/n/ne325b855ddbf

    https://note.com/meguseed/n/n2370b11af546

タイトルとURLをコピーしました