Comment créer et modifier des tableaux croisés dynamiques Excel avec VBA

(Last Updated On: 15 avril 2020)

Récemment, j’ai eu dans un projet à mettre à jour des tableaux croisés dynamiques (TCD) avec une macro.

En général je préfère limiter l’utilisation de code VBA à l’automatisation d’opérations en boucle qui seraient fastidieuses à réaliser à la main. Aujourd’hui Excel permet de réaliser énormément de choses sans passer par du code, et cela évite de maintenir le code, car l’utilisateur du fichier Excel n’a pas forcément l’expérience ni le temps pour le faire.

Mais pour ce projet là, le cahier des charges était d’avoir une base de données créée de manière dynamique en fonction de colonnes de variables que l’utilisateur choisissait. Cette base est ensuite exploitée par un TCD pour des fins d’analyse.

Pour modifier le TCD de manière dynamique, j’ai utilisé les macro VBA ci-dessous.

Pour les utiliser dans votre code, copier-coller simplement les macros dans un module Visual Basic (Alt + F11).

Vous pouvez aussi télécharger le fichier d’exemple en fin d’article.

Créer un tableau croisé dynamique

Sub CreerTCD(Optional ByVal tcdNom As String = "Tableau croisé dynamique 1")
' Créer un tcd dans une feuille existante à partir d'une plage de cellules

Dim tcd As PivotTable, tcdCache As PivotCache
Dim sRngStart As String, sRngAddress As String

' Plage de la source de données
sRngAddress = ActiveSheet.Range("A1:R100").Address(ReferenceStyle:=xlR1C1, External:=True)

' Cellule où insérer le tcd
sRngStart = ActiveSheet.Range("T1").Address(ReferenceStyle:=xlR1C1, External:=True)

' Créer le cache du tcd
Set tcdCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=sRngAddress)

' Créer le tcd à partir du cache
Set tcd = tcdCache.CreatePivotTable( _
    TableDestination:=sRngStart, _
    TableName:=tcdNom)

End Sub

Ajouter un champ de tableau croisé dynamique

Sub AjouterChampTCD(ByVal sChamp As String, ByVal sZoneTcd As String, Optional ByVal pos As Variant = 1, Optional ByVal sNombreFormat As String = vbNullString, Optional ByVal tcdNom As String = vbNullString)
' Ajoute un champ au tcd dans la zone spécifiée: filtre, ligne, colonne ou valeur

Dim tcd As PivotTable, sTxtChamp As String

If tcdNom <> vbNullString Then
    Set tcd = ActiveSheet.PivotTables(tcdNom)
Else
    Set tcd = ActiveSheet.PivotTables(1)
End If

If tcd.PivotFields(sChamp).Orientation = xlHidden Then

    Select Case sZoneTcd
    
        ' Ajoute un champ en Filtre, Ligne, Colonne
        
        Case "Filtre"
        tcd.PivotFields(sChamp).Orientation = xlPageField
        
        Case "Colonne"
        tcd.PivotFields(sChamp).Orientation = xlColumnField
        
        Case "Ligne"
        tcd.PivotFields(sChamp).Orientation = xlRowField
        
        ' Ajoute un champ en Valeur
        
        Case "Somme"
        sTxtChamp = "Somme de " & sChamp
        tcd.AddDataField tcd.PivotFields(sChamp), sTxtChamp, xlSum
        
        Case "Nombre"
        sTxtChamp = "Nombre de " & sChamp
        tcd.AddDataField tcd.PivotFields(sChamp), sTxtChamp, xlCount
        
        Case "Moyenne"
        sTxtChamp = "Moyenne de " & sChamp
        tcd.AddDataField tcd.PivotFields(sChamp), sTxtChamp, xlAverage
        
        Case "Max"
        sTxtChamp = "Max de " & sChamp
        tcd.AddDataField tcd.PivotFields(sChamp), sTxtChamp, xlMax
        
        Case "Min"
        sTxtChamp = "Min de " & sChamp
        tcd.AddDataField tcd.PivotFields(sChamp), sTxtChamp, xlMin
        
        Case "Produit"
        sTxtChamp = "Produit de " & sChamp
        tcd.AddDataField tcd.PivotFields(sChamp), sTxtChamp, xlProduct
        
        Case Else
        ' par défaut
        
    End Select
    
End If

End Sub

Supprimer un champ d’un tableau croisé dynamique

Sub SupprimerChampTCD(ByVal sChamp As String, Optional ByVal tcdNom As String = vbNullString)
' Supprime un champ du tcd

Dim tcd As PivotTable

If tcdNom <> vbNullString Then
    Set tcd = ActiveSheet.PivotTables(tcdNom)
Else
    Set tcd = ActiveSheet.PivotTables(1)
End If

If tcd.PivotFields(sChamp).Orientation <> xlHidden Then
    tcd.PivotFields(sChamp).Orientation = xlHidden
End If

End Sub

Actualiser un tableau croisé dynamique

Sub ActualiserTCD(Optional ByVal tcdNom As String = vbNullString)
' Actualise un tcd

Dim tcd As PivotTable

