循环切片器并选择值

本文介绍了循环切片器并选择值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有一个枢轴切片器，其中包含一个链接到数据透视表的网站列表。我想循环切片机来实现以下功能：

选择一个网站 显示相关值 导出数据到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