XL 2021 VBA / Macro et tableau croisé dynamique

OLivier2014

XLDnaute Nouveau
Bonjour,



Je souhaiterais effectuer en macro des tableaux croisés dynamiques à partir d’une base de données quotidienne. La difficulté est que les critères des TCD ne sont pas toujours présents dans la base de données. Exemple ci-dessous où je dois lister toutes les marques possibles mais il se peut que demain je n’ai pas de Fiat ou de Citroen dans les données et du coup la macro plante



With ActiveSheet.PivotTables("PivotTable1").PivotFields("Marque")

.PivotItems("Renault").Visible = True

.PivotItems("Citroen").Visible = True

.PivotItems("BMW").Visible = True

.PivotItems("Mercedes").Visible = True

.PivotItems("Fiat”).Visible = True



Je fais appel svp aux costauds du VB. Deux solutions peut être :

_ La 1ere en améliorant le code en mettant un équivalent de « iserror »

_ La 2eme avec une variable qui listerait toutes les marques possibles indiquées dans un tableau de A1 jusqu’à An et créer une boucle dans pivots items pour i allant de 1 à n avec la valeur true



Que puis je mettre comme code VB ? Ou toute autre solution simple si vous avez une idée facile



Merci beaucoup
 

oguruma

XLDnaute Occasionnel
Dans la même veine des idées.... en général je fais un TCD "brut de pomme" avec tous les éléments. En général pour formater à ta guise le tableau TCD à ta guise c'est toujours un peu galère.
Donc en général je construis un tableau plus présentable et personnalisé avec une capture dynamique en ligne et colonnes des informations que je souhaites récupérer et pour cela tu as la fonction certes un peu compliqué mais puissante :
1702388791112.png

si ça peut apporter de l'eau à ton moulin ;)

conseil aussi renommes ton TCD "PivotTable1" ==> en un nom qui sonne bien surtout si tu en as plusieurs.
Après tu peux te faire une table de paramètres et gérer tes tcd via cette table.
J'ai fait un post là-dessus sur une classe qui permet de gérer une table de paramètres....
c'est dans le forum trucs & astuces

s("Renault"). ==> ça se paramètre
PivotFields("Marque") : idem

et en effet il est possible de faire du segment dynamique
 
Dernière édition:

oguruma

XLDnaute Occasionnel
Bonjour Voici un début de solution avec activation dynamique des filtres et segments.
J'ai pris comme exemple les résultats du Bac ça va rappeler des souvenirs.
La méthode :
- construire des listes dynamiques avec la fonction DECALER
- ici deux listes : les années et les origines sociales
pour construire les listes plusieurs méthodes :
- soit via un TCD
- soit via la fonction UNIQUE et on utilise l'adresse étendu (#) dans le NBVAL pour déterminer la profondeur de la liste
... il y a aussi d'autres méthodes mais ce n'est pas l'objet du post. Pour cela j'ai déposé un trucs & astuces

1702472707258.png


1702472371511.png


1702472395845.png


1702472415923.png


1702472434451.png


Si on passe par les segments et leur construction dynamique
1702472476587.png


Les TCD
1702472502378.png


Voici le code
VB:
Option Explicit

Sub ACTIVER_FILTRES_ANNEES_1()
    Call SET_FILTRES_V1("TCD_BAC", "ANNEE", "LST_ANNEES_1", True)
End Sub
Sub ACTIVER_FILTRES_ANNEES_2()
    Call SET_FILTRES_V1("TCD_BAC", "ANNEE", "LST_ANNEES_2", True)
End Sub

Sub ACTIVER_SEG_ANNEES_1()
    Call SET_FILTRES_V1("TCD_BAC", "ANNEE", "LST_ANNEES_1", True)
End Sub
Sub ACTIVER_SEG_ANNEES_2()
    Call SET_FILTRES_V1("TCD_BAC", "ANNEE", "LST_ANNEES_2", True)
End Sub


Sub ACTIVER_FILTRES_ORIGINE_1()
    Call SET_FILTRES_V1("TCD_BAC_2", "ORIGINE_SOCIALE", "LST_ORIGINES_1", True)
End Sub
Sub ACTIVER_FILTRES_ORIGINE_2()
    Call SET_FILTRES_V1("TCD_BAC_2", "ORIGINE_SOCIALE", "LST_ORIGINES_2", True)
End Sub

Sub ACTIVER_SEG_ORIGINE_1()
    Call SET_FILTRES_V1("TCD_BAC_2", "ORIGINE_SOCIALE", "LST_ORIGINES_1", True)
End Sub
Sub ACTIVER_SEG_ORIGINE_2()
    Call SET_FILTRES_V1("TCD_BAC_2", "ORIGINE_SOCIALE", "LST_ORIGINES_2", True)
End Sub


Private Sub SET_FILTRES_V1(hTCD As String, hPivotFields As String, hList As String, hOnOff As Boolean)
    Dim sVal As Variant
    ActiveSheet.PivotTables(hTCD).PivotFields(hPivotFields).CurrentPage = "(All)"
    With ActiveSheet.PivotTables(hTCD).PivotFields(hPivotFields)
        For Each sVal In Range(hList)
            Debug.Print sVal.Value
            .PivotItems(CStr(sVal.Value)).Visible = hOnOff
        Next
    End With
    ActiveSheet.PivotTables(hTCD).PivotCache.Refresh
End Sub

Sub ACTIVER_SEGEMENT_1(hTCD As String, hSegment As String, hList As String, hOnOff As Boolean)
    Dim sVal As Variant
 
    With ActiveWorkbook.SlicerCaches(hSegment)
        For Each sVal In Range(hList)
            Debug.Print sVal.Value
            .SlicerItems(CStr(sVal.Value)).Selected = hOnOff
        Next
    End With
    ActiveSheet.PivotTables(hTCD).PivotCache.Refresh
End Sub

En fait il suffit de boucler sur le contenu de la liste passée en paramètres pour activer ou désactiver les critères.

ça devrait te donner des pistes pour ton problèmes.

voir fichiers joint (.csv source, .xlsm)

Je passe par du powerquery pour travailler le fichier .csv ça te permettra de te faire la main sur pwq
 

Pièces jointes

  • REUSSITE_AU_BAC.xlsm
    52.9 KB · Affichages: 1
  • fr-en-reussite-au-baccalaureat-origine-sociale.txt
    22.7 KB · Affichages: 0
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
313 309
Messages
2 097 033
Membres
106 812
dernier inscrit
Excellou74