All,
I am pretty new to VBA scripting within excel.
I have a capacity planning report which gets data from Teradata Database and builds a pivot table. The data in pivot table is gotten from Teradata Database. Finally a chart is build from this Pivot.
The only problem to this is that for one such sample graph, I get the following error message.
"Unable to get the PivotItems Property of the PivotField Class".
Here's the snippet of the excel macro.
Sub QAF()
'
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"data!A:H").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("YY", "MM", _
"DD", "HH", "MI"), ColumnFields:="ArrFin", PageFields:="Accountname"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("NumQueries")
.Orientation = xlDataField
.Caption = "Sum of NumQueries"
.Function = xlSum
End With
ActiveSheet.Name = "pivot" ' to be added
Charts.Add
ActiveChart.Name = "graph" ' to be added
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlLine
With ActiveChart.PivotLayout.PivotTable.PivotFields("MI")
.PivotItems("(blank)").Visible = False
End With
End Sub
One other thing that I wanted to share is that the resultant data is greater than 65536 (which is the max number of rows in Excel).
Could this be a problem?
Please let me know if there is a way to circumvent this problem.
Thanks,
Vinay Bagare
Bookmarks