Export multiple sheets to PDF per product by looping through slicer

[Helena123]

I have an excel file containing multiple charts showing sales, demand, etc. in 5 sheets. These charts are linked and filtered by a slicer "Product Name". This slicer is located in sheet "Supplier" .
I am trying to export these 5 sheets to PDF per product by having the code to loop through slicer. The error I got is

https://i.stack.imgur.com/OggSj.png


And the code highlighted in yellow when Debug is

SC.VisibleSlicerItemsList = SL.SlicerItems(SlicerverdiIndex + 1).Name

Below is my full code. I have spent weeks to research and try different suggestions on internet but no luck. Could anyone please help?


Sub ExportPDF()Dim SC As SlicerCache
Dim SL As SlicerCacheLevel
Dim SI As SlicerItem
Dim PrintRange As Range
Dim MyRangeArray As Variant
Dim i As Integer
Dim FName As String
Dim FPath As String


Set sC = ActiveWorkbook.SlicerCaches("Slicer_Product_Name2") 'Add slicer name between " "
Set SL = sC.SlicerCacheLevels(1)
Set sI = SL.SlicerItems(1) 'Sets slicer item to a start value


'c(ounter) is set to 1, ready to begin
c = 1


'Repeat the loop until the slicer doesnt have any data. "Do while c=1" is to kick it off in the first place
Do While c = 1 Or sI.HasData = True


'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



'Ensure that print only happens when the slicer has data
If sI.HasData = True Then




'Define file path for printed file storage
FPath = "C:\Users\..."
FName = SI.SourceName


'Define WHAT to print and how to build file name
'List of Excel Ranges to export from'


ThisWorkbook.Sheets(Array("Sales", "Demand", "Supplier", "Inventory", "Distributor")).Select



ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=FPath & "\" & FName & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False


'PRINT CODE FINISHED
End If


'Select next Value in slicer
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

[georgiboy]

Hi Helena,

I had an issue like this before, my solution was not pretty but I will share it anyway, I have amended your code to work on my dummy workbook and added a function for it to use:

Code:

Sub ExportPDF()
Dim sC As SlicerCache
Dim x As Long
Dim FName As String
Dim FPath As String

Set sC = ActiveWorkbook.SlicerCaches("Slicer_Product")

For x = 1 To sC.SlicerItems.Count
SlicerSelect "Slicer_Product", sC.SlicerItems(x).Name
FPath = "C:\Users\..."
FName = sC.SlicerItems(x).Name
ThisWorkbook.Sheets(Array("Sales", "Demand", "Supplier", "Inventory", "Distributor")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=FPath & "\" & FName & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next x

End Sub

Function:

Function SlicerSelect(SlicerName As String, SlicerItem As String)
Dim SC As SlicerCache
Dim SI As SlicerItem
Dim index As Integer

Set SC = ActiveWorkbook.SlicerCaches(SlicerName)
SC.ClearManualFilter
For Each SI In SC.SlicerItems
If SI.Name = SlicerItem Then
SI.Selected = True
Else
SI.Selected = False
End If
Next SI
End Function

Hope this helps or spawns another idea

[Helena123]

Hi georgiboy,


I have just applied your code and got an run-time error 1004: Application-denied or object-defined error

With line
For x = 1 To SC.SlicerItems.Count

[georgiboy]

You may need to incorporate the level you had before:

Set SL = sC.SlicerCacheLevels(1)
and play with it, from my understanding the way you work with slicers depends on the way the data is created in the first place.

You may be able to include the function provided within your own code.

Hope you find a solution soon

[Paul_Hossler]

I don't think you want to use VisibleSlicerItemsList

https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.slicercache.visiblesliceritemslist?view=exce l-pia


The VisibleSlicerItemsList (https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.slicercache.visiblesliceritemslist?view=exce l-pia#Microsoft_Office_Interop_Excel_SlicerCache_VisibleSlicerItemsList) property is only applicable for slicers that are based on OLAP data sources (SlicerCache (https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.slicercache?view=excel-pia).OLAP (https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.slicercache.olap?view=excel-pia#Microsoft_Office_Interop_Excel_SlicerCache_OLAP) = true).


Try integrating something like this



Option Explicit


Sub test()
Dim oSlicerCache As SlicerCache
Dim oSlicer As Slicer
Dim oSlicerItem As SlicerItem

Set oSlicerCache = ActiveWorkbook.SlicerCaches("Slicer_AAA")
Set oSlicer = oSlicerCache.Slicers("AAA")

oSlicerCache.ClearManualFilter


For Each oSlicerItem In oSlicer.SlicerCache.SlicerItems
With oSlicerItem
If .Selected Then
MsgBox .Value
End If
End With
Next


End Sub

[Helena123]

I have applied that and it works for me, thank you.

However, now the chart format keep changing, some products still have custom format when exported to PDF, some turns back to default format. And it does happen when I manually select item in the slicer.