Macro de création de TCD utilisable sur plusieurs fichiers

**VictorienR** · 13/05/2015, 13h32

Bonjour à tous,

Alors voilà, je viens vous voir aujourd'hui car j'aurai vraiment besoin d'un programme, cependant il dépasse mon niveau d'apprenti à VBA. Je reçois chaque semaine un Plan de livraison (exemple en pièce jointe) qui contient une liste en général de plusieurs milliers de lignes avec des pièces à livrer. Sur chaque ligne il y a : la référence de la pièce, son nom, une date de livraison et une quantité.
Le soucis c'est que sur plusieurs milliers de lignes, c'est illisible... Donc j'ai recherché comment faire, et j’ai découvert les TCD : un super outil d'ailleurs, cependant le plan de livraison étant différent chaque semaine, il faut recréer le TCD toutes les semaines en rentrant correctement les bons paramètres, un enfer surtout que je ne maîtrise vraiment pas ces TCD !

J'ai pensé donc à un code qui permettrai à la réception de chaque plan de livraison, de créer automatiquement le TCD qui détaillerai sur les deux premières colonnes les références et désignations de chaque pièce, sur les colonnes suivantes les mois de l'année (janvier à décembre 2015), et qui donnerai une quantité totale à livrer pour chaque pièce à chaque mois.

Alors après quelques recherches sur internet, je suis tombé sur un sujet un peu similaire au miens , qui y détaillait un code vraiment pas mal pour mon problème (code ci-dessous). Cependant j'ai vraiment beaucoup de mal à le comprendre, et à essayer de l'adapter à mon soucis... J'ai déjà créé un classeur de macro personnel, je sais comment rajouter une icone pour pouvoir utiliser une macro dans n'importe quel autre fichier, il ne me reste "plus que" le code à adapter...

Si quelqu'un pouvait éventuellement m'aider sur cela, ce serait génial !
Je vous met également en pièce jointe le document vierge qui était donné dans le lien pour l'utilisation du code ci-dessous.

Merci beaucoup,

Victorien

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Option Explicit
'Option Private Module
 
Public Sub Traitement_Importation()
Dim wb As Workbook
Dim wsData As Worksheet, wsPT As Worksheet
Dim lastCol As Long, lastRow As Long
Dim lo As ListObject
Dim ptCache As PivotCache
Dim pt As PivotTable
Dim CalcMode As XlCalculation
 
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
 
    Set wb = ActiveWorkbook
    Set wsData = wb.Worksheets(1)
    With wsData
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        Set lo = wsData.ListObjects.Add(xlSrcRange, .Cells(1).Resize(lastRow, lastCol), , xlNo)
        With lo
            .Name = "tblImportation"
            .TableStyle = "TableStyleLight8"
        End With
    End With
 
    wb.Worksheets.Add After:=Worksheets(Worksheets.Count)
    Set wsPT = ActiveSheet
    ActiveSheet.Name = "TCD"
 
    With wsPT
        Set ptCache = wb.PivotCaches.Create(xlDatabase, lo.Range, 3)
        Set pt = ptCache.CreatePivotTable(.Cells(1), "PT_1", , 3)
        With pt
            .ManualUpdate = True
            With .PivotFields("Colonne2")
                .Orientation = xlRowField
               .Caption = "Mois"
            End With
            With .PivotFields("Colonne7")
                .Orientation = xlDataField
                .Position = 1
                .Function = xlSum
                .NumberFormat = "#,##0"
                .Caption = "Colonne7 "
            End With
            With .PivotFields("Colonne9")
                .Orientation = xlDataField
                .Position = 2
                .Function = xlSum
                .NumberFormat = "#,##0"
                .Caption = "Colonne9 "
            End With
           .ManualUpdate = False
            .ManualUpdate = True
            .PivotFields("Mois").LabelRange.Offset(1, 0).Group Start:=True, End:=True, _
                    Periods:=Array(False, False, False, False, True, False, False)
            .RowAxisLayout xlTabularRow
            .TableStyle2 = "PivotStyleMedium2"
            .ManualUpdate = False
        End With
    End With
 
    Application.Calculation = CalcMode
 
    Set pt = Nothing
    Set ptCache = Nothing
    Set lo = Nothing
    Set wsPT = Nothing: Set wsData = Nothing
    Set wb = Nothing
 
End Sub

**78chris** · 13/05/2015, 14h14

Bonjour

Envoyé par VictorienR

cependant le plan de livraison étant différent chaque semaine, il faut recréer le TCD toutes les semaines en rentrant correctement les bons paramètres

Si le fichier a toujours la même structure, il faut créer le TCD dans un autre classeur et ne pas recommencer à chaque fois.

