Processing
 

Strange error during addition of slicer cache

29/08/2016 21:54#1

Joined at: 10 months ago

Post: 7

Thank: 0

Thanked: 0

I'm developing an application with multiple pivot tables and slicers.

I try to prepare a template sheet and copy - paste it in order to create multiple analysis.

When I duplicate the sheet, the Slicers will be linked to both original and new pivot tables (belonging to the same SlicerCache), so I need to:

  • Unlink original SlicerCache from the new pivot table
  • Delete original Slicer from the new sheet
  • create new SlicerCache with the same connection settings
  • create new Slicer on the new sheet, belonging to the new SlicerCache

My code so far:

Function DuplicateSlicer(PreviousSlicer As Slicer) As Slicer     Dim NewSlC As SlicerCache     Dim NewSlicer As Slicer     Dim DestWorkSheet As Worksheet     Dim SlCSequence As Integer     Dim NewSlCName As String      With PreviousSlicer         Set DestWorkSheet = .Parent         .SlicerCache.PivotTables.RemovePivotTable (DestWorkSheet.PivotTables(1))         SlCSequence = 1         Do Until GetSlicerCache(DestWorkSheet.Parent, .SlicerCache.Name & SlCSequence) Is Nothing             SlCSequence = SlCSequence + 1         Loop         NewSlCName = .SlicerCache.Name & SlCSequence         Set NewSlC = DestWorkSheet.Parent.SlicerCaches.Add2(DestWorkSheet.PivotTables(1), _             .SlicerCache.SourceName, .SlicerCache.Name & SlCSequence)         Set NewSlicer = NewSlC.Slicers.Add(DestWorkSheet, Caption:=.SlicerCache.SourceName, _             Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height)          NewSlicer.SlicerCache.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData          .Delete     End With  End Function 

My problem is with the line
DestWorkSheet.Parent.SlicerCaches.Add2(DestWorkSheet.PivotTables(1), _ .SlicerCache.SourceName, .SlicerCache.Name & SlCSequence)

According to MSDN help it should work even without specifying name:

The name Excel uses to reference the slicer cache (the value of the SlicerCache.Name property). If omitted, Excel will generate a name. By default, Excel concatenates "Slicer_" with the value of the PivotField.Caption property for slicers with non-OLAP data sources, ... (Replacing any spaces with "_".) If required to make the name unique in the workbook namespace, Excel adds an integer to the end of the generated name. If you specify a name that already exists in the workbook namespace, the Add method will fail.

However even if I use my code as above, or I just omit 3rd parameter, I keep getting

error 1004: The slicer cache already exists.

To make things even more complicated, if I use a variable for name parameter of Slicercaches.Add (NewSlCName = .SlicerCache.Name & SlCSequence) I get different one:

error: 5 "Invalid procedure call or argument"

Strange error during addition of slicer cache

I really don't have any idea how to fix it.

Update

I've used SlicerCaches.Add2 as that's available from the object tips.
According to another article .Add is deprecated and shouldn't be used.
I've also tried .Add instead of .Add2, it gives the same error.

30/08/2016 12:18Top#2

Joined at: 10 months ago

Post: 7

Thank: 0

Thanked: 0

So far the only approach I could make to work is this:

Create two templates with the same layout and pivot tables, one of them with slicers and the other is without.

To create a new sheet: duplicate the template without slicers, then run below code for creating the slicers in the new sheet:

Sub DuplicateSlicers(NewWorkSheet As Worksheet, SourceWorkSheet As Worksheet)     Dim SlC As SlicerCache     Dim sl As Slicer      For Each SlC In SourceWorkSheet.Parent.SlicerCaches         For Each sl In SlC.Slicers             If (sl.Parent Is SourceWorkSheet) Then                 Call DuplicateSlicer(sl, NewWorkSheet)             End If         Next sl     Next SlC End Sub  Function DuplicateSlicer(PreviousSlicer As Slicer, NewSheet As Worksheet) As Slicer     Dim NewSlC As SlicerCache     Dim NewSlicer As Slicer      If PreviousSlicer Is Nothing Then         Set DuplicateSlicer = Nothing         Exit Function     End If      On Error GoTo ErrLabel      With PreviousSlicer         Set NewSlC = NewSheet.Parent.SlicerCaches.Add2(NewSheet.PivotTables(1), _             .SlicerCache.SourceName)         Set NewSlicer = NewSlC.Slicers.Add(NewSheet, Caption:=.Caption, Top:=.Top, Left:=.Left, _             Width:=.Width, Height:=.Height)     End With     NewSlicer.SlicerCache.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData     Set DuplicateSlicer = NewSlicer      Exit Function  ErrLabel:     Debug.Print PreviousSlicer.Caption & " - " & Err.Number & ": " & Err.Description     Err.Clear  End Function 
Similar articles

Excel More conditions in one formula?

4 months ago - Reply: 2 - Views: 171

Get Workbook reference from Open File dialog

4 months ago - Reply: 2 - Views: 128

Add Calculated Field To Pivot Table

4 months ago - Reply: 0 - Views: 5

Nested IF Functions in Excel not working

4 months ago - Reply: 3 - Views: 163

Formulating Date in Excel 2013

4 months ago - Reply: 2 - Views: 88

VB6 extracts in Excel 2013

5 months ago - Reply: 2 - Views: 153

Excel 2013 : Pull Matching Data from Column

5 months ago - Reply: 1 - Views: 19

excel search and copy in/from another file

5 months ago - Reply: 0 - Views: 1

Buggy PivotTable.ColumnRange

5 months ago - Reply: 0 - Views: 4

Excel static conditional formatting area

5 months ago - Reply: 0 - Views: 8

vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re