XL 2013 TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

[Sebast]

Bonsoir à toutes et à tous,

qui peut me dire comment, par VBA, introduire une restriction dans un filtre placé en champ Page ?

J'y arrive quand c'est directement dans la source, c’est-à-dire les colonnes les unes à côté des autres, mais pas dans un TCD.
Je sais que je peux après coup, à la mano saisir la restriction dans le filtre mais j'ai tellement de TCD que je ne veux pas prendre le risque d'oublier.

Par exemple pour filtrer sur le champ "Emballage" tout ce qui contient la chaîne "CORB"

On constate que pour filtrer dans la table, RAS, on a une instruction simple et lisible :

Sub Filtre_Source()
With Sheets("Source")
    .Range("$A$1:$H$9048").AutoFilter Field:=2, Criteria1:="=*CORB*"
End With
End Sub

alors que quand on est dans le TCD, la sélection se fait par défaut (en gros, l'enregistreur n'affiche pas ce qui n'est pas "CORB" mais traduit cela non pas en code mais en une liste - négative) à la Prévert …

Quelqu'un a-t-il une idée comment exprimer cela en VBA ?

Merci d'avance

 

[Iznogood1]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

Bonjour

un exemple rapide

Sub demo()
  test (InputBox("Chaine de filtre", "Filtre TCD", "CORB"))
End Sub

Sub test(masque As String)
  Dim PI As PivotItem
  For Each PI In ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Emballage").PivotItems
    PI.Visible = (InStr(PI.Value, masque) > 0)
  Next
End Sub

Attention, code fait à l'arrache ; pas de gestion d'erreur par exemple si aucune entrée ne correspond à ton filtre.
Je te laisse faire...
Tu peux aussi améliorer le code (exemple en gérant les majuscules / minuscules, en gérant les erreurs possibles)

Enfin, je te conseille de ne pas utiliser ActiveSheet, mais directement le codename de ta feuille (Feuil1.PivotTable("machin")...)
De même, n'hésite pas à nommer tes TCD de manière plus explicit

 

[Sebast]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

Salut Iznogood1,

Merci de t’être penché sur ma question.
Ton code fonctionne et fait le job. J’ai bien pris note des modifs à appliquer pour intercepter d’éventuelles erreurs.

Cependant (excuse d’avance ma naïveté …) :

Est-on obligé de passer par une boîte de dialogue ?
J’ai essayé d’adapter le code en écrivant en dur « CORB » mais ça plante

Quelque chose comme

PI.Visible = « CORB » conduit malheureusement à un bug


Je tourne en rond et n’arrive pas à adapter le code.
Quelle serait la syntaxe pour avoir l’exécution directe du filtre, sans l’inputbox ?

Merci

 

[Bebere]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

bonjour
à tester

Sub demo()
test "CORB" '(InputBox("Chaine de filtre", "Filtre TCD", "CORB"))
End Sub

Sub test(masque As String)
Dim PI As PivotItem
For Each PI In Feuil3.PivotTables("Tableau croisé dynamique1").PivotFields("emballage").PivotItems
If Left(PI.Value, 1) <> "(" Then PI.Visible = (InStr(PI.Value, masque) > 0)
Next
End Sub

 

[Sebast]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

Salut Bebere, Iznogood1,

Merci pour vos codes respectifs.

Iznogood1
Je continue à coincer car je n’arrive pas à « purger » le filtre Emballage situé en champ Page, avant de lancer demo().

Un bug apparaît quand quelque chose a déjà été inscrit en filtre Emballage préalablement
Ex : tu lances une fois avec la restriction « CORB » ; ça fait bien le filtrage
Puis tu relances avec cette fois la restriction « KG » : ça plante.

Du coup, j’ai pensé qu’avec l’instruction suivante, on levait tous les filtres,

ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Emballage").CurrentPage = "(All)"

mais il n’en est rien …

Savez-vous quelle instruction insérer pour ne pas avoir de restriction sur « Emballage » (avant précisément de lancer demo()) ?


De plus, je n’arrive pas à adapter vos codes respectifs pour passer en direct, c’est-à-dire sans l’inputbox.

Merci d’avance

 

[Bebere]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

Sebast regarde le post#4, inputbox est en commentaire

 

[Iznogood1]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

Crée un bouton sur ta feuille et utilise le code suivant

Private Sub btnCORB_Click()
  Dim PI As PivotItem
  Application.ScreenUpdating = False
  For Each PI In Me.PivotTables("Tableau croisé dynamique1").PivotFields("Emballage").PivotItems
    PI.Visible = (InStr(PI.Value, "CORB") > 0)
  Next
  Application.ScreenUpdating = True
End Sub

Remarque :
Ce code doit être dans la feuille TCD pour que Me.PivotTables... fonctionne
Sinon, remplace le "Me" par Activesheet, ou Feuil2 ou Worksheets("TCD") ...

Attention, plantage si aucune entrée ne comporte la chaîne "CORB" (à gerer avec un On Error...)

 

[Sebast]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

Salut Iznogood1, salut Bebere,
merci beaucoup pour vos propositions, dont j'essaie de m'inspirer.

Bebere : tu avais raison, je n'avais pas vu la mise en commentaire …
Iznogood1 : merci pour ta patience et ton aide

Vous allez rire ! Je pense qu'il vaut effectivement mieux garder la main sur la valeur à rechercher (et donc garder l'inputbox) …
Du coup, j'ai adapté vos codes mais bute sur un pb avec la chaîne de caractères.
En fait, j'essaie maintenant de gérer les erreurs qui peuvent intervenir à la saisie.