If tcdNom <> vbNullString Then
    Set tcd = ActiveSheet.PivotTables(tcdNom)
Else
    Set tcd = ActiveSheet.PivotTables(1)
End If

' Actualise un seul tcd
tcd.PivotCache.Refresh
 
' Actualise tous les tcd
'ActiveWorkbook.RefreshAll
    
End Sub

Supprimer un tableau croisé dynamique

Sub SupprimerUnTCD(Optional ByVal tcdNom As String = vbNullString)
' Supprime un tcd
On Error GoTo Erreur

If tcdNom <> vbNullString Then
    ActiveSheet.PivotTables(tcdNom).TableRange2.Clear
Else
    ActiveSheet.PivotTables(1).TableRange2.Clear
End If

Exit Sub

Erreur:
    MsgBox "Le tableau croisé dynamique ''" & tcdNom & "'' n'a pas été trouvé.", vbExclamation, "Suppression annulée"
    
End Sub

Changer la source de données d’un tableau croisé dynamique

Sub ChangerSourceTCD(Optional ByVal tcdNom As String = vbNullString)
' Change la source de données d'un tcd à partir d'une plage de cellules

Dim tcd As PivotTable, SrcData As String

On Error GoTo Erreur
If tcdNom <> vbNullString Then
    Set tcd = ActiveSheet.PivotTables(tcdNom)
Else
    Set tcd = ActiveSheet.PivotTables(1)
End If

' Détermine la nouvelle source de données du tcd
SrcData = ActiveSheet.Range("A1:R200").Address(ReferenceStyle:=xlR1C1, External:=True)

' Change la source de données
tcd.ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
 SourceType:=xlDatabase, _
 SourceData:=SrcData)
Exit Sub
Erreur:
   MsgBox "Impossible de changer la source du TCD.", vbExclamation
End Sub

Regarder la vidéo

Voici une courte vidéo qui montre l’utilisation des macros VBA pour modifier un TCD.

📥TELECHARGER LE FICHIER D’EXEMPLE

Téléchargez le classeur Excel pour ce tutoriel. Seuls votre adresse e-mail et nom vous seront demandés dans la prochaine étape. Vous recevrez le lien de téléchargement par e-mail.

Modifier les tableaux croisés dynamiques avec VBA

Ce fichier Excel montre comment modifier un tableau croisé dynamique Excel à l'aide de macros VBA.
Télécharger

Envoyer le lien de téléchargement à :

Oui, je consens à recevoir la Newsletter. Je confirme avoir lu et accepté la Politique de confidentialité.

Dites-moi en commentaire s’il y a d’autres types de modifications de TCD que vous souhaiteriez automatiser (ou que vous réalisez) avec une macro VBA!

 

 

Souscrivez à ma newsletter :

5 commentaires

  1. Salignon dit

    Bonjour,
    Je cherche à créer un TCD par VBA :
    Mon besoin est d’ajouter à un TCD classique une soustraction entre 2 dates de la forme xx/xx/xxxx
    Le but est de tirer un délai en jour ouvré (ne comptant pas les week end).
    Il s’agit en fait de pouvoir ajouter ce champs que j’appellerai “délai” sur le croisée dynamique.
    Si je veux le coder en vba c’est parce que je n’ai pas la possibilité d’ajouter cette formule directement avec l’option “champs calculés”.
    Serait il possible d’avoir un exemple de l’écriture/du codage pour faire ce champs ?
    Merci par avance,
    Bien cordialement,

  2. jepifavre dit

    Bonjour Mr,
    je suis depuis quelques temps votre site qui est particulièrement intéressant. Il y a une chose qui me plaît beaucoup qui est très personnalisé dans votre fichier. C’est l’incorporation d’un onglet “xl Busisness tools” qui fait apparaître une fenêtre “A propropos” dans le menu de Excel. Comment faites vous pour personnilser ainsi la fenêtre.
    Merci si vous nous faites un ajout dans un de vos prochain cours.
    Très amicalement
    boiseux

    • Lionel dit

      Bonjour,

      Merci pour votre commentaire 🙂

      J’utilise un ancien utilitaire pour Excel 2007 pour modifier le “Ribbon menu” d’Excel et y ajouter mon sous-menu. Il s’agit de Custom UI Tool.
      Je ne pense pas que je vais faire un article dans un futur proche et il existe déjà pas mal de ressources sur internet à propos de la customisation du menu Excel. Je vous recommande notamment cet excellent article : https://silkyroad.developpez.com/excel/ruban/

      Lionel

  3. Olivier dit

    Bonjour,

    Tout d’abord merci pour cet article très intéressant et qui nous simplifie la gestion des TCD en VBA

    Cependant je constate dans la procédure pour l’ajout d’un champ dans le tableau croisé, on as la possibilité de passer en attribut la position du nouveau champ mais celle-ci n’est pas utilisée dans le code ensuite.

    Cette possibilité m’intéresse mais en cherchant dans la documentation, je n’ai pas trouvé comment faire.
    Pouvez-vous m’indiquer comment réaliser cette partie ?

    Bien cordialement,

Répondre à Lionel Annuler la réponse

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *