VBA选择每个切片器项目，然后将每个选定的切片器项目另存为pdf? [英] VBA to select each slicer item AND then save each selected slicer item as a pdf?

问题描述

我设计了一个仪表板，该仪表板由许多不同的数据透视表和数据透视表组成.

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

所有这些数据透视表/图表均由1个称为"Slicer_Store"的切片器控制.

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

此切片器中大约有800种不同的商店可供选择.

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

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

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.

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

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

---

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

---

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文件.

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

修改后的代码:

这应该起作用，但是要遍历所有切片器项目(800多个)需要花费很多时间.另外，我需要确保它仅打印第一页(打印区域)，以便切片器本身不会被打印.

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.

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

推荐答案

这实际上解决了问题，但您获得800多个项目的方法将永远需要完成.请参见下面的另一种解决方案，该解决方案需要用户进行一点协作，但它要快得多.

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.

在打印到PDF之前添加以下行:

Add this line before printing to PDF:

 Range("b1") = sI.Name

这会将商店名称写到范围内，以便以后您可以将其用作pdf文件的名称.

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

此外，在路径的末尾添加一个斜杠:

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

更新

在此解决方案中，您需要确保第一个切片器项目并且仅选择了一个(因此，您不应清除手动过滤器).基于此进行编码.原始代码每次都会遍历所有切片器项目，选择一个然后取消选择其他项目，这将导致极高的计算成本.

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

这篇关于VBA选择每个切片器项目，然后将每个选定的切片器项目另存为pdf?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！