EXCEL VBA でピボットテーブルの挿入を記録する

　ピボットテーブルは 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