Excel slicer instellen met vba lukt niet met een string

[enomiss]

Ik prober een slicer in te stellen met een string

Onderstaande werkt:

ActiveWorkbook.SlicerCaches("Slicer_Merk1").VisibleSlicerItemsList = Array( _
"[dXref].[Merk].&[J17]", "[dXref].[Merk].&[J18]")

In plaats van slechts 2 waarden J17 en J18 om de pivot table te filteren kunnen het er meer zijn zoals hieronder te zien is:

ActiveWorkbook.SlicerCaches("Slicer_Merk1").VisibleSlicerItemsList = Array( _
"[dXref].[Merk].&[J17]", "[dXref].[Merk].&[J18]", "[dXref].[Merk].&[J50]", "[dXref].[Merk].&[J500]")

Om dit op te lossen doe ik deze waardes verzamelen in een array, vervolgens "[dXref].[Merk].&[ ervoor zetten en ]", erachter zetten. Dit zet ik vervolgens met een For each netjes achter elkaar in een string genaamd "txt"

De string "txt" kan er als volgt uit zien:

"[dXref].[Merk].&[J17]", "[dXref].[Merk].&[J18]"

of

"[dXref].[Merk].&[J17]", "[dXref].[Merk].&[J18]", "[dXref].[Merk].&[J50]", "[dXref].[Merk].&[J500]"

Ik dacht vervolgens dat dit zou werken:

ActiveWorkbook.SlicerCaches("Slicer_Merk1").VisibleSlicerItemsList = Array( _
txt)

Maar helaas, ik heb al uren gegoogled, maar ik kom er echt niet uit. Ik blijf foutmeldingen krijgen. Er lijkt iets met de komma te zijn.

Wanneer ik handmatig dit doe dan werkt het wel:

test = "[dXref].[Merk].&[J17]"

ActiveWorkbook.SlicerCaches("Slicer_Merk1").VisibleSlicerItemsList = Array( _
test)

Wanneer ik handmatig dit doe dan werkt het niet:

test = "[dXref].[Merk].&[J17]" & Chr(44) & "[dXref].[Merk].&[J18]"

ActiveWorkbook.SlicerCaches("Slicer_Merk1").VisibleSlicerItemsList = Array( _
test)

Dus wanneer ik er meerdere tegelijk probeer te filteren dan krijg ik de foutmeldingen. Heeft iemand een idee waar dit aan ligt?

[ Voor 6% gewijzigd door enomiss op 06-07-2018 17:17 ]

[BertS]

Zet je het nu in één string in die for each? Dat moet ook in een array komen, met één item per positie in de array

[enomiss]

BertS schreef op zaterdag 7 juli 2018 @ 07:30:
Zet je het nu in één string in die for each? Dat moet ook in een array komen, met één item per positie in de array

Klopt, ik zet de losse items zoals bijvoorbeeld "[dXref].[Merk].&[J17]" (dit stukje tekst is 1 item, enkel J17 varieert bij andere items) achter elkaar in 1 string. Misschien moet ik dit niet doen, maar de slicer direct voeden met een array ipv met een string. Maar het lukt me niet om dit aan het werk te krijgen. Eigenlijk zou dus hieronder achter het = teken de naam van een vooraf gevulde array moeten staan, maar hoe formuleer ik dat, dat die de hele array voedt zeg maar?

ActiveWorkbook.SlicerCaches("Slicer_Merk1").VisibleSlicerItemsList =

[ Voor 4% gewijzigd door enomiss op 08-07-2018 23:52 ]

[NMe]

Waar hoort mijn topic?

PRG >>CSA

[enomiss]

Dit is de volledige sub, maar hij werkt dus nog steeds niet. Let niet op de laatste regel ActiveWorkbook.SlicerCaches("Slicer_Merk1").VisibleSlicerItemsList = Array(myArray), want die klopt nog steeds niet...

Sub test()

Dim Selectie As Range
Dim myArray() As String
Dim txt As String
Dim i As Long
Dim cell As Range
Dim FiltercodeBegin As String
Dim FiltercodeEinde As String

Set Selectie = Selection

i = 0
ReDim myArray(0)
For Each cell In Selectie
myArray(i) = cell
i = i + 1
ReDim Preserve myArray(i)
Next

