循环用于切片器选择，处理常规透视，但不处理电源透视

Question

All - 这是在RE：Excel VBA中。我正在寻找循环浏览切片器选择并打印 PDF。我的代码确实适用于标准透视表，但在 PowerPivot 上使用它时不起作用，这正是我正在寻找的。

谁能详细说明一下如何转动以下内容以使其在 PowerPivot 中工作？(来自电源透视源数据的切片器(

Option Explicit
Sub ExportPDFs()
Dim sI As SlicerItem, sI2 As SlicerItem, sc As SlicerCache
Dim fname$
Set sc = ActiveWorkbook.SlicerCaches("Slicer_Date")
With sc
    For Each sI In sc.SlicerItems
        If sI.HasData = True Then
        sc.ClearManualFilter
        For Each sI2 In sc.SlicerItems
            If sI.Name = sI2.Name Then sI2.Selected = True Else: sI2.Selected = False
        Next
        Debug.Print sI.Name
        fname = sI.Caption & " " & Format(Date, "MM-DD-YYYY") & " " & "Report"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
    Next
    ActiveWorkbook.SlicerCaches("Slicer_Year").ClearManualFilter
    MsgBox "Reports Saved"
End With
End Sub

Answer 1

这适用于 Power Pivot 模型，只需更改切片器名称、文件名规则等即可满足您的需求。

此VBA将遍历您的Power Pivot切片器并将结果打印到 'PDF。 '要使其正常工作，请在VBA下方更改切片器名称和存储位置。

Private Sub PowerPivotLoopSlicerPrintPDF()
Dim SC As SlicerCache
Dim SL As SlicerCacheLevel
Dim SI As SlicerItem
Set SC = ActiveWorkbook.SlicerCaches("Slicer_Kolonne1") 'Add slicer name 
'between " "
Set SL = SC.SlicerCacheLevels(1)
'c(ounter) is set to 1, ready to begin
c = 1

'Repeat the a loop until number of prints exceeds number of items in slicer
Do While c <= SC.SlicerCacheLevels.Item.Count + 1
'This makes sure that SI is the correct slicer. Needed for corrent file 
' name.
    For Each SI In SL.SlicerItems
        If SI.Selected = True Then
        SlicerverdiIndex = c
    Exit For
        End If
    Next SI

    'PRINT CODE
    Dim FName           As String
    Dim FPath           As String
    'Define file path for printed file storage
    FPath = "C:UsersremiaDesktopVBA"   'Choose your filepath
    FName = SI.SourceName
    'Define WHAT to print and how to build file name
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    FPath & "" & FName & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False
    'PRINT CODE FINISHED
'Sets the slicer to the last item in the list
If SlicerverdiIndex = 1 Then
    SlicerverdiIndex = SC.SlicerCacheLevels.Item.Count + 1
End If
SC.VisibleSlicerItemsList = SL.SlicerItems(SlicerverdiIndex - 1).Name
'Adds 1 to the counter, will loop until end of slicer has been reached.
c = c + 1
Loop
End Sub

如果您只想循环/打印切片器，则以下代码适用于 WITH 数据：

'This VBA will loop through your Power Pivot slicer and print the results to PDF.
 'To get it working change slicer name and storage location in below VBA.
Private Sub PrintTimelisterPDF()
Dim sC As SlicerCache
Dim SL As SlicerCacheLevel
Dim sI As SlicerItem
  Set sC = ActiveWorkbook.SlicerCaches("Slicer_OrdreNr") 'Add slicer name between " "
  Set SL = sC.SlicerCacheLevels(1)
  Set sI = SL.SlicerItems(1)    'Sets sliceritem to a start value
  'c(ounter) is set to 1, ready to begin
  c = 1
 'Repeat the loop until the slicer doesnt have any data. "Do while c=1" is to kick it 
  off in the first place
   Do While c = 1 Or sI.HasData = True
 'This makes sure that SI is the correct slicer. Needed for corrent file name.
   For Each sI In SL.SlicerItems
 If sI.Selected = True Then
    SlicerverdiIndex = c
    Exit For
    End If
    Next sI
'Ensure that print only happens when the slicer has data
If sI.HasData = True Then

    'PRINT CODE
    Dim FName           As String
    Dim FPath           As String
    'Define file path for printed file storage
    FPath = "C:Usersremi.ovsteboDocumentsVBA"   'Choose your filepath
    FName = sI.SourceName
    'Define WHAT to print and how to build file name
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    FPath & "" & FName & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False
    'PRINT CODE FINISHED
 End If

  'Select next Value in slicer
    sC.VisibleSlicerItemsList = SL.SlicerItems(SlicerverdiIndex + 1).Name
  'Adds 1 to the counter
    c = c + 1
    Loop
    End Sub