皆様こんにちは、ノグチです。
前回は、エクセルVBAでピボットテーブルを操作するための第一歩として、PivotTableオブジェクトについてご紹介しました。
今回は、エクセルVBAでピボットテーブルを作成する手順の1ステップ目、ピボットキャッシュにデータソースをセットする方法をご紹介していきます。
ピボットテーブルやオブジェクトとは何ぞや？という方は、下記の記事を併せてご覧ください。
PivotCacheオブジェクトとは
手作業でピボットテーブルを作成場合、セル範囲を入力するなりドラッグするなりして、データソース範囲を指定しますよね。
エクセルVBAでピボットテーブルを作成する場合も、データソース範囲を指定するという点では手動作成の場合と手順は変わりませんが、手動でピボットテーブルを作成する際には意識しないものを使うことを知っておく必要があります。
それが、ピボットキャッシュです。
手動でピボットテーブルを作成するとき、データソースにしたい範囲を選択すればピボットテーブルができますよね。
なので、「ピボットテーブル=データソースに指定したデータそのもの」と考えがちなのですが、厳密に言うとエクセルの中では、
- データソースからピボットキャッシュにデータをコピー
- ピボットキャッシュからピボットテーブル作成
という順序で処理されています。
ピボットテーブルのデータソースを書き換えてたとき、「更新」するまで書き換えたデータソースの内容はピボットテーブルに反映されませんよね。
これは、ピボットテーブルがデータソースを常に直接参照しているのではなく、ピボットテーブルのデータソースを「範囲指定した瞬間のデータ」または「データソース範囲を変更した瞬間のデータ」を参照しているからなのです。
つまり、手動でピボットテーブルを作成する場合には意識しないこのピボットキャッシュを、エクセルVBAでピボットテーブルを作成したり、データを変更したりする場合は操作する必要があるのです。
PivotCacheオブジェクトを作成する方法
ピボットテーブルは、データソースからピボットキャッシュができ、ピボットキャッシュからピボットテーブルができること、エクセルVBAでピボットテーブルのデータを操作する場合は、ピボットキャッシュを操作する必要があることをお伝えしました。
エクセルVBAでピボットテーブルを作成する場合も同様に、まずデータソース範囲を指定する必要があります。
そこで、データソース範囲をVBAで操作できるように、Createメソッドを使って指定したデータソース範囲を持ったPivotCacheオブジェクトを作ります。
エクセルVBAの記述方法はこちら。
今回はエクセルのリストをデータソースにしますので、「xlDatabase」を指定します。
これで、指定した範囲のデータソースを持つPivotCacheオブジェクトができる=ピボットキャッシュをVBAで操作するための準備ができます。
ピボットキャッシュをセットするVBAプログラム
では次に、データソースの範囲指定方法を、例を用いてご紹介していきます。
「データ」というシートの表をデータソースとして、「ピボットテーブル」シートにピボットテーブルを作成する、というコードを用意しました。
今回はピボットキャッシュの格納だけを記述したいので、PivotChaceオブジェクト型変数を使って、「データ」シートにある指定したセル範囲をピボットキャッシュにする、という内容にしています。
データソースとして指定したいセル範囲は、Rangeを使って記述します。
Sub Create_pivotCache()
Dim DataS As Worksheet 'データシート
Dim PCache As PivotCache 'ピボットキャッシュ格納用変数
Set DataS = ActiveWorkbook.Worksheets("データ")
'ピボットテーブルキャッシュに、「データ」シートのセル範囲をセット
Set PCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=DataS.Range("A1:F20"))
End Sub
これで、
「このブックの「データシート」にあるA1セル～F20セルをピボットキャッシュにセットする」
ことができました!
まとめ
今回は、エクセルVBAでピボットテーブルを作成する手順の一つ目として、データソースをピボットキャッシュに格納する方法をご紹介しました。
今回はコードの中でセル範囲を(“A1:F20”)のように直接記述していましたが、もちろん可変にすることもできますよ。
その方法のご案内はまた別の機会に。
それでは、次回は今回格納したピボットキャッシュから、ピボットテーブルを作成していきましょう。
最後までお読みいただき、ありがとうございました！