FiltercodeBegin = """[dXref].[Merk].&["
FiltercodeEinde = "]"","


For i = LBound(myArray) To UBound(myArray) - 1
txt = txt & FiltercodeBegin & myArray(i) & FiltercodeEinde
Next i

txt = Left(txt, Len(txt) - 2)

myArray = Split(txt, ",")

ActiveWorkbook.SlicerCaches("Slicer_Merk1").VisibleSlicerItemsList = Array(myArray)

End Sub

Dit werkt overigens ook niet:
ActiveWorkbook.SlicerCaches("Slicer_Merk1").VisibleSlicerItemsList = Array(Split(txt, ","))

Ik heb dit gevonden:
https://www.mrexcel.com/f...d-string-array-array.html

Maar als ik dus dit doe werkt het ook niet:
ActiveWorkbook.SlicerCaches("Slicer_Merk1").VisibleSlicerItemsList = myArray

[ Voor 19% gewijzigd door enomiss op 09-07-2018 08:44 ]

[enomiss]

Ik heb eindelijk de oplossing gevonden. Zie hieronder. Blijkbaar hoefde de "" niet in de array items te zitten.

Visual Basic:

Sub FiltersMatchen()
                Dim Selectie As Range
                Dim myArray() As String
                Dim txt As String
                Dim i As Long
                Dim cell As Range
                Dim FiltercodeBegin As String
                Dim FiltercodeEinde As String

                Set Selectie = Selection

                i = 0
                ReDim myArray(0)
                For Each cell In Selectie
                    myArray(i) = cell
                    i = i + 1
                    ReDim Preserve myArray(i)
                Next

                FiltercodeBegin = "[dXref].[Merk].&["
                FiltercodeEinde = "]"

                For i = LBound(myArray) To UBound(myArray) - 1
                    txt = txt & FiltercodeBegin & myArray(i) & FiltercodeEinde & Chr(44)
                Next i
                
                txt = Left(txt, Len(txt) - 1)
                
'                MsgBox txt
                
                myArray = Split(txt, ",")

                ActiveWorkbook.SlicerCaches("Slicer_Merk1").VisibleSlicerItemsList = myArray
End Sub

[ Voor 3% gewijzigd door enomiss op 09-07-2018 11:53 ]

[breew]

@enomiss : ik had er net ook één voor je

Visual Basic:

Option Explicit

Sub test()

Dim slcItem As SlicerItem

'loop door de slicerItems in de slicer "naam
For Each slcItem In ActiveWorkbook.SlicerCaches("Slicer_naam").SlicerItems
  'als slcItem.Caption in de geselecteerde range voorkomt, dan aanzetten, anders uit
  If Not Selection.Find(slcItem.Caption, LookIn:=xlValues) Is Nothing Then
   slcItem.Selected = True
  Else
    slcItem.Selected = False
  End If
Next slcItem

End Sub

voor



na

[ Voor 18% gewijzigd door breew op 09-07-2018 09:09 ]

[enomiss]

breew schreef op maandag 9 juli 2018 @ 09:07:
@enomiss : ik had er net ook één voor je

Visual Basic:

Option Explicit

Sub test()

Dim slcItem As SlicerItem

'loop door de slicerItems in de slicer "naam
For Each slcItem In ActiveWorkbook.SlicerCaches("Slicer_naam").SlicerItems
  'als slcItem.Caption in de geselecteerde range voorkomt, dan aanzetten, anders uit
  If Not Selection.Find(slcItem.Caption, LookIn:=xlValues) Is Nothing Then
   slcItem.Selected = True
  Else
    slcItem.Selected = False
  End If
Next slcItem

End Sub

voor
[afbeelding]


na
[afbeelding]

Bedankt, die oplossing ziet er eleganter uit, echter ik krijg op regel 8 een error: application- defined or object defined error

[breew]

enomiss schreef op maandag 9 juli 2018 @ 09:20:
[...]
Bedankt, die oplossing ziet er eleganter uit, echter ik krijg op regel 8 een error: application- defined or object defined error

heb je "Slicer_naam" wel veranderd in "Slicer_Merk1"?

[ Voor 4% gewijzigd door breew op 09-07-2018 09:29 ]

[enomiss]

breew schreef op maandag 9 juli 2018 @ 09:27:
[...]


heb je "slicer_naam" wel veranderd in "slicer_Merk1"?

Ja heb ik gedaan. Ik denk dat het verschil erin zit dat jij een selectie maakt op een niet-pivot tabel, maar ik doe juist een selectie maken op de betreffende pivot tabel. Dus ik moet in 1x de juiste filters voeden. Volgens mij klapt de pivot tabel bij jou na 1 item in elkaar waardoor de selectie leegraakt. Daarom had ik die niet elegante oplossing.... of gaat dit ook met de jouwe? Application.EnableEvents = false lost ook niks op denk ik.

[ Voor 6% gewijzigd door enomiss op 09-07-2018 09:36 ]

[breew]

enomiss schreef op maandag 9 juli 2018 @ 09:34:
[...]


Ja heb ik gedaan. Ik denk dat het verschil erin zit dat jij een selectie maakt op een niet-pivot tabel, maar ik doe juist een selectie maken op de betreffende pivot tabel. Dus ik moet in 1x de juiste filters voeden. Volgens mij klapt de pivot tabel bij jou na 1 item in elkaar waardoor de selectie leegraakt. Daarom had ik die niet elegante oplossing.... of gaat dit ook met de jouwe?

Ah, jij selecteert 'in' de pivot tabel? Dat veranderd de zaak een beetje.. Ik selecteer in de brondata...
Misschien een stomme vraag, maar als je selecteert in de pivot-tabel, waarom selecteer je dan niet 'gewoon' op de slicer? Dan heb je meteen het gewenste resultaat...

[enomiss]

breew schreef op maandag 9 juli 2018 @ 09:37:
[...]


Ah, jij selecteert 'in' de pivot tabel? Dat veranderd de zaak een beetje.. Ik selecteer in de brondata...
Misschien een stomme vraag, maar als je selecteert in de pivot-tabel, waarom selecteer je dan niet 'gewoon' op de slicer? Dan heb je meteen het gewenste resultaat...

Klopt. Omdat ik ze dan 1 voor 1 moet aanklikken (foutgevoelig) en steeds moet scrollen door die kleine slicer. Nu kan ik heel snel met eventueel Ctrl een selectie maken op stukken van een hele kolom en dan met een knop kiezen op welke slicer ik deze selectie toepas. Vooral gebruiksgemak dus.
Daarnaast kan ik deze array of string bewaren en later gebruiken als een opgeslagen filterset of op andere slicers toepassen en de filters zo syncen zeg maar.
Ook voer ik andere vba code uit na elke filter wijziging, en bij elke individuele klik moet ik dan een halve seconde wachten, wat irritant is. Daarom wou ik dit dus.

[ Voor 17% gewijzigd door enomiss op 09-07-2018 10:21 ]