16.5　在代码中引用数据透视表

实际工作中经常需要运用代码处理工作簿中已经创建的数据透视表，这就需要引用指定的数据透视表，然后进行相关操作。对于代码中新创建的数据透视表，可以使用Set语句将数据透视表对象赋值给一个对象变量，以便于后续代码的引用。

Excel中的PivotTables集合代表指定工作表中所有PivotTable对象组成的集合，在图16-1可以看出PivotTables对象集合是WorkSheet对象的子对象，而不是隶属于WorkBook对象。

与Excel中的其他对象集合类似，数据透视表对象也可以通过名称或者序号进行引用。如果数据透视表的名称是固定的，在代码中则可以使用其名称引用数据透视表。

示例16.6　数据透视表的多种引用方法

打开示例文件“数据透视表的多种引用方法.xlsm”，“数据透视表”工作表为该工作簿中的第一个工作表，并且其中只有一个数据透视表名称为“PvtOnSheetl”，如图16-17所示。

图16-17　工作表中的数据透视表区域

那么下面的4个引用方式是完全相同的：

Sheets（“数据透视表”）.PivotTables(“PvtonSheetl”)

Sheets(1).PivotTables(“PvtOnSheet1”)

Sheets（“数据透视表”）.PivotTables(1)

Sheets(1).PivotTables(1)

使用数据透视表区域内任意Range对象的PivotTable属性都可以引用该数据透视表，本例中的数据透视表区域为A1：H11。

Sheets(“Sheet1”).Cells(1, “A”).PivotTable

Sheets(“Sheet1”).Range(“H1”).PivotTable

Sheets(“Sheet1”).Cells(11,“H”).PivotTable

注意

图16-17所示工作表中的C1:H1单元格区域虽然是空白区域，但是这些单元格仍然属于数据透视表区域，因此可以使用其PivotTable属性引用数据透视表。

示例16.7　遍历工作簿中的数据透视表

在示例文件“遍历工作簿中的数据透视表.xlsm”中已经创建了4个季度的数据透视表分别位于4个不同的工作表中，如图16-18所示。

即使不知道这些数据透视表的名称，在代码中仍可以使用For…Next循环结构遍历PivotTables集合中的所有PivotTable对象。

图16-18　季度数据透视表

示例代码将遍历当前工作簿中的所有数据透视表，并显示其名称和所在工作表的名称，运行AIIPivotTables过程结果如图16-19所示。

代码解析：

第5行代码利用字符串连接符“&”生成消息框中的第一行标题，其中vbTab代表制表符。

第6行代码到第11行代码为双层For…Each嵌套循环。其中外层For…Each循环用于遍历当前工作簿中的全部工作表，内层For…Each循环用于遍历指定工作表中的PivotTable对象。

图16-19　遍历数据透视表

第9行代码生成消息框的显示内容。其中第一个Name属性返回数据透视表的名称，“.Parent.Name”返回工作表的名称。vbCrLf代表回车换行符。

第13行代码显示类型为vblnformation，标题为“AIIPivotTable”的消息框，显示的内容为字符串变量strMsg的值。

第14行代码和第15行代码释放对象变量所占用的系统资源。