Save and Load Excel Slicer Cache

Question

I came across a simple piece of VBA to inspect slicer cache all active Slicers within an Excel Workbook.

Sub RetrieveSlicers()
Dim caches As Excel.SlicerCaches
Set caches = ActiveWorkbook.SlicerCaches
End Sub

By placing a break-point next to End Sub, right clicking on cache and selecting 'Add Watch...'

(See below)

You can view all of the items within every active slicer via the 'Watches' window.

My question is, can I save slicer cache information (specifically SlicerItems) for later (maybe as a text array?), and then load this saved slicer cache back into the slicers (re-populate slicers with saved SlicerItems)?

Example below:

I'm sure as easy as it is to get Slicer Cache data, it'll be just as easy to set Slicer Cache data.

Any help as always is much appreciated.

Mr. J

Answer 1

Something like this should work (I don't have anything to test at the moment) :

Sub Save_Slicers()
Dim SliCaches As Excel.SlicerCaches
Dim SliCache As Excel.SlicerCache
Dim SliCName As String
Dim sliIt As Excel.SlicerItem
Dim A()
ReDim A(1 To 3, 1 To 1)

A(1, 1) = "Slicer Cache Name"
A(2, 1) = "Slicer Item Name"
A(3, 1) = "Selected"
ReDim Preserve A(LBound(A, 1) To UBound(A, 1), LBound(A, 2) To UBound(A, 2) + 1)

Set SliCaches = ActiveWorkbook.SlicerCaches
For Each SliCache In SliCaches
    SliCName = SliCache.Name
    For Each sliIt In SliCache.SlicerItems
        A(1, UBound(A, 2)) = SliCName
        A(2, UBound(A, 2)) = sliIt.Name
        A(3, UBound(A, 2)) = sliIt.Selected
        ReDim Preserve A(LBound(A, 1) To UBound(A, 1), LBound(A, 2) To UBound(A, 2) + 1)
    Next sliIt
Next SliCache
ReDim Preserve A(LBound(A, 1) To UBound(A, 1), LBound(A, 2) To UBound(A, 2) - 1)

'Print it in a sheet
Sheets("Sheet1").Range("A1").Resize(UBound(A, 2), UBound(A, 1)).Value = Application.Transpose(A)
End Sub

Same one with a manual slicer cache selector :

Sub Save_Selected_Slicers()
Dim SliCaches As Excel.SlicerCaches
Dim SliCache As Excel.SlicerCache
Dim SliCName As String
Dim sliIt As Excel.SlicerItem
Dim SaveSlice As Single
Dim A()
ReDim A(1 To 3, 1 To 1)

A(1, 1) = "Slicer Cache Name"
A(2, 1) = "Slicer Item Name"
A(3, 1) = "Selected"
ReDim Preserve A(LBound(A, 1) To UBound(A, 1), LBound(A, 2) To UBound(A, 2) + 1)

Set SliCaches = ActiveWorkbook.SlicerCaches
For Each SliCache In SliCaches
    SliCName = SliCache.Name
    SaveSlice = MsgBox("Do you want to save " & SliCName & " ?", vbYesNo, "Save slicers")
    If SaveSlice <> vbYes Then
    Else
        For Each sliIt In SliCache.SlicerItems
            A(1, UBound(A, 2)) = SliCName
            A(2, UBound(A, 2)) = sliIt.Name
            A(3, UBound(A, 2)) = sliIt.Selected
            ReDim Preserve A(LBound(A, 1) To UBound(A, 1), LBound(A, 2) To UBound(A, 2) + 1)
        Next sliIt
    End If
Next SliCache
ReDim Preserve A(LBound(A, 1) To UBound(A, 1), LBound(A, 2) To UBound(A, 2) - 1)
'Print it in a sheet
Sheets("Sheet1").Range("A1").Resize(UBound(A, 2), UBound(A, 1)).Value = Application.Transpose(A)
End Sub

And load :

Sub Load_Slicers()
Dim SliCaches As Excel.SlicerCaches
Dim SliCache As Excel.SlicerCache
Dim sliIt As Excel.SlicerItem
Dim i As Double
Dim A()
'Load the array you printed
A = Sheets("Sheet1").Range("A1", Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp)).Value


Set SliCaches = ActiveWorkbook.SlicerCaches
For i = LBound(A, 1) To UBound(A, 1)
    For Each SliCache In SliCaches
        If SliCache.Name <> A(i, 1) Then
        Else
            For Each sliIt In SliCache.SlicerItems
                If sliIt.Name <> A(i, 2) Then
                Else
                    sliIt.Selected = A(i, 3)
                End If
            Next sliIt
        End If
    Next SliCache
Next i
End Sub