上篇文章,我们一起了解了VBA操作数据透视表的过程&梳理了数据透视表操作设计到的对象以及他们的层次关系。利用Excel强大的宏录制功能生成了相应的VBA代码,细心的你一定发现宏录制成的代码实在是复杂。从这期开始,我们将一起学习透视表相关的对象以及这些对象的方法。通过接下来数篇文章的学习您可以写出更简单的VBA代码。


在本期文章中,您将学到如下内容

目录

⊙  对象层次关系

⊙创建PivotTable对象

⊙  访问PivotTable对象


1
对象层次关系

由于本文主要介绍透视表的创建和访问,在学习这个之前点前。我们先概要地了解下,实现透视表创建&访问所涉及到的透视表对象以及他们之间的层次关系。通过了解层次关系可以帮助我们建立对透视表对象的初步认识,为我们后续的学习打下坚实的基础。


涉及对象

PivotCache、PivotCaches、PivotTable、PivotTables

层次关系

EXCEL 数据透视表(九) 教程 第1张



PivotCaches

代表工作簿中数据透视表内存缓冲区的集合。每个内存缓冲区都由一个PivotCache对象表示。

EXCEL 数据透视表(九) 教程 第2张

属性


EXCEL 数据透视表(九) 教程 第3张

方法

PivotCache

代表一个数据透视表的内存缓冲区。PivotCache对象是PivotCaches集合的成员。


PivotTables

指定工作表上所有PivotTable对象的集合。


PivotTable

代表工作表上的一张数据透视表。PivotTable对象是PivotTables集合的成员。PivotTables集合包含单张工作表中的所有 PivotTable对象。


2
创建PivotTable对象

1.创建cache

Dim caches As PivotCaches  ‘定义PivotCaches对象指向当前workbook的caches集合

Dim cache As PivotCache  ’定义PivotCache 对象指向待创建的cache

Set caches = ActiveWorkbook.PivotCaches

Set cache = caches.Create(xlDatabase, "Sheet2!R1C1:R3C4", Version:=xlPivotTableVersion12)


关键的Create方法参数说明

EXCEL 数据透视表(九) 教程 第4张


EXCEL 数据透视表(九) 教程 第5张

EXCEL 数据透视表(九) 教程 第6张

本例针对Excel中的数据区域创建透视表,后面将讲解如何针对外部数据源创建透视表(例如数据库)


        2.创建Pivottable

Dim table As PivotTable   ‘定义Pivottable用于指向待创建的透视表

Set table = cache.CreatePivotTable(TableDestination:="Sheet2!R4C1", TableName:="PIVOT", defaultversion:=xlPivotTableVersion12) ’调用CreatePivotTable方法,创建透视表


CreatePivotTable方法说明

EXCEL 数据透视表(九) 教程 第7张

注意:如果sheet对应的区域已经有透视表,创建则会出错。

EXCEL 数据透视表(九) 教程 第8张

感兴趣的表亲,可以考虑下该如何检测到这个错误呢?可以在文章底部留言,答案将在下一期公布


要想真正掌握VBA操作透视表,上面介绍的这两个方法一定要掌握,在后续的文章中我们会继续深入挖掘这些方法。

3
 访问PivotTable对象

访问指定的透视表

Dim table As PivotTable

Dim tables as PivotTables


set tables = activesheet.Pivottables

set table = tables("PIVOT") ’传入对应的透视表名称


遍历当前sheet页所有的透视表

Dim table As PivotTable  ' 定义PivotTable对象

Dim tables As PivotTables  ‘定义PivotTables对象


Set tables = ActiveSheet.PivotTables ’获取到活动worksheet的pivottables集合


For i = 1 To tables.Count   ‘ 通过循环遍历所有的pivottable 


Set table = tables.Item(i)    '通过item找到对应的table

MsgBox table.Name & table.TableRange1.Address ’ 打印出Pivottable对应的名称&数据源区域