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

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください