还是被这个递归,以及各种复杂的名称搞得晕头转向,不得不记录一下理解的过程。方便以后的应用。其实关键不在于实现这个事件,而在于代码的思路。
Sub PrintPvtTblblByMultiPageFlds()
Dim objPvtTbl As PivotTable
Dim objPvtTblFld As PivotField
Dim objPvtTblIm As PivotItem
Dim i As Integer
Dim m As Integer
Dim astrCurrentPageFld() As String
Set objPvtTbl = Sheets("数据透视表").PivotTables(1)
With objPvtTbl
If .PageFields.Count = 0 Then
MsgBox "当前数据透视表中没有筛选字段!", vbInformation, "提示"
Exit Sub
End If
m = .PageFields.Count
ReDim astrCurrentPageFld(1 To .PageFields.Count)
For i = 1 To .PageFields.Count
astrCurrentPageFld(i) = .PageFields(i).CurrentPage
Next
For Each objPvtTblIm In .PageFields(1).PivotItems
.PageFields(1).CurrentPage = objPvtTblIm.Name
' If .PageFields.Count = 1 Then 这个IF条件是给单筛选字段弄的。对于多筛选字段,只要else下面那句call。
' .Parent.PrintOut
' .Parent.PrintPreview
' Else
Call PrintPvtTbl(objPvtTbl, 2) '此处断点会帮助理解。
' End If
Next
For i = 1 To UBound(astrCurrentPageFld)
.PageFields(i).CurrentPage = astrCurrentPageFld(i)
Next
End With
MsgBox m
End Sub
Sub PrintPvtTbl(ByVal objPvtTbl As PivotTable, ByVal iPageFldIndex As Integer)
Dim objPvtTblIm As PivotItem
With objPvtTbl
If iPageFldIndex = .PageFields.Count Then
For Each objPvtTblIm In .PageFields(iPageFldIndex).PivotItems
.PageFields(iPageFldIndex).CurrentPage = objPvtTblIm.Name '设个断点然后watch objPvtTblIm.Name
' .Parent.PrintOut
.Parent.PrintPreview
Next
Exit Sub
Else
For Each objPvtTblIm In .PageFields(iPageFldIndex).PivotItems
.PageFields(iPageFldIndex).CurrentPage = objPvtTblIm.Name
Call PrintPvtTbl(objPvtTbl, iPageFldIndex + 1) '设个断点然后watch iPageFldIndex
Next
End If
End With
End Sub
打印效果:
理解起来无非是,递归不停压东西入栈(去到
If iPageFldIndex = .PageFields.Count Then
)然后打印一层颜色都变,版本号和规格型号都不变的sheets。然后
.PageFields(iPageFldIndex).CurrentPage = objPvtTblIm.Name
去到了版本号的下个选项。最外层的规格型号也是这道理。