一簿中多表创建透视表的练习

OKJSJSF

Sub add()
    Dim i As Byte
    i = ActiveSheet.PivotTables.Count
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets("贷款明细").Range("a1:x10")).CreatePivotTable TableDestination:=ActiveCell.Offset(0, 4 * i), TableName:="PivotTable" & i + 1
End Sub
上面代码为上楼代码加上参数标签。结果相同，速度不清楚。

OKJSJSF

熟悉的面孔：

OKJSJSF

Sub add()
    Dim objtable As Object, i As Byte
    i = ActiveSheet.PivotTables.Count
    Set objtable = ActiveWorkbook.PivotCaches.Create(xlDatabase, Worksheets("贷款明细").Range("a1:x10")).CreatePivotTable(ActiveCell.Offset(0, 4 * i), "PivotTable" & i + 1)
    Set objtable = Nothing
End Sub上面代码把某一个透视表设为对象变量，参数就要用括号（）。代码中未使用参数标签。

OKJSJSF

同上楼，但使用了参数标签。Sub add()
    Dim objtable As Object, i As Byte
    i = ActiveSheet.PivotTables.Count
    Set objtable = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets("贷款明细").Range("a1:x10")).CreatePivotTable(TableDestination:=ActiveCell.Offset(0, 4 * i), TableName:="PivotTable" & i + 1)
    Set objtable = Nothing
End Sub

OKJSJSF

Sub add()
    Dim objcache As Object, i As Byte
    i = ActiveSheet.PivotTables.Count
    Set objcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Worksheets("贷款明细").Range("a1:x10"))
    With objcache
        .CreatePivotTable TableDestination:=ActiveCell.Offset(0, 4 * i), TableName:="PivotTable" & i + 1
    End With
    Set objcache = Nothing
End Sub上面代码把某一个缓存设为对象变量，未用参数标签。

OKJSJSF

下面代码把某一个缓存设为对象变量，有参数标签。
Sub add()
    Dim objcache As Object, i As Byte
    i = ActiveSheet.PivotTables.Count
    Set objcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Worksheets("贷款明细").Range("a1:x10"))
    With objcache
        .CreatePivotTable TableDestination:=ActiveCell.Offset(0, 4 * i), TableName:="PivotTable" & i + 1
    End With
    Set objcache = Nothing
End Sub

OKJSJSF

Sub add()
    Dim objcache As Object, objtable As Object, i As Byte
    i = ActiveSheet.PivotTables.Count
    Set objcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Worksheets("贷款明细").Range("a1:x10"))
    With objcache
        Set objtable = .CreatePivotTable(TableDestination:=ActiveCell.Offset(0, 4 * i), TableName:="PivotTable" & i + 1)
    End With
    Set objcache = Nothing
    Set objtable = Nothing
End Sub上面代码把某个缓存及它的某个透视表均设为对象变量，进行嵌套。

OKJSJSF

上面代码数据源类型均为单元格区域，如果改为连接，怎么写？SourceType:=xlExternal, SourceData:=ActiveWorkbook.Connections("贷款明细2")
多了一个新集合Connections

OKJSJSF

必须参照《excel 2007 vba 实战技巧精粹》第18章“ADO应用”进行练习了。其中有：动态创建数据库文件、连接字符串、动态建立数据表 等技巧。与创建透视表连接属性有点类似。

OKJSJSF

我一般在一个工作簿的空白分类汇总表的A1单元格中创建透视表。