循环切片器并选择值 [英] Loop through pivot slicer and select values

问题描述

我有一个枢轴切片器，其中包含一个链接到数据透视表的网站列表。我想循环切片机来实现以下功能：

选择一个网站

显示相关值

导出数据到PDF文件

选择下一个网站等

我有将数据导出为PDF文件的代码，但我是努力通过切片机循环并在每个站点上过滤。目前我有以下几点：

  Sub ExportPDFs（）
 
 Dim sI As SlicerItem 
 
对于ActiveWorkbook.SlicerCaches（Slicer_site）中的每个sI SlicerItems 
 
 Debug.Print sI.Name 
 Debug.Print范围（A3）
 
'ChDirQ：\PROMs\Data Completeness\PDFs
 
'ActiveSheet.ExportAsFixedFormat类型：= xlTypePDF，文件名：= _ 
'问： \PROMs\Data Completeness\PDFs\PROMS数据完整性 - & sI.Name& .pdf，_ 
'质量：= xlQualityStandard，IncludeDocProperties：= True，IgnorePrintAreas _ 
'：= False，OpenAfterPublish：= False 
 
下一个
 
 End Sub 
  

我添加了debug.print行（单元格A3包含我的工作表上的站点名称，所以当我选择一个不同的切片器项目时，这种情况会发生变化）来测试它，它循环遍历每个切片器项目，但不更改数据，因此调试窗口显示以下内容：

 网站1 
网站1 
网站2 
网站1 
网站3 
网站1 
网站4 
网站1 
  

如何让它每次过滤数据循环通过？

解决方案

您需要主动告诉VBA选择特定的切片器项目并取消选择其余部分。可能还有一个更有效的方法来做到这一点，但是我刚刚创建并测试了以下内容：

  Dim sI作为SlicerItem，sI2 As SlicerItem，sC As SlicerCache 
 
设置sC = ActiveWorkbook.SlicerCaches（Slicer_site）
 
与sC 
 
对于每个sI在sC.SlicerItems 
 
 sC.ClearManualFilter 
 
对于每个sI2在sC.SlicerItems 
如果sI.Name = sI2.Name然后sI2.Selected = True否则： sI2.Selected = False 
下一个
 
 Debug.Print sI.Name 
'将导出添加到PDF代码
 
下一个
 
结束
  

I have a pivot slicer with a list of sites that are linked to a PivotTable. I want to loop through the slicer to achieve the following:

selects a site
displays the relevant values
exports the data to a PDF file
select next site etc

I've got the code that exports the data to a PDF file working but I'm struggling to loop through the slicer and filter on each site. At the moment I have the following:

Sub ExportPDFs()

Dim sI As SlicerItem

For Each sI In ActiveWorkbook.SlicerCaches("Slicer_site").SlicerItems

    Debug.Print sI.Name
    Debug.Print Range("A3")

    '    ChDir "Q:\PROMs\Data Completeness\PDFs"

    '    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    '        "Q:\PROMs\Data Completeness\PDFs\PROMS data completeness - " & sI.Name & ".pdf", _
    '        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
     '        :=False, OpenAfterPublish:=False

Next

End Sub

I've added the debug.print lines (cell "A3" contains the site name on my worksheet, so this changes when I select a different slicer item) to test it, it loops through each slicer item but doesn't change the data so the debug window shows the following:

Site 1
Site 1
Site 2
Site 1
Site 3
Site 1
Site 4
Site 1

How do I make it filter the data each time it loops through?

解决方案

You need to actively tell VBA to select the specific slicer item and deselect the rest. There may be an even more efficient way to do this, but I just created and tested the following and it worked:

Dim sI As SlicerItem, sI2 As SlicerItem, sC As SlicerCache

Set sC = ActiveWorkbook.SlicerCaches("Slicer_site")

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

    Next

End With

这篇关于循环切片器并选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！