Obtenir résultat d'un TCD avec VBA

Version imprimable

16/09/2016, 22h51
ericdev67

Obtenir résultat d'un TCD avec VBA

Bonjour,

Comment est-il possible d'obtenir le résultat d'un TCD avec VBA .....:

J'ai une feuille, ~10000 lignes, ~50 colonnes. Je fais un TCD pour compiler et obtenir mes totaux, mais....avec en faisant 2-3 codes, le nom des TCD change et me pose problème....
Comment pourrait-on faire pour obtenir un tableau style TCD via VBA...?

Une petite aide ?

Merci pour les idées...

Bonjour,

Une solution possible dans cet exemple.

Il vous faut travailler avec un objet PivotTable.

Le code suivant crée un TCD dans un nouvel onglet. Dans cette version, le nom du TCD est défini par vous.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
 
Sub CreationTcd(ByVal FeuilleSourceTcd As Worksheet, ByVal LigneTitreSource As Long, ByVal NomFeuilleTcd As String)
 
Dim AireTcd As Range
Dim DerniereLigne As Long
Dim DerniereColonne As Long
Dim ShTcd As Worksheet
 
Dim Pvt As PivotTable
 
    With FeuilleSourceTcd
 
        .Cells.EntireRow.Hidden = False
        .Cells.EntireColumn.Hidden = False
 
         DerniereLigne = .Cells(.Rows.Count, 1).End(xlUp).Row  ' A adapter suivant la colonne de référence du tableau
         DerniereColonne = .Cells(LigneTitreSource, .Columns.Count).End(xlToLeft).Column
         Set AireTcd = .Range(.Cells(LigneTitreSource, 1), .Cells(DerniereLigne, DerniereColonne))
 
    End With
 
    ' Suppression éventuelle feuille Tcd déjà existante
    For Each ShTcd In Sheets
        If ShTcd.Name = NomFeuilleTcd Then
           Application.DisplayAlerts = False
           ShTcd.Delete
           Application.DisplayAlerts = True
           Exit For
         End If
    Next ShTcd
 
     ' Création de l'onglet supportant le TCD
     Set ShTcd = Worksheets.Add(after:=Sheets(Sheets.Count))
     With ShTcd
        .Name = NomFeuilleTcd
     End With
 
     ' Création du Tcd
     ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=AireTcd, Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="'" & NomFeuilleTcd & "'!R3C1", TableName:="TCD1", DefaultVersion:=xlPivotTableVersion12
 
       ' xlPivotTableVersion12 : A adapter suivant votre version d'Excel
 
     Set Pvt = ShTcd.PivotTables("TCD1")
 
     With Pvt
 
            With .PivotFields("Sexe")
                .Orientation = xlColumnField
                .Position = 1
            End With
            With .PivotFields("Date")
                .Orientation = xlRowField
                .Position = 1
            End With
 
            .AddDataField Pvt.PivotFields("Id"), "Nombre de Id", xlCount
 
            ' Groupement des années et des mois
            .RowRange.Cells(3, 1).Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
 
            ' Attribution d'un style
            .TableStyle2 = "PivotStyleDark7"
 
            ' La zone ColumnRange est la zone au dessus des données
            With .ColumnRange
                .HorizontalAlignment = xlCenter
                .ColumnWidth = 16
            End With
 
 
            ' La zone DataBodyRange est la zone des données
            With .DataBodyRange
                .HorizontalAlignment = xlCenter
            End With
 
 
    End With
 
    ActiveWindow.DisplayGridlines = False
 
    Set AireTcd = Nothing
    Set ShTcd = Nothing
 
End Sub

La macro est lancée dans l'onglet source avec cette procédure :

Code:

1
2
3
4
 
Private Sub BoutonCreerLeTcd_Click()
        CreationTcd Sheets("Données"), 10, "Tcd"
End Sub

Si vous n'avez pas la main sur le nom du TCD :
S'il n'y a qu'un seul TCD sur l'onglet, le problème ne se pose pas.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
Sub SelectionTcd()
 
Dim ShTcd As Worksheet
Dim Pvt As PivotTable
Dim I As Integer
 
    Set ShTcd = Sheets("Feuil1") ' Nom de la feuille contenant le TCD
 
    With ShTcd
         If .PivotTables.Count > 0 Then
            For I = 1 To .PivotTables.Count
                Set Pvt = .PivotTables(I)
                Pvt.TableRange2.Select  ' La méthode Select n'est utilisée que pour montrer le TCD
                                                 ' sinon elle n'est pas utile
                MsgBox Pvt.Name
                '........
                Set Pvt = Nothing
            Next I
        End If
    End With
 
    Set ShTcd = Nothing
 
End Sub

Une fois compris le principe, il vous faut travailler avec l'enregistreur de macro et corriger le code selon vos besoins.

L'exemple est dans le fichier joint :

Pièce jointe 220498

Pour connaître les zones nommées d'un TCD travail-zones-nommees-tcd-vba

Cordialement.

17/09/2016, 09h39
ericdev67

Bonjour Eric Kergresse,

Merci pour cette explication très claire et détaillée pour une création et utilisation des TCD.

En fait, je pensais éviter d'utiliser un TCD, je pensais plutôt utiliser un tableau matrice qui pourrait me donner le même résultat, sans passer par un TCD. Je pensais à des tableaux avec plusieurs dimensions et utilisation des "ubunt " etc...

Mais je vous remercie grandement pour votre aide et garde votre code "au chaud"

Merci pour votre aide,
17/09/2016, 10h10
Invité

Que cherchez-vous à récupérer dans votre TCD ? Les totaux de la dernière colonne ?

Cordialement.
17/09/2016, 13h16
ericdev67

Bonjour,

Il y a un peu de ça, mais pas seulement... :
C'est un fichier de suivi commercial , résultat final attendu :
En ligne, le nom des commerciaux, en colonne les différentes villes et en valeurs la somme des ventes par ville. Ce que fait naturellement un TCD. Et pour répondre à votre question,
Ce qui m'interesse essentiellement, c'est connaître pour chaque commercial, la ville où la somme des ventes est la plus grande...
En vous remerciant pour votre aide,
17/09/2016, 14h22
78chris

Bonjour

Alors TCD ou pas TCD ?

Un simple filtre sur un TCD, donnera ce top villes si on met Commercial et ville en ligne avec un filtre 10 premiers appliqué au champ ville, limité à 1 en partant du haut.
17/09/2016, 21h58
ericdev67

Merci,
Effectivement on peut se poser la question...et finalement je préfère l'option sans TCD puisque je n'ai besoin que de 1 seul chiffre ( le plus grand, par ville et par commercial), à force de les utiliser ( sans vba), je pensais que c'était une solution, mais finalement, un tableau matrice pourrait correspondre à ce que je recherche...
Une petite aide ?
En vous remerciant

C'est bon, j'ai trouvé mon bonheur dans les tutos de BOISGONTIER, avec les listes triées sans doublons.

Merci pour votre aide,