XL 2021 VBA / Macro et tableau croisé dynamique

OLivier2014 · 12 Décembre 2023

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

goube · 12 Décembre 2023

Bonjour,
As tu envisagé la solution Power Query qui remplace avantageusement le VBA dans de nombreux cas.
Un exemple d'une ou plusieurs bases serait un plus pour obtenir une réponse adéquate.
Cordialement

oguruma · 12 Décembre 2023

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 :
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

oguruma · 13 Décembre 2023

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

Si on passe par les segments et leur construction dynamique

Les TCD

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

XL 2021 VBA / Macro et tableau croisé dynamique

OLivier2014

XLDnaute Nouveau

goube

XLDnaute Impliqué

oguruma

XLDnaute Occasionnel

oguruma

XLDnaute Occasionnel

Pièces jointes

Discussions similaires