VBA Excel: PivotTable Visibility and PivotField inclusion problems
I've been encountering two "mysterious" and frustrating results with my VBA script for MS Excel 2000.
1) PivotItem Visibility Issue
' Create and focus on a sheet for the Pivot Table:
Sheets.Add.Name = Tabname & " Open Table"
Sheets(Tabname & " Open Table").Select
' Create the Pivot Table (this is merely modified code from a recorded macro...):
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & Tabname & "'!R1C1:R3000C54").CreatePivotTable TableDestination:=Range("A1"), _
TableName:="Pivot1 " & Tabname
ActiveSheet.PivotTables("Pivot1 " & Tabname).SmallGrid = False
' Create a corresponding PivotChart:
Charts.Add.Name = Tabname & " Open Chart"
ActiveChart.SetSourceData Source:=Sheets(Tabname & " Open Table").Range("A1")
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ChartArea.Select
Sheets(Tabname & " Open Table").Select
' Arrange the PivotTable Layout:
With ActiveSheet.PivotTables("Pivot1 " & Tabname).PivotFields("Project")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Pivot1 " & Tabname).PivotFields("Binary Status")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("Pivot1 " & Tabname).PivotFields("Status")
.Orientation = xlDataField
.Position = 1
End With
' The problem area is below (everything prior was to provide context).
' PivotItem "Open Document" in the "Binary Status" PivotField is *always*
' marked as Visible=false and I am unable to programmatically make it visible.
'
' I read that this may have been part of a bug; however, upon checking my
' Excel version, it seems that I have an updated executable.
' Furthermore, I read that setting AutoSort to Manual may help; however,
' I'm not sure how to do that in VBA.
'
' Two other PivotItems under "Binary Status" do not experience this problem
' and are quite visible. Also, I am able to set any of them to Visible=false
' without incident.
' Arrange the PivotTable Layout:
'With ActiveSheet.PivotTables("Pivot1 " & Tabname).PivotFields("This Week")
'.AutoSortOrder.xlAscending
'End With
' Show "Open Document" ...please?:
With ActiveSheet.PivotTables("Pivot1 " & Tabname).PivotFields("Binary Status")
.PivotItems("(blank)").Visible = False
.PivotItems("Open Document").Visible = True
End With
'With ActiveSheet.PivotTables("Pivot1 " & Tabname).PivotFields("This Week")
' .AutoSort xlAscending
'End With
2) Including a PivotField into a PivotTable
Sheets.Add.Name = Tabname & " ChangeReq Table"
Sheets(Tabname & " ChangeReq Table").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & Tabname & "'!R1C1:R3000C54").CreatePivotTable TableDestination:=Range("A1"), _
TableName:="Pivot4 " & Tabname
ActiveSheet.PivotTables("Pivot4 " & Tabname).SmallGrid = False
Charts.Add.Name = Tabname & " ChangeReq Chart"
ActiveChart.SetSourceData Source:=Sheets(Tabname & " ChangeReq Table").Range("A1")
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ChartArea.Select
' Make only "Open CR" visible, which works fine... (see 1)
With ActiveChart.PivotLayout.PivotFields("Binary Status")
.PivotItems("Open Other").Visible = False
.PivotItems("Open CR").Visible = True
.PivotItems("Open Document").Visible = False
.PivotItems("(blank)").Visible = False
End With
Sheets(Tabname & " ChangeReq Table").Select
With ActiveSheet.PivotTables("Pivot4 " & Tabname).PivotFields("Defect Type")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Pivot4 " & Tabname).PivotFields("Defect Type")
.PivotItems("Bug").Visible = False
.PivotItems("Business Process chg").Visible = False
.PivotItems("Document").Visible = False
.PivotItems("Enhancement").Visible = False
.PivotItems("Functionality Release Pending").Visible = False
.PivotItems("Issue").Visible = False
.PivotItems("Issue-Workaround").Visible = False
.PivotItems("Problem").Visible = False
.PivotItems("Question").Visible = False
.PivotItems("Requirement").Visible = False
.PivotItems("Risk").Visible = False
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("Pivot4 " & Tabname).PivotFields("Status")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("Pivot4 " & Tabname).PivotFields("Status")
.Orientation = xlDataField
.Position = 1
End With
' For some reason, I can't "dock" PivotField "Project" to the right of "Defect Type"
' on the xlRowField orientation. It just doesn't show up.
' When I recorded the action in a macro, it gave me this same code.
With ActiveSheet.PivotTables("Pivot4 " & Tabname).PivotFields("Project")
.Orientation = xlRowField
.Position = 2
Your help is appreciated in advance. Thanks.
[10554 byte] By [Grismath] at [2007-12-22] 