ピボットテーブルは EXCEL に初期から搭載されている分析ツールである．ピボットグラフで未だに散布図が描けないという致命的な欠陥があるが，それでも有用なツールであることに変わりはない．
今回の記事はコードの整形ができていない．PivotTable オブジェクトのプロパティが膨大で手がつけられなかったためである．時間がある時に取り組みたい．
ピボットテーブルの挿入
テーブルを選択した状態で「挿入」タブの「テーブル」から「ピボットテーブル」を選ぶ．
データモデルを追加しない場合
「ピボットテーブルの作成」ダイアログで何も変更せずにOKをクリックすると，外部の EXCEL ファイルへの接続情報を持たずにピボットテーブルを作成する．接続先が一箇所ならこれで問題ない．
この手順のマクロ記録が以下のコードである．
Sub Macro2()
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"テーブル1", Version:=6).CreatePivotTable TableDestination:="Sheet1!R3C1", _
TableName:="ピボットテーブル1", DefaultVersion:=6
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("ピボットテーブル1")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("ピボットテーブル1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("ピボットテーブル1").RepeatAllLabels xlRepeatLabels
End Sub
データモデルを追加する場合
「複数のテーブルを分析するかどうかを選択」で「このデータをデータモデルに追加する」をオンにすると，自分自身を含めた外部のファイルへの接続情報を追加することになる．
同様に手順をマクロ記録したコードである．
Sub Macro3()
Sheets("市区町村").Select
Workbooks("市区町村人口2000-2015.xlsm").Connections.Add2 _
"WorksheetConnection_市区町村人口2000-2015.xlsm!テーブル1", "", _
"WORKSHEET;C:\Users\User'sName\***\市区町村人口2000-2015.xlsm", _
"市区町村人口2000-2015.xlsm!テーブル1", 7, True, False
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("WorksheetConnection_市区町村人口2000-2015.xlsm!テーブル1"), _
Version:=6).CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:= _
"ピボットテーブル2", DefaultVersion:=6
Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("ピボットテーブル2")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = True
.CompactRowIndent = 1
.VisualTotals = False
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = True
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.DisplayEmptyRow = False
.DisplayEmptyColumn = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.DisplayImmediateItems = True
.ViewCalculatedMembers = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = True
.RowAxisLayout xlCompactRow
End With
ActiveSheet.PivotTables("ピボットテーブル2").PivotCache.RefreshOnFileOpen = False
ActiveSheet.PivotTables("ピボットテーブル2").RepeatAllLabels xlRepeatLabels
End Sub
Connections コレクションと WorkbookConnection オブジェクト
上記コードを見比べたところ，Connections コレクションの有無についての記述の違いが目立つ．オブジェクトブラウザーで検索すると，戻り値は WorkbookConnection オブジェクトであることが分かる．
| CLASS | MEMBER | Type of Return |
|---|---|---|
| Connections | Add2 | WorkbookConnection |
| AddFromFile | WorkbookConnection | |
| Application | Application | |
| Count | Long | |
| Creator | XlCreator | |
| Item | WorkbookConnection | |
| Parent | Object |
Connections.Add2 メソッドの引数
Function Add2(Name As String, Description As String, ConnectionString, CommandText, [lCmdtype], [CreateModelConnection], [ImportRelationships]) As WorkbookConnection
さて，オブジェクトブラウザーでは Connections.Add2 メソッドは WorkbookConnection オブジェクトを返すことになっている．だが上記サンプルコードでは引数の記述がある．ここを深堀りしてみる．
Connections.Add2(String, String, Object, Object, Object, Object, Object) Method
公式サイトによる解説は次のとおりである．
| Prameter | Data Type | Description |
|---|---|---|
| Name | String | 接続の名前を指定 |
| Description | String | 接続の簡単な説明 |
| ConnectionString | Object | 接続文字列を指定 |
| CommandText | Object | 接続を作成するコマンドテキストを指定 |
| lCmdtype | Object | コマンドの種類を指定 |
| CreateModelConnection | Object | PowerPivot モデルへの接続を作成するかどうかを指定 |
| ImportRelationships | Object | 既存のリレーションシップをインポートするかどうかを指定 |
"WorksheetConnection_市区町村人口2000-2015.xlsm!テーブル1", _ "", _ "WORKSHEET;C:\Users\User'sName\***\市区町村人口2000-2015.xlsm", _ "市区町村人口2000-2015.xlsm!テーブル1", _ 7, _ True, _ False
1 行目から 4 行目までは分かる．重要なのはおそらく 3 行目 ConnectionString と 4 行目 CommandText だ．6 行目と 7 行目は Boolean 型で指定しているのは分かる．
5 行目がよく分からない．lCmdtype を調べてみることにする．どうも先頭の大文字の X が抜けているのではないかという気がする．
定数 XlCmdtype のメンバー
公式の情報は XlCmdType Enum にある．
| MEMBER | CONST | Description |
|---|---|---|
| xlCmdCube | 1 | OLAP データソースで使用されるキューブ名 |
| xlCmdDAX | 8 | Data Analysis Expressions (DAX) の式 |
| xlCmdDefault | 4 | OLE DB プロバイダーが認識するコマンドテキスト |
| xlCmdExcel | 7 | Excel の式 |
| xlCmdList | 5 | データを一覧表示するためのポインター |
| xlCmdSql | 2 | SQL ステートメント |
| xlCmdTable | 3 | OLE DB データソースにアクセスするテーブル名 |
| xlCmdTableCollection | 6 | テーブルコレクションの名前 |
WorkbookConnection を返すオブジェクト
オブジェクトブラウザーで検索すると WorkbookConnection を返すオブジェクトは 5 つある．
| CLASS | Property |
|---|---|
| PivotCache | WorkbookConnection |
| QueryTable | WorkbookConnection |
| SlicerCache | WorkbookConnection |
| TableObject | WorkbookConnection |
| XmlMap | WorkbookConnection |
他に WorkbookConnection を返すオブジェクト
一方，上記サンプルコードから明らかなように Workbook オブジェクトのプロパティとしての Connections コレクションから派生する Add2 メソッドや Item メソッドも WorkbookConnection オブジェクトを返す．
オブジェクトブラウザーを Connection で検索すると，もっと多くのオブジェクトが抽出されてくる．
| CLASS | Method/Property |
|---|---|
| Model | AddConnection |
| Model | CreatemodelWorkbookConnection |
| Model | DataModelConnection |
| PivotTable | ChangeConnection |
| ModelTable | SourceWorkbookConnection |
オブジェクトブラウザーで検索できる範囲としては以上である．
WorkbookConnection オブジェクトは何をしているのか？
その名の通り EXCEL のファイルへの接続である．サンプルコードにファイルへのフルパスが記述されていることに留意されたい．今回のコードでは自分自身に接続しているが，外部ファイルへの接続も当然可能であると推測できる．
WorkbookConnection オブジェクトのメンバーと戻り値のデータ型
WorkbookConnection オブジェクトの関係が分かったところで，そのメンバーを調べてみよう．全部で 18 件，メソッドが 2 件，プロパティが 16 件である．
| MEMBER | Type of Return |
|---|---|
| Application | Application |
| Creator | XlCreator |
| DataFeedConnection | DataFeedConnection |
| Delete | Sub |
| Description | String |
| InModel | Boolean |
| ModelConnection | ModelConnection |
| ModelTables | ModelTables |
| Name | String |
| ODBCConnection | ODBCConnection |
| OLEDBConnection | OLEDBConnection |
| Parent | Object |
| Ranges | Ranges |
| Refresh | Sub |
| RefreshWithRefreshAll | Boolean |
| TextConnection | TextConnection |
| Type | XlConnectionType |
| WorksheetDataConnection | WorksheetDataConnection |
PivotCache オブジェクトにも注目
Macro2 コードの 3 行目，Macro3 コードの 8 行目にそれぞれ SourceType のキーワードがある．違いは xlDatabase か xlExternal かである．どうやらここで接続先を振り分けているらしい．
オブジェクトブラウザーで PivotCache オブジェクトを調べてみよう．全部で 40 件，うちメソッドが 6 件，プロパティが 34 件である．
オブジェクトブラウザーから抽出した結果である．サンプルコードに出現するキーワードを太字で示した．
| CLASS | MEMBER | Type of Return |
|---|---|---|
| PivotCache | ADOConnection | Object |
| Application | Application | |
| BackgroundQuery | Boolean | |
| CommandText | Variant | |
| CommndType | XlCmdType | |
| Connection | Variant | |
| CreatePivotChart | Shape | |
| CreatePivotTable | PivotTable | |
| Creator | XlCreator | |
| EnableRefresh | Boolean | |
| Index | Long | |
| IsConnected | Boolean | |
| LocalConnection | Variant | |
| MaintainConnection | Boolean | |
| MakeConnection | Sub | |
| MemoryUsed | Long | |
| MissingItemsLimit | XlPivotTableMissingItems | |
| OLAP | Boolean | |
| OptimizeCache | Boolean | |
| Parent | Object | |
| QueryType | XlQueryType | |
| RecordCount | Long | |
| RecordSet | Object | |
| Refresh | Sub | |
| RefreshDate | Date | |
| RefreshName | String | |
| RefreshOnFileOpen | Boolean | |
| RefreshPeriod | Long | |
| ResetTimer | Sub | |
| RobustConnect | XlRobustConnect | |
| SaveAsODC | Sub | |
| SavePassword | Boolean | |
| SourceConnectionFile | String | |
| SourceData | Variant | |
| SourceDataFile | String | |
| SourceType | XlPivotTableSourceType | |
| UpgradeOnRefresh | Boolean | |
| UseLocalConnection | Boolean | |
| Version | XlPivotTableVersionList | |
| WorkbookConnection | WorkbookConnection |
ここから，次に調査すべきオブジェクトが分かる．
PivotTables コレクション
PivotTable オブジェクトに行く前に PivotTables コレクションについても触れておく．メソッドが 2 件，プロパティが 4 件である．
| CLASS | MEMBER | Type of Return |
|---|---|---|
| PivotTables | Add | PivotTable |
| Application | Application | |
| Count | Long | |
| Creator | XlCreator | |
| Item | PivotTable | |
| Parent | Object |
PivotTales.Add メソッド
PivotTables コレクションのメソッドの中で Add メソッドは PivotTable オブジェクトを返すのであるが，引数 (Parameter) を取る．
PivotTables.Add (PivotCache, tabledestination, TableName, ReadData, DefaultVersion)
| Parameter | Data Type | Description |
|---|---|---|
| PivotCache | PivotCache | ピボットテーブルレポートの基になるピボットテーブルキャッシュを指定 |
| Tabledestination | Variant | ピボットテーブルレポートの配置先範囲 (結果のレポートを配置するワークシートの範囲) の左上端のセルを指定 |
| TableName | Variant | ピボットテーブルレポートの名前を指定 |
| ReadData | Variant | True の場合は，外部データベースのすべてのレコードを含むピボットテーブルキャッシュを作成する．False を設定すると，データが実際に読み取られる前に，一部のフィールドをサーバーベースのページフィールドとして設定できる |
| DefaultVersion | Variant | ピボットテーブルが最初に作成された Microsoft Excel のバージョン |
PivotTable オブジェクト
PivotTable オブジェクトのメンバーは膨大である．全部で 134 件，メソッドが 31 件，プロパティが 103 件．
| MEMBER | Type of Return |
|---|---|
| ActiveFilters | PivotFilters |
| AddDataField | PivotField |
| AddFields | Function |
| AllocateChanges | Sub |
| Allocation | XlAllocation |
| AllocationMethod | XlAllocationMethod |
| AllocationValue | XlAllocationValue |
| AllocationWeightExpression | String |
| AllowMultipleFilters | Boolean |
| AlternativeText | String |
| Application | Application |
| ApplyLayout | Sub |
| CacheIndex | Long |
| CalculaedMembersInFilters | Boolean |
| CalculatedFields | CalculatedFields |
| CalculatedMembers | CalculatedMembers |
| ChangeConnection | Sub |
| ChangeList | PivotTableChangeList |
| ChangePivotCache | Sub |
| ClearAllFilters | Sub |
| ClearTable | Sub |
| ColumnChanges | Sub |
| ColumnFields | Object |
| ColumnGrand | Boolean |
| ColumnRange | Range |
| CompactLayerRowHeader | String |
| CompactLayoutColumnHeader | String |
| CompactRowIndent | Long |
| ConvertToFormulas | Sub |
| CreateCubeFile | String |
| Creator | XlCreator |
| CubeFields | CubeFields |
| DataBodyRange | Range |
| DataFields | Object |
| DataLabelRange | Range |
| DataPivotField | PivotField |
| DiscardChanges | Sub |
| DisplayContextTooltips | Boolean |
| DisplayEmptyColumn | Boolean |
| DisplayEmptyRow | Boolean |
| DisplayErrorString | Boolean |
| DisplayFieldCaptions | Boolean |
| DisplayImmediateItems | Boolean |
| DisplaymemberPropertyTooltips | Boolean |
| DisplayNullString | Boolean |
| DrillDown | Sub |
| DrillTo | Sub |
| DrillUp | Sub |
| EnableDataValueEditing | Boolean |
| EnableDrilldown | Boolean |
| EnableFieldDialog | Boolean |
| EnableFieldList | Boolean |
| EnableWizard | Boolean |
| EnbaleWriteBack | Boolean |
| ErrorString | String |
| FieldListSortAscending | Boolean |
| GetData | Double |
| GetPivotData | Range |
| GrandTotalName | String |
| HasAutoFormat | Boolean |
| Hidden | Boolean |
| HiddenFields | Object |
| InGridDropZones | Boolean |
| InnerDetail | String |
| LayoutRowDefault | XlLayoutRowType |
| ListFormulas | Sub |
| Location | String |
| ManualUpdate | Boolean |
| MDX | String |
| MergeLabels | Boolean |
| Name | String |
| NullString | String |
| PageFieldOrder | Long |
| PageFields | Object |
| PageFieldStyle | String |
| PageFieldWrapCount | Long |
| PageRange | Range |
| PageRangeCells | Range |
| Parent | Object |
| PivotCache | PivotCache |
| PivotChart | Shape |
| PivotColumnAxis | PivotAxis |
| PivotFields | Object |
| PivotFormulas | PivotFormulas |
| PivotRowAxis | PivotAxis |
| PivotSelect | Sub |
| PivotSelection | String |
| PivotSelectionStandard | String |
| PivotTableWizard | Sub |
| PivotValueCell | PivotValueCell |
| PreserveFormatting | Boolean |
| PrintDrillIndicators | Boolean |
| PrintTitles | Boolean |
| RefreshAllLabels | Boolean |
| RefreshDataSourceValues | Sub |
| RefreshDate | Date |
| RefreshName | String |
| RefreshTable | Boolean |
| RepeatItemsOnEachPrintedPage | Boolean |
| RowAxisLayout | Sub |
| RowFields | Object |
| RowGrand | Boolean |
| RowRange | Range |
| SaveData | Boolean |
| SelectionMode | XlPTSelectionMode |
| ShowDrillIndicators | Boolean |
| ShowPageMultipleItemLabel | Boolean |
| ShowPages | Function |
| ShowTableStyleColumnHeaders | Boolean |
| ShowTableStyleColumnStripes | Boolean |
| ShowTableStyleLastColumn | Boolean |
| ShowTableStyleRowHeaders | Boolean |
| ShowTableStyleRowStripes | Boolean |
| ShowValuesRow | Boolean |
| Slicers | Slicers |
| SmallGrid | Boolean |
| SortUsingCustomLists | Boolean |
| SourceData | Variant |
| SubtotalHiddenPageItems | Boolean |
| Summary | String |
| SutotalLocation | Sub |
| TableRange1 | Range |
| TableRange2 | Range |
| TableStyle2 | Variant |
| Tag | String |
| TotalAnnotation | Boolean |
| Update | Sub |
| VacatedStyle | String |
| Value | String |
| Version | XlPivotTableVersionList |
| ViewCaculatedMembers | Boolean |
| VisibleFields | Object |
| VisualTotals | Boolean |
| VisualTotalsForSets | Boolean |
定数 XlPivotTableSourceType
| MEMBER | CONST |
|---|---|
| xlConsolidation | 3 |
| xlDatabase | 1 |
| xlExternal | 2 |
| xlPivotTable | -4148 |
| xlScenario | 4 |