VBA* Loop 2 Slicers and Convert them to PDF/Excel in Custom Folder

[Armygeddan]

Final product :

1. Loop both Slicers (Slicer_Region, Slicer_Customer)
2. Save each Slicer loop as a PDF and Excel file
3. Only save certain Tabs in the whole file when exporting to PDF and Excel (Array)
4. Have the File Save Format as YYYY_MM(Previous Month)_FilePath(NamedRange).Pdf/Excel
5. Create a Year and a Month Folder in the File Save Destination if it doesn’t exist

I’ve got #3, #4, and #5 completed but need help with #1 and #2

When running the Loop and Converting the Slicer Items to PDF and Excel, it needs to save each selection in a certain name save format of YYYY_MM_CELLA_CELLB

For Region, I’ve got
“Middle East/Africa” as “MEA” “Asia/Pacific” as “AP” Single names like Canada are standalone as themselves When running the Loop, Asia/Pacific would need to be converting to the format of AP(Slicer_Customer)
So per Save, it will need to look like the following
<CODE> Save 1
– CELLA_CELL1
Save 2
– CELLA_CELL2 Etc
Save 16
– CELLB_CELL1
Save 17
– CELLB_CELL2

</CODE>I’d also like an initial save of both the Slicer Dashboards with nothing selected on the Slicers and name it as “ALL_ALL”
That’s a lot of loops to save but sure beats doing it manually.
Here is how my Slicer looks like (Edited out confidential information)

I’ve only been working with VBA for a couple of months so all of the above is quite a bit advanced for me but would give me a huge learning curve for future projects like this. I’ve got the Basics of VBA down but working with Slicers has thrown me in for a Loop (Pun Intended)

Please note that all my data was initially pulled with OLAP but it is all saved in a PowerPivot Data Model so there should be no worries of external sources when gathering data from external sources when the Slicers are selected.

Thank you so much in advance, I know this is asking a lot.
Here is what I have so far:

(Please Note I am current getting an error on Line 20) Says “For Each Slice In MySlicer.SlicerItem” And the error says “Run-time error '1004' Application-defined or object-defined error”

Sub LoopSlicer()
Dim strGenericFilePath     As String: strGenericFilePath = Range("FilePath")
Dim strYearSlash           As String: strYearSlash = Year(Date) & "\"
Dim strMonthSlash          As String: strMonthSlash = CStr(Format(DateAdd("M", -1, Date), "MM")) & "\"
Dim strYearBracket         As String: strYearBracket = Year(Date) & "_"
Dim strMonthBracket        As String: strMonthBracket = CStr(Format(DateAdd("M", -1, Date), "MM")) & "_" & "Smart_Data_"
Dim strFileName            As String: strFileName = "Standard_Customer"
Dim IntSliceCount          As Integer
Dim IntLoop                As Integer
Dim SliceLoop              As Integer
Dim Slice                  As SlicerItem
Dim MySlicer               As SlicerCache
Set MySlicer = ActiveWorkbook.SlicerCaches("Slicer_Region")
    IntSliceCount = 0
    ' Count slicer options
    For Each Slice In MySlicer.SlicerItem
        IntSliceCount = IntSliceCount + 1
    Next Slice
    ' NOTE:--------------------------------------------------------------------------
    ' When selecting a slicer, all the other slicers in the field must be de-selected
    ' -------------------------------------------------------------------------------
    ' Activate slicers one by one and print
    With MySlicer
        For IntLoop = 1 To IntSliceCount
        ' Activate the current slicer in loop, deactivate rest
            For SliceLoop = 1 To IntSliceCount
                If IntLoop = SliceLoop Then
                    .SlicerItems(IntLoop).Selected = True
                Else
                    .SlicerItems(SliceLoop).Selected = False
                End If
            Next SliceLoop
        Application.DisplayAlerts = False
        ' Check for the Year Folder and create it if it does not exist
        If Len(Dir(strGenericFilePath & strYearSlash, vbDirectory)) = 0 Then
            MkDir strGenericFilePath & strYearSlash
        End If
        
        ' Check for the Month Folder and create it if it does not exist
        If Len(Dir(strGenericFilePath & strYearSlash & strMonthSlash, vbDirectory)) = 0 Then
            MkDir strGenericFilePath & strYearSlash & strMonthSlash
        End If
        On Error Resume Next
        On Error GoTo 0
        
        ' Converts to PDF and saves with YYYY_MM_ Format + Filename based off of FilePath String
        ThisWorkbook.Sheets(Array("TestSheet1", "TestSheet2", "TestSheet3")).Select
        ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:= _
        strGenericFilePath & strYearSlash & strMonthSlash & strYearBracket & strMonthBracket & strFileName, _
        Quality:=QualityStandard, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        Application.DisplayAlerts = True
        ' Popup Message that the conversion and save is complete as YYYY_MM_Standard_Customer
        MsgBox "File Saved As:" & vbNewLine & "\" & strYearBracket & strMonthBracket & strFileName
        ' De-activate the current slicer in loop, activate the rest
        For SliceLoop = 1 To IntSliceCount
            If IntLoop = SliceLoop Then
                .SlicerItems(IntLoop).Selected = False
            Else
                .SlicerItems(SliceLoop).Selected = True
            End If
        Next SliceLoop
    Next IntLoop
End With
End Sub

 

[Armygeddan]

I'm getting an error when trying to run this.

ActiveWorkbook.SlicerCaches("Slicer_Region").VisibleSlicerItemsList = Array(v)

Says "Run-time error '-2147418113 (8000ffff)':
Method 'VisibleSlicerItemsList' of object 'SlicerCache' failed

I did copy and paste your Regions into column B of the main tab out of sight. It wouldn't pdf those as well when this code works correct?

And you are tracking that there are 2 Slicers correct? I only see in your code "Slicer_Region" when there is also a "Slicer_Customer" in play so I apologize if the other portions of the code cover that but I can't read it.

 

[Worf]

- Let’s test with one slicer, when everything is working we can add the second one.
- What do you mean by out of sight? A hidden column is not a problem, but with a hidden sheet the code needs amending.
- Did you keep the message box? It will show the list, which should be similar to the ones on post #23.
- The last example exports the active sheet. Later we can hide sheets and export the workbook like on post #23.
- The code at post #27 worked, is the only difference the out of sight data?