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

Début de la boite de navigation du travail pratique

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