VBA loop through slicer and export multiple sheets to PDF

[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 Run-time error '5': Invalid procedure call or argument

And the code highlighted in yellow when Debug is

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

Below is my full code. Any help is hugely appreciated!

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

 

[John_w]

I can't download it, only edit it because it looks like you've uploaded it to Google Docs, where it becomes a Google Sheets file. If you upload it to 'My Drive' and share the link I should be able to download it.

 

[Helena123]

Sample.xlsm

Sorry for that. Could you please try again, not sure how the setting should be but this time I have it as "Viewers can download" instead of "Editor" as before.

 

[John_w]

I have now downloaded the Excel workbook, however it contains queries and connections to other files in C:\Sales\, which I haven't got, so I can't test the macro properly. However, try deleting this code:

            'Select the next slicer item to update the chart sheets with that item
            
            For Each slMatch In SL.SlicerItems
                If slItem.Name = slMatch.Name Then slMatch.Selected = True Else: slMatch.Selected = False
            Next

 

[Helena123]

The sample file has the folder "Sales" containing 2 files as data source. Please download this folder from this link Sales - Google Drive

You might change the location path in the sample file accordingly.


I have just deleted these lines, and the issue now is all the PDFs show the same information, the slicer did not work somehow.

But good news is it was able to export to PDFs per product name.

Thanks again!

 

[John_w]

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.

I've got your sample workbook with the .csv files in the 'Sales' folder working now and written a macro to create the PDFs. It took me a lot of investigation to find the solution because of the way your workbook references the .csv files as external data sources, which is completely new to me.

On each of the 3 Sales sheets in the sample workbook, there is a PivotChart chart which is linked to the 'Product Name' slicer on the 'Slicer' sheet.

I think the PivotCharts were created using Power Pivot, which uses Power Query to query and read data from external data sources, in this case the 'Sales E1.csv' and 'Sales E2.csv' files in the 'C:\Sales' folder. Therefore, there is no data, nor PivotTables, in the workbook. Instead, the Power Pivot creates a Data Model, which is a virtual representation of the external data as relational tables.

With this type of Slicer and PivotCharts the usual method of selecting each slicer item in turn by setting its Selected property to True, using:

    For Each slItem In slCache.SlicerItems
        
        'Show all items
        
        slCache.ClearManualFilter
        
        If slItem.HasData Then
                        
            'Select the next slicer item to update the chart sheets with that item
            
            For Each slMatch In slCache.SlicerItems
                If slItem.Name = slMatch.Name Then slMatch.Selected = True Else: slMatch.Selected = False
            Next

        End If
        
    Next

doesn't work because the slMatch.Selected = True causes the following error:

Run-time error '1004':
Application-defined or object-defined error

Instead, we have to assign an array to the SlicerCache's VisibleSlicerItemsList property. This array is a String array of the name(s) of the Slicer items you want to select. By putting each SlicerItem.Name in the array in turn, only that single SlicerItem is selected, and the PivotCharts in the 3 Sales sheets are updated to reflect the selected SlicerItem, i.e. the Product Name.

Here is the macro, which also includes extra code to save and restore the currently selected item(s) in the 'Product Name' slicer.

Public Sub Create_PDFs_From_Slicer_Items()

    Dim PDFsFolder As String
    Dim currentSheet As Worksheet
    Dim slCache As SlicerCache
    Dim slItem As SlicerItem
    Dim saveSelectedSlicerItems() As String, numSelected As Long
    Dim selectedSlicerItem(1 To 1) As String
   
    'Folder path for output PDFs
    
    PDFsFolder = "C:\Sales\"
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

    'Name of slicer: right-click Slicer -> Slicer Settings -> Name to use in formulas
    
    Set slCache = ThisWorkbook.SlicerCaches("Slicer_Product_Name")

    Set currentSheet = ActiveSheet
   
    Application.ScreenUpdating = False
    
    'Save currently selected slicer item(s)
    
    numSelected = 0
    For Each slItem In slCache.SlicerCacheLevels(1).SlicerItems
        If slItem.Selected Then
            numSelected = numSelected + 1
            ReDim Preserve saveSelectedSlicerItems(1 To numSelected)
            saveSelectedSlicerItems(numSelected) = slItem.Name
        End If
    Next
    
    'Select each slicer item and save 3 sheets as a PDF file named as the slicer value
    
    For Each slItem In slCache.SlicerCacheLevels(1).SlicerItems
    
        If slItem.Value <> "(blank)" Then
        
            selectedSlicerItem(1) = slItem.Name
            slCache.VisibleSlicerItemsList = selectedSlicerItem
            
            ThisWorkbook.Sheets(Array("Sales", "Sales (2)", "Sales (3)")).Select
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                            Filename:=PDFsFolder & slItem.Value & ".pdf", _
                                            Quality:=xlQualityStandard, _
                                            IncludeDocProperties:=True, _
                                            IgnorePrintAreas:=False, _
                                            OpenAfterPublish:=False
            
            currentSheet.Select
        
        End If
        
    Next

    'Restore currently selected slicer item(s)
    
    If numSelected > 0 Then slCache.VisibleSlicerItemsList = saveSelectedSlicerItems

    Application.ScreenUpdating = False
    
    MsgBox "Finished"

End Sub

 

[Helena123]

I've got your sample workbook with the .csv files in the 'Sales' folder working now and written a macro to create the PDFs. It took me a lot of investigation to find the solution because of the way your workbook references the .csv files as external data sources, which is completely new to me.

On each of the 3 Sales sheets in the sample workbook, there is a PivotChart chart which is linked to the 'Product Name' slicer on the 'Slicer' sheet.

I think the PivotCharts were created using Power Pivot, which uses Power Query to query and read data from external data sources, in this case the 'Sales E1.csv' and 'Sales E2.csv' files in the 'C:\Sales' folder. Therefore, there is no data, nor PivotTables, in the workbook. Instead, the Power Pivot creates a Data Model, which is a virtual representation of the external data as relational tables.

With this type of Slicer and PivotCharts the usual method of selecting each slicer item in turn by setting its Selected property to True, using:

    For Each slItem In slCache.SlicerItems
       
        'Show all items
       
        slCache.ClearManualFilter
       
        If slItem.HasData Then
                       
            'Select the next slicer item to update the chart sheets with that item
           
            For Each slMatch In slCache.SlicerItems
                If slItem.Name = slMatch.Name Then slMatch.Selected = True Else: slMatch.Selected = False
            Next

        End If
       
    Next

doesn't work because the slMatch.Selected = True causes the following error:



Instead, we have to assign an array to the SlicerCache's VisibleSlicerItemsList property. This array is a String array of the name(s) of the Slicer items you want to select. By putting each SlicerItem.Name in the array in turn, only that single SlicerItem is selected, and the PivotCharts in the 3 Sales sheets are updated to reflect the selected SlicerItem, i.e. the Product Name.

Here is the macro, which also includes extra code to save and restore the currently selected item(s) in the 'Product Name' slicer.

Public Sub Create_PDFs_From_Slicer_Items()

    Dim PDFsFolder As String
    Dim currentSheet As Worksheet
    Dim slCache As SlicerCache
    Dim slItem As SlicerItem
    Dim saveSelectedSlicerItems() As String, numSelected As Long
    Dim selectedSlicerItem(1 To 1) As String
  
    'Folder path for output PDFs
   
    PDFsFolder = "C:\Sales\"
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

    'Name of slicer: right-click Slicer -> Slicer Settings -> Name to use in formulas
   
    Set slCache = ThisWorkbook.SlicerCaches("Slicer_Product_Name")

    Set currentSheet = ActiveSheet
  
    Application.ScreenUpdating = False
   
    'Save currently selected slicer item(s)
   
    numSelected = 0
    For Each slItem In slCache.SlicerCacheLevels(1).SlicerItems
        If slItem.Selected Then
            numSelected = numSelected + 1
            ReDim Preserve saveSelectedSlicerItems(1 To numSelected)
            saveSelectedSlicerItems(numSelected) = slItem.Name
        End If
    Next
   
    'Select each slicer item and save 3 sheets as a PDF file named as the slicer value
   
    For Each slItem In slCache.SlicerCacheLevels(1).SlicerItems
   
        If slItem.Value <> "(blank)" Then
       
            selectedSlicerItem(1) = slItem.Name
            slCache.VisibleSlicerItemsList = selectedSlicerItem
           
            ThisWorkbook.Sheets(Array("Sales", "Sales (2)", "Sales (3)")).Select
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                            Filename:=PDFsFolder & slItem.Value & ".pdf", _
                                            Quality:=xlQualityStandard, _
                                            IncludeDocProperties:=True, _
                                            IgnorePrintAreas:=False, _
                                            OpenAfterPublish:=False
           
            currentSheet.Select
       
        End If
       
    Next

    'Restore currently selected slicer item(s)
   
    If numSelected > 0 Then slCache.VisibleSlicerItemsList = saveSelectedSlicerItems

    Application.ScreenUpdating = False
   
    MsgBox "Finished"

End Sub

Thanks a million for your great help. In terms of exporting to PDF per item in the slicer, it works perfectly now.

However, some of the pivot charts which have 2 data series formated in combo type (custom type) becomes column type. It looks like the format turns back to default formatting. If possible, could you advise if a VBA in a new module is needed or some lines of code can be placed somewhere in the current module?

Thank you!

 

[John_w]

However, some of the pivot charts which have 2 data series formated in combo type (custom type) becomes column type. It looks like the format turns back to default formatting. If possible, could you advise if a VBA in a new module is needed or some lines of code can be placed somewhere in the current module?

I think it's best to start a new thread because this seems to be a different issue. You may need to provide a sample workbook showing the exact issue and maybe someone can help.

 

[Helena123]

I think it's best to start a new thread because this seems to be a different issue. You may need to provide a sample workbook showing the exact issue and maybe someone can help.

Sure, thanks again for your help on this thread.