On peut, soit créer le TCD directement sur le fichier, soit au préalable importer les lignes par liaison. Simplement le fichier lié doit garder le même nom et ne pas bouger de place (afin de ne pas refaire la liaison, manuellement ou par VBA)

Ainsi le TCD est pérenne...

**VictorienR** · 13/05/2015, 14h59

Bonjour Chris,

J'ai un autre fichier qui avec une macro copie à le nouveau plan de livraison chaque semaine, cependant j'avais tenté d'y associer un TCD mais il n'est pas compatible avec la macro : le TCD ne se met pas à jour (même en faisant le rafraîchissement), les données se chamboulent, et du coup il faut recommencer le TCD à chaque fois. De plus par soucis de simplicité pour ne pas à avoir à manipuler plusieurs fichiers je préférerai le faire directement à chaque fois sur le nouveau fichier reçu. La solution que j'ai trouvé est exactement la même, je ne comprends juste pas les lignes de code qui créent le TCD, donc je n'arrive pas à les adapter...

**78chris** · 13/05/2015, 15h51

Re

Sans plus d'explications, tu as peu de chance d'avoir des réponses.

Tu joins 2 fichiers mais on ne sait pas à quoi ils correspondent : SOUS-TOTAL n'ayant pas d'en-tête n'est pas utilisable. Aucun des 2 ne contient de TCD, donc aucune information sur l'objectif visé

Tu utilises office 2013 mais un format xls : les TCD ne se pilotent pas pareil, notamment en VBA, selon la version et un format xls induit des contraintes 97-2003 sur les TCD.

On ne sait pas ce que tu entends par "le TCD ne se met pas à jour (même en faisant le rafraîchissement), les données se chamboulent". Si la structure de la source est stable il n'y a aucune raison que quoi que ce soit soit chamboulé.

Il faut au minimum décrire clairement :

la structure de la source
la structure du TCD : champs de lignes, colonnes, valeurs etc

et le format de tes fichiers xls ou xlsx.

Utilise aussi l'enregistreur de macros pour voir le code généré et comparer avec ton exemple.

**VictorienR** · 13/05/2015, 16h33

Effectivement j'ai du mal expliquer :

Le fichier sous-total que j'ai mis en pièce jointe n'est pas à moi, il vient du lien que j'ai donné et permet d'utiliser le code que j'ai donné dessus, pour que l'ont puisse voir à peu près ce que je souhaiterai obtenir au final.

Voici deux fichiers en pièce jointe : le fichier "TCD vba plan de livraison" est le fichier avec lequel je peux faire le copiage des nouveaux plans de livraisons chaque semaine pour détecter si il y a apparition d'une nouvelle pièce. C'est dans ce fichier que pourrait se faire le TCD. Il y a d'ailleurs dans la feuille "TCD des commandes" un TCD comme je souhaiterai obtenir à chaque fois que je copie un nouveau plan de livraison.

Je te transmet également comme deuxième fichier le fichier "Plan de livraison" qui pourrait te permettre de simuler la manip que je fait quand je reçois un nouveau plan de livraison similaire chaque semaine : lancer la macro qui va demander à l'utilisateur de chercher ce nouveau plan de livraison, et de le copier avec le bouton "recherche du nouveau PL". C'est une fois que cette manip est faite que j'aimerai que le TCD s'actualise en fonction du nouveau plan de livraison que l'ont vient de copier.

Voilà en espérant que ce soit un peu plus précis...

Invité · 13/05/2015, 18h56

Bonsoir,
Regardes ce lien: http://www.developpez.net/forums/d14...s/#post7904864

**78chris** · 13/05/2015, 19h12

RE

Déjà il y a une colonne Période dans la source absente de la destination.

Ce que je conseillerais c'est

avoir un classeur modèle avec le TCD, et la liste de référence et la source au format adéquat (ou alors adapter la copie)

à l'ouverture du classeur source vider les lignes du tableau (tableau au sens 2007-2013) qui sert de source au TCD :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
With ActiveWorkbook.ActiveSheet.ListObjects("Tableau1")
 
    If .ListRows.Count > 0 Then .DataBodyRange.Rows.Delete  
End With

mais en principe elles doivent être vides dans le modèle.

rechercher la taille de la source : ne pas copier 5000 lignes mais juste de la ligne 2 à la fin sous la ligne de titre du tableau. Dans ton code, tu as pour trouver la taille les lignes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
lastRow = .Cells(Rows.Count, 1).End(xlUp).Row

et il faut copier vers la cellule A2 de ton tableau1

il ne reste plus qu'à fermer le fichier et actualiser le TCD (+ la macros des couleurs si nécessaire) et enregistrer sous où tu le veux.

Edit : salut rdurupt ;-)

Invité · 13/05/2015, 20h20

Salut Chris

Macro de création de TCD utilisable sur plusieurs fichiers

Macros et VBA Excel

Discussions similaires

Partager

Partager