Macros-commandes VBA/Travail pratique/Construire un tableau croisé dynamique

Cas pratiques

Cas pratique n° 1 : Créer une base de données

Créer un nouveau fichier EXCEL supportant les macros VBA : Voyage.xlsm
Ajouter les intitulés suivants en ligne (A1 : I1) :

 Numéro ; Prénom ; Nom ; Adresse ; Code postal ; Ville ; H/F ; Prix voyage ; Réglé

Indiquer dans chaque rubrique des données fictives

Solution

Exemple base de données :

Cas pratique 1 : Base de données

Cas pratique n° 2 : Création automatique d'un TCD avec la méthode de l'enregistreur

En reprenant l'énoncé du cas pratique 1 :

Utiliser l'enregistreur pour créer un TCD automatiquement
Contraintes du TCD :

On veut une décomposition par sexe et état de règlement La source du TCD devra être faite de façon à ce que si l'utilisateur rajoute une ligne, celle-ci soit prise en compte dans le TCD

L'utilisateur n'aura qu’à cliquer sur un bouton pour activer la macro

Solution

Les étapes :

Activation de l'enregistreur
Créer un TCD normalement
Arrêter l'enregistreur
Création d'un bouton et affectation de la macro
Exécution de la macro en cliquant sur le bouton
Erreur d'écriture
Modification de la macro
Enregistrement

Lors de l'enregistrement, la macro suivante est créée :

Sub CREERTCD()
'
' CREERTCD Macro

' Sélection des données
    Columns("A:H").Select
' Création du TCD
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Feuil1!L1C1:L1048576C8", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Feuil4!L3C1", TableName:="Tableau croisé dynamique1", _
        DefaultVersion:=xlPivotTableVersion12
    Sheets("Feuil4").Select
    Cells(3, 1).Select
' Choix des champs
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Sexe")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique1").PivotFields("Réglé"), _
        "Nombre de Réglé", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Sexe")
        .PivotItems("(blank)").Visible = False
    End With
End Sub

Or, une boîte de dialogue s'ouvre pour indiquer une erreur. L'erreur provient de ce passage :

 
 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Feuil1!L1C1:L1048576C8", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Feuil4!L3C1", TableName:="Tableau croisé dynamique1", _
        DefaultVersion:=xlPivotTableVersion12

Pour résoudre le problème, il faut supprimer quelques lignes :

Il faut effacer la valeur qui se trouvait dans : TableDestination

Cela revient à : TableDestination = ""

Supprimer les lignes :

Sheets.add ; Sheets (« sheetname »).Select ; Cells (x,y).Select

L'écriture finale est :

Sub CREERTCD()
'
' CREERTCD Macro
' Sélection des données
    Columns("A:H").Select
' Création du TCD
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Feuil1!L1C1:L1048576C8", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="", TableName:="Tableau croisé dynamique1", _
        DefaultVersion:=xlPivotTableVersion12
' Choix des champs
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Sexe")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique1").PivotFields("Réglé"), _
        "Nombre de Réglé", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Sexe")
        .PivotItems("(blank)").Visible = False
    End With
End Sub

Cas pratique n° 3 : Actualisation du TCD avec la méthode de l'enregistreur

En reprenant l'énoncé du cas pratique 1 :

Créer une macro via l’enregistreur permettant d’actualiser le TCD automatiquement
Créer un bouton et lui affecter la macro
Rajouter des données dans votre tableau
Cliquer sur le bouton pour tester l’actualisation de votre TCD

Solution

Les étapes :

Cliquer sur "Affichage" ; "Macro" ; "Enregistrer une macro"
Donner un nom à la macro : "ACTUALISERTCD"
Clic droit sur le TCD ; "Actualiser"
Arrêter l'enregistrement
Insérer une forme
Clic droit sur la forme : "Modifier le texte", exemple : "Actualiser le TCD"
Clic droit sur la forme : "Affecter une macro" ; sélectionner la macro "ACTUALISERTCD"
OK

L'écriture crée par Excel est la suivante :

Sub ACTUALISERTCD()
'
' ACTUALISERTCD Macro
    Sheets("TCD").Select
    Range("B5").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
End Sub

Cas pratique n° 4 : Extraction de données avec une macro

En reprenant l'énoncé du cas pratique 1 :

Extraire par une macro la valeur du nombre total de voyages non réglés (hommes et femmes confondus)
Modifier la fonction de synthèse du champ de valeur "Nombre de voyage" par "Somme de voyage" par une macro
Avec cette nouvelle organisation, extraire la valeur de la somme total de voyage réglés (hommes et femmes confondus)

Solution

Extraire la valeur du nombre total de voyages non réglés (hommes et femmes confondus)

Nous souhaitons récupérer les champs "Réglé", "N" avec pour champ de valeur le nombre.

Pour cela il faut bien vérifier que ces 3 champs sont glisser dans la liste des champs du TCD. Si ce n’est pas le cas il faut les rajouter.

Puis, nous pouvons écrire le code VBA avec la synthaxe exacte des champs.

Sub ExtraitValeurNombre()

    Dim TCD As PivotTable
    Dim rPlage As Range
    
    ' Défini le TCD sur lequel on souhaite récupérer la valeur
    Set TCD = ActiveSheet.PivotTables("TCD_Voyages")
   
    ' Récupération du total de la valeur du champ de valeur "Nombre de voyage"
    'de la colonne "Réglé N" 
    Set rPlage = TCD.GetPivotData("Nombre de voyage", "Réglé", "N")
    MsgBox rPlage.Value

End Sub

Modifier la fonction de synthèse du champ de valeur "Nombre de voyage" par "Somme de voyage" par une macro

Cette procédure modifie la synthèse du champ de valeur "Nombre de voyage", afin d'afficher la somme et de le renommer "Somme de voyage".

Sub ModifFunction()

    Dim TCD As PivotTable
    Dim tcd_fld As PivotField
    
    Set TCD = ActiveSheet.PivotTables("TCD_Voyages")
    With TCD.PivotFields("Nombre de voyage")
    
        .Function = xlSum
        .NumberFormat = "# ##0 €"
        .Caption = "Somme de voyage"
        
    End With
    
End Sub

Avec cette nouvelle organisation, extraire la valeur de la somme totale de voyage réglés (hommes et femmes confondus)

Nous souhaitons récupérer les champs "Réglé", "O" avec pour champ de valeur la somme.

Pour cela il faut bien vérifier que ces 3 champs sont glisser dans la liste des champs du TCD (question précédente).

Puis, nous pouvons écrire le code VBA avec la synthaxe exacte des champs.

Sub ExtraitValeurSomme()

    Dim TCD As PivotTable
    Dim rPlage As Range
    
    ' Défini le TCD sur lequel on souhaite récupérer la valeur
    Set TCD = ActiveSheet.PivotTables("TCD_Voyages")
   
    ' Récupération du total de la valeur du champ de valeur "Somme de voyage"
    'de la colonne "Réglé O"
    Set rPlage = TCD.GetPivotData("Somme de voyage", "Réglé", "O")
    MsgBox rPlage.Value

End Sub

Cet article est issu de Wikiversity. Le texte est sous licence Creative Commons - Attribution - Partage dans les Mêmes. Des conditions supplémentaires peuvent s'appliquer aux fichiers multimédias.