Quand on saisit une valeur existant exactement dans le champ "Emballage", RAS (exemple : 5 KG)
Quand on saisit une valeur existant partiellement dans le champ "Emballage", RAS (ex : KG)
Mais quand on saisit un mot n'existant pas du tout, alors plantage ! (exemple : CARTON)

J'ai créé une variable comptant le nombre d'occurrences où le terme apparaît dans la plage de recherche
Nombre = Application.WorksheetFunction.CountIf(Zone, Answer)
mais cela ne fonctionne que pour une correspondance exacte
Ainsi "5 Kg" est trouvé car cette entrée existe mais pas "Kg"; or, Kg est bien une sous-chaîne de 5 Kg …

En fait, je n'arrive pas à inclure INSTR() dans Application.WorksheetFunction.CountIf(Zone, Answer)

Savez-vous me dire comme faire ?
et éventuellement me dire si mon filet de sécurité est valable (if Nombre >0 etc.) ?

(en pièce jointe mon code)

Merci d'avance

Sub Fitration()
    Choix (InputBox("La valeur suivante vous est proposée, validez ou saisissez-en une autre", "PivotTable Filter", "CORB"))
End Sub
 
Sub Choix(Answer As String)
 
Dim k As Long
Dim var As String
Dim z As Long
 
Dim Nombre As Long   ' pour compter combien de fois Answer apparaît dans la source
Dim Lastline As Long
Dim Zone As Range
 
' on définit où chercher
Lastline = Sheets("Source").Range("B" & Rows.Count).End(xlUp).Row
Set Zone = Sheets("Source").Range("B2:B" & Lastline)
Nombre = Application.WorksheetFunction.CountIf(Zone, Answer) ' ne marche que pour correspondance exacte
'MsgBox "Il y a " & Nombre & " entrées dans la base"
 
With Feuil3.PivotTables("Tableau croisé dynamique1").PivotFields("emballage")
 
    If Nombre > 0 Then ' c'est à dire s'il y a au moins une occurence (sinon bug)
 
        For k = 1 To .PivotItems.Count
            var = .PivotItems(k)
        
            If var <> "" Then .PivotItems(var).Visible = False
            If InStr(var, Answer) > 0 Then .PivotItems(k).Visible = True
            
         Next k
        
    Else ' i.e. la chaîne ne figure pas en colonne Emballage, donc pas de filtration possible sur ce critère
        MsgBox "La chaîne " & Answer & " n'est pas présente dans la base. Fin du traitement ... "
        Exit Sub
    End If
            
End With
End Sub

 

[Bebere]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

bonjour Sebst,Iznogood
avec une boucle,déplacé if nombre.....end if

Sub Choix(Answer As String)

Dim k As Long
Dim var As String
Dim z As Long

