VBA:创建数据透视表 [英] vba: create pivot table

问题描述

我必须使用vba创建数据透视表，但出现以下错误:关于此代码的运行时错误'438'对象不支持此属性或方法":ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Sheet1!R1C1:R1048576C8", Version:=6).CreatePivotTable TableDestination:= _ pivotTableWs!R1C1, tableName:=tableName, DefaultVersion:=6

I have to create a pivot table using vba but i got the following error: "Run-time error '438' Object doesn't support this property or method" about this code: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Sheet1!R1C1:R1048576C8", Version:=6).CreatePivotTable TableDestination:= _ pivotTableWs!R1C1, tableName:=tableName, DefaultVersion:=6

完整的源文件

Dim tableName As String
Dim pivotTableWs As Worksheet

tableName = "pivotTableName"

Set pivotTableWs = Sheets.Add(after:=Worksheets("Sheet1"))
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R1048576C8", Version:=6).CreatePivotTable TableDestination:= _
    pivotTableWs!R1C1, tableName:=tableName, DefaultVersion:=6
Sheets(pivotTableWs).Select
Cells(1, 1).Select
With ActiveSheet.PivotTables(tableName)
    .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(tableName).PivotCache
    .RefreshOnFileOpen = False
    .MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables(tableName).RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables(tableName).PivotFields("field1")
    .Orientation = xlRowField
    .Position = 1
End With
ActiveSheet.PivotTables(tableName).AddDataField ActiveSheet.PivotTables( _
    tableName).PivotFields("ticketid"), "Count of field1", xlCount
With ActiveSheet.PivotTables(tableName).PivotFields("field2")
    .Orientation = xlColumnField
    .Position = 1
End With

我使用开发人员"标签创建此代码，选择宏注册"，然后手动创建数据透视表

I create this code using "Developer" tab, selected "Macro register" and i create pivot table manually

推荐答案

我添加了2个对象变量PvtTbl As PivotTable和PvtCache As PivotCache以使代码更加动态.

I've added 2 Object variables PvtTbl As PivotTable and PvtCache As PivotCache to make the code more dynamic.

其他说明在下面的代码中(作为注释).

Other explanations are inside the code below (as comments).

代码

Code

Option Explicit

Sub AutoPivot()

Dim PvtTbl As PivotTable
Dim PvtCache As PivotCache

Dim PvtTblName As String
Dim pivotTableWs As Worksheet

PvtTblName = "pivotTableName"

' set the worksheet object where we will create the Pivot-Table
Set pivotTableWs = Sheets.Add(after:=Worksheets("Sheet1"))

' set the Pivot Cache (the Range is static)
Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!R1C1:R1048576C8")

' create a new Pivot Table in the new created sheet
Set PvtTbl = pivotTableWs.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=pivotTableWs.Range("A1"), TableName:=PvtTblName)

' after we set the PvtTbl object, we can easily modifty all it's properties
With PvtTbl
    .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

    With .PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With

    .RepeatAllLabels xlRepeatLabels

    With .PivotFields("field1")
        .Orientation = xlRowField
        .Position = 1
    End With

    .AddDataField .PivotFields("ticketid"), "Count of field1", xlCount

    With .PivotFields("field2")
        .Orientation = xlColumnField
        .Position = 1
    End With

End With

End Sub

这篇关于VBA:创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！