excel - 如何根据所选的切片器编辑 VBA 脚本以保存到特定文件夹

Question

我开发了一个仪表板，它基于 2 个切片器进行更改：部门和员工。

我的最终目标是附加代码在切片器中的选定部门上运行，第二个切片器将选择所有员工，然后代码将遍历每个员工并打印到 PDF 以将每个员工保存在他们的透视部门中文件夹。

该文件夹将以部门名称作为文件夹名称，因此我正在考虑某种 = 运算符。我从另一个用户那里提取了这段代码。

*The below VBA code goes through and selects all and places it in the folder. Is there a way to have 
 the PDF file save depending department or the first slicer. 
 
Private Sub PowerPivotLoopSlicerSimplePrintoPDF()
Dim SC As SlicerCache
Dim SL As SlicerCacheLevel
Dim SI As SlicerItem
Set SC = ActiveWorkbook.SlicerCaches("Slicer_Employee") '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:\prchica\Desktop\Scorecard\PDF files"   '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:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _
False, From:=1, To:=1
'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*

标签： excelvbaautomationslicers

Answer 1

我需要将此添加到我的代码中：

Dim SC As SlicerCache

Set SC = ActiveWorkbook.SlicerCaches("Slicer_Department") 'Add slicer name between " "
Call ParseAndPrintSlicer(SC)

Set SC = ActiveWorkbook.SlicerCaches("Slicer_Emplid") 'Add slicer name between " "
Call ParseAndPrintSlicer(SC)