Dim Nombre As Long ' pour compter combien de fois Answer apparaît dans la source
Dim Lastline As Long
Dim Zone As Range
Answer = UCase(Answer)
' on définit où chercher
Lastline = Sheets("Source").Range("B" & Rows.Count).End(xlUp).Row
Set Zone = Sheets("Source").Range("B2:B" & Lastline)
a = Sheets("Source").Range("B2:B" & Lastline)
For k = 1 To UBound(a)
If InStr(a(k, 1), Answer) > 0 Then Nombre = Nombre + 1
Next k
'Nombre = Application.WorksheetFunction.CountIf(Zone, Answer & "*") ' ne marche que pour correspondance exacte
'MsgBox "Il y a " & Nombre & " entrées dans la base"
If Nombre > 0 Then ' c'est à dire s'il y a au moins une occurence (sinon bug)

With Feuil3.PivotTables("Tableau croisé dynamique1").PivotFields("emballage")
For k = 1 To .PivotItems.Count
var = .PivotItems(k)

If var <> "" Then .PivotItems(var).Visible = False
If InStr(var, Answer) > 0 Then .PivotItems(k).Visible = True

Next k
End With
Else ' i.e. la chaîne ne figure pas du tout en colonne Emballage, donc pas de filtration possible sur ce critère
MsgBox "La chaîne " & Answer & " n'est pas présente dans la base. Fin du traitement ... "
End If


End Sub

 

[Sebast]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

Salut Bebere,

Merci beaucoup pour ton aide détaillée.

Mes essais montrent que tout fonctionne sauf bizarrement pour « VRAC », alors que ce terme existe bel et bien dans la base …
Ça coince sur cette ligne :

If var <> "" Then .PivotItems(var).Visible = False

C’est incompréhensible …


De plus, je comprends à peu près la démarche mais ne suis pas familier des structures à la UBound.
Du coup, si on voulait être plus classique, il faudrait écrire comment les lignes suivantes :
a = Sheets("Source").Range("B2:B" & Lastline)
For k = 1 To UBound(a)

Encore merci et bonne soirée

 

[Bebere]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

bonjour Sebast,Iznogood
sous excel2010,çà fonctionne
qu'as tu comme erreur

 

[Sebast]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

Salut Bebere,

merci de t'être penché sur mon cas.
Quand je t'ai répondu hier soir (22 h 50), je testais depuis chez moi sur excel 2013 Windows 10 et ça coinçait à :

If var <> "" Then .PivotItems(var).Visible = False

(je noterai ce soir le message)

Or, ce matin, sur excel 2013 win7, plus de problème ! va comprendre ...

Je vais re-tester ce soir et noterai tout.

Suis quand même preneur s'il y a une solution sans UBound()

Merci beaucoup pour ton aide

 

[Sebast]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

Rebonjour,

Bien que je sois encore sur un poste autre que celui qui plantait hier soir, j’ai continué mes recherches et ai des bugs de même nature.

S’affiche le message « 1004 impossible de définir la propriété Visible de la classe PivotItem »
et s’affiche en jaune le deuxième membre de la ligne suivante (donc après Then) :

 If InStr(var, Answer) > 0 Then .PivotItems(k).Visible = True

Voici un enchainement de sélections qui conduit au plantage :
1 sélection : « 1 » nickel
2 sélection : CORB nickel
3 sélection : « 6 » plantage
Et quand je regarde dans le menu déroulant Emballage, je constate les incohérences suivantes :
Certes les items contenant 6 y sont bien mais il y a (entre autres) 7 KG, 8KG, VRAC … donc rien qui contient 6 !

Par ailleurs, j’ai remanié le code et ai introduit l’instruction

 .ClearAllFilters

Placée avant d’ouvrir/fermer sélectivement, en fonction du critère Answer


Pensant que ça aurait une incidence, mais sans effet. Cependant, ce code remanié me paraît plus lisible.

Peux-tu essayer l’enchainement 1, CORB, 6 et dis-moi si ça plante chez toi.

Encore merci pour ton aide

Sub Choix(Answer As String) ' Seb 0704/2016 (marche même si entrée absente)

Dim k As Long ' recense les pivotItems
Dim var As String ' prend la valeur du PivotItem rencontré
Dim compteur As Long ' pour compter les occurences d'une valeur d'un PivotItem

Application.ScreenUpdating = False

Answer = UCase(Answer)

