问题描述
我必须使用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:创建数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!