vba VBA选择每个切片器项目然后将每个选定的切片器项目保存为pdf？

VBA to select each slicer item AND then save each selected slicer item as a pdf?

提问by ranopano

I've designed a dashboard consisting of a number of different pivot tables and pivot charts.

我设计了一个仪表板，其中包含许多不同的数据透视表和数据透视图。

All of these pivot tables/charts are controlled by 1 slicer called "Slicer_Store".

所有这些数据透视表/图表都由 1 个名为“Slicer_Store”的切片器控制。

There are about 800 different Stores to choose from in this slicer.

在这个切片器中有大约 800 个不同的商店可供选择。

I need to save a pdf of EVERY store's dashboard. The process of manually selecting each slicer item, then saving the sheet as a pdf file, is extremely time consuming with 800+ stores, so I was hoping to automate the process via VBA.

我需要保存每个商店仪表板的pdf。手动选择每个切片器项目，然后将工作表另存为 pdf 文件的过程对于 800 多家商店来说非常耗时，因此我希望通过 VBA 使该过程自动化。

Here's my code so far:

到目前为止，这是我的代码：

Public Sub myMacro()
Dim sI As SlicerItem, sI2 As SlicerItem, sC As SlicerCache
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Store")
With sC

    For Each sI In sC.SlicerItems
        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
        'add export to PDF code here
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\TestUser\Desktop\testfolder" & Range("b1").Text  & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Next

End With
End Sub

---

---

The code does process all though slicer items, but the file is not being saved as a pdf. I need each file to be saved as the value in B2, so it would be Store1.pdf, Store2.pdf, Store3.pdf, etc.

该代码确实处理了所有切片器项目，但该文件并未保存为 pdf。我需要将每个文件保存为 B2 中的值，因此它将是 Store1.pdf、Store2.pdf、Store3.pdf 等。

Any help would be hugely appreciated. This is a big project at work and a lot of people are dependent on these pdf files..

任何帮助将不胜感激。这是一个大项目，很多人都依赖这些 pdf 文件。

---

---

Edited code:

编辑的代码：

This should work, but it takes forever to go over all of the slicer items (800+). Also, I need to make sure that it only prints the first page (print area) so the slicer itself won't be printed.

这应该可以工作，但需要永远浏览所有切片器项目（800+）。另外，我需要确保它只打印第一页（打印区域），这样切片器本身就不会被打印。

Public Sub myMacro()
Dim sI As SlicerItem, sI2 As SlicerItem, sC As SlicerCache
Dim ws As Worksheet
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Store_Number")
Set ws = Sheet18
With sC

    For Each sI In sC.SlicerItems
        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
        'add export to PDF code here
      ws.PageSetup.PrintArea = ws.Range("A1:N34").Address

       ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\testuser\Desktop\testfolder" & Range("M1").Text & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Next

End With
End Sub

采纳答案by M--

This actually resolve the issue but the approach you get towards 800+ item would take forever to be completed. See below for another solution which needs a little bit of collaboration from the user but it is much faster.

这实际上解决了问题，但是您对 800 多个项目的处理需要永远完成。请参阅下面的另一种解决方案，它需要用户的一些协作，但速度要快得多。

Add this line before printing to PDF:

在打印为 PDF 之前添加此行：

 Range("b1") = sI.Name

This will write name of the store to the range so later you can use it as the name of your pdf file.

这会将商店的名称写入范围，以便稍后您可以将其用作 pdf 文件的名称。

Also, add a slash to the end of your path:

另外，在路径末尾添加斜杠：

 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
          "C:\Users\TestUser\Desktop\testfolder\" & Range("b1").Text  & ".pdf", Quality:= _

IF you want to only print first page, you can set the print area right before above lines or use this:

如果你只想打印第一页，你可以在上面几行之前设置打印区域或者使用这个：

ActiveSheet.PrintOut from:=1, To:=1

UPDATE

更新

In this solution you need to make sure that first slicer item, and only that one is selected (So you should not clear manual filter). This is coded based on that. The original code goes over all of the slicer items each time, select one and deselect the others which causes an extremely high computational cost.

在这个解决方案中，您需要确保第一个切片器项目，并且只选择了那个（所以你不应该清除手动过滤器）。这是基于此编码的。原始代码每次都会遍历所有切片器项，选择一个并取消选择其他项，这会导致极高的计算成本。

Public Sub myMacro()
Dim sC As SlicerCache
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Store_Number")




  'This reminds the user to only select the first slicer item
   If sC.VisibleSlicerItems.Count <> 1 Or sC.SlicerItems(1).Selected = False Then
      MsgBox "Please Only Select Store-Number 1"
      Exit Sub
   End If


For i = 1 To sC.SlicerItems.Count

    'Do not clear ilter as it causes to select all of the items (sC.ClearManualFilter)

    sC.SlicerItems(i).Selected = True
    If i <> 1 Then sC.SlicerItems(i - 1).Selected = False


    'Debug.Print sI.Name
    'add export to PDF code here
    With Sheet18.PageSetup

    .PrintArea = Sheet18.Range("A1:N34" & lastRow).Address

    .FitToPagesWide = 1
    .FitToPagesTall = 1

    End With

    Sheet18.Range("M1") = sC.SlicerItems(i).Name

   'This prints to C directory, change the path as you wish

   Sheet18.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\" & Range("M1").Text & ".pdf", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
Next

End Sub

回答by William Xia

Sub FacultyToPDF()

Dim wb As String
Dim sh As Worksheet
Dim fname As String
Dim location As String
Dim sI As SlicerItem, sI2 As SlicerItem, sC As SlicerCache
Dim index As Integer
Const PrintRange = "Print_Area"    

fPath = "C:\Users\xiaz01\Desktop\Special Project\PDF"
Set sC = ActiveWorkbook.SlicerCaches("Slicer_billing_phys_name")    


For Each sI In ActiveWorkbook.SlicerCaches("Slicer_billing_phys_name").SlicerCacheLevels(1).SlicerItems
    ActiveWorkbook.SlicerCaches("Slicer_billing_phys_name").VisibleSlicerItemsList = Array(sI.Name)
    fname = Range("B1").Text & Format(Date, " yy-mm-dd") & ".pdf"
    Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fname
Next    

End Sub