' Etape 1 : on parcourt le filtres "Emballage" pour y compter les occurences "Answer"
With Feuil3.PivotTables("Tableau croisé dynamique1").PivotFields("emballage")
        For k = 1 To .PivotItems.Count
               var = .PivotItems(k)
               If InStr(var, Answer) > 0 Then compteur = compteur + 1 ' compteur incrémenté
        Next k
End With

'MsgBox "Le compteur s'élève à " & compteur ' juste pour contrôle


' Etape 2 : on affiche les items correspondant et on ferme les autres
With Feuil3.PivotTables("Tableau croisé dynamique1").PivotFields("emballage")

 If compteur > 0 Then
     .ClearAllFilters ' on lève tous les filtres préalablement retenus
 
     For k = 1 To .PivotItems.Count
     var = .PivotItems(k)

     If var <> "" Then .PivotItems(var).Visible = False
     If InStr(var, Answer) > 0 Then .PivotItems(k).Visible = True

     Next k

  Else ' juste pour contrôle
    MsgBox "La chaîne " & Answer & " est absente de la bdd, le traitement est interrompu"
    Exit Sub
    Application.ScreenUpdating = True

 End If

End With
Application.ScreenUpdating = True
End Sub

 

[Bebere]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

Sebast
je pense que le 1 dans la boucle doit être 2
le 1=tous

For k = 1 To .PivotItems.Count

edit:ce n'est pas bon
par contre tu peux ajouter

With Feuil3.PivotTables("Tableau croisé dynamique1").PivotFields("emballage")
.ClearManualFilter'à ajouter

edit:à tester

Sub Choix(Answer As String)
Dim k As Long
Dim var As String
Dim z As Long

Dim Nombre As Long ' pour compter combien de fois Answer apparaît dans la source
Dim Lastline As Long
Dim Zone As Range, test As Boolean
Answer = UCase(Answer)
' on définit où chercher
Lastline = Sheets("Source").Range("B" & Rows.Count).End(xlUp).Row
Set Zone = Sheets("Source").Range("B2:B" & Lastline)
a = Sheets("Source").Range("B2:B" & Lastline)
For k = 1 To UBound(a)
If InStr(a(k, 1), Answer) > 0 Then Nombre = Nombre + 1
Next k
'Nombre = Application.WorksheetFunction.CountIf(Zone, Answer & "*") ' ne marche que pour correspondance exacte
'MsgBox "Il y a " & Nombre & " entrées dans la base"
If Nombre > 0 Then ' c'est à dire s'il y a au moins une occurence (sinon bug)
On Error Resume Next

With Feuil3.PivotTables("Tableau croisé dynamique1").PivotFields("emballage")
.ClearManualFilter
For k = 1 To .PivotItems.Count
test = False
var = .PivotItems(k)
If InStr(var, Answer) > 0 Then .PivotItems(k).Visible = True: test = True
If test = False Then .PivotItems(k).Visible = False
Next k
On Error GoTo 0
End With
Else ' i.e. la chaîne ne figure pas du tout en colonne Emballage, donc pas de filtration possible sur ce critère
MsgBox "La chaîne " & Answer & " n'est pas présente dans la base. Fin du traitement ... "
End If

End Sub

 

[Sebast]

Re : TCD : filtre dans champ Page / comment exprimer une restriction en VBA ?

Salut Bebere,

Merci pour cette correction qui maintenant ne fait plus apparaitre les soucis rencontrés jusqu’ici.
J’ai testé beaucoup de cas de figures et ça semble nickel. Et bien sûr l’examen du code montre que les cas de figure litigieux sont envisagés.
Bien vu avec l’introduction de test, qui permet de lancer l’instruction selon le statut du test !

1. je pense que le 1 dans la boucle doit être 2
le 1=tous
For k = 1 To .PivotItems.Count

Je ne comprends pas, pour moi la boucle commence du premier au dernier item donc 1=first (et non tous)
Si on commence à 2, on en loupe 1 ?

.clearmanualfilter

et clearallfilter ?
Je pensais qu’en utilisant clearallfilter, je couvrais l’ensemble des filtres ?



Dans le code que j’ai posté à 16 h 05, je contrôle la présence de Answer en filtre en allant non pas (plus) dans l’onglet Source mais dans PivotFields("emballage")

Y vois-tu un inconvénient ou vaut-il par principe mieux attaquer la source du tcd (qui était mon option initiale) ?



Encore merci pour ton attention et ton aide précieuse