Discussion :

[EmmanuelleC]

Bonjour,

Je dois réaliser une macro excel qui doit prendre des informations dans des fichiers utilisé par des commerciaux et en sortir dun fichier plat standardisé qui pourra etre importé dans l'ERP

Au niveau logique, je commence par parcourir le fichier du commercial ligne par ligne. Pour chaque ligne, si c'est un article, je recopie les informations dans mon fichier a moi
Ensuite, je rajoute une colonne ou j'indique les probleme rencontrés (champ obligatoire absent, champ qui devrait etre numérique qui ne l'est pas,...)
Et enfin, je vire les doublons

Ca se met gentillement en place, mais je ne suis pas sure d'avoir fait les bon choix pour la gestion des doublons.

Pour mes doublons, je réalise les opérations suivante
1) je numérote mes lignes dans une colonne non utilisée
2) je trie par ordre de n° d'article, puis de prix d'achat, prix de vente, catégorie d'objet, sous catégorie, taxe récupel, quantité minimum d'achat et devise d'achat (logiquement, tout ces champs sont supposés devoir etre identiques pour un article donné)
3) je fais une boucle à partir du bas de mon tableau
Si le n° d'article est identique au n° d'article de la ligne supérieur
---Si oui, est-ce que toute la ligne est identique
------Si oui, j'efface ma ligne
------Si non, je regarde si la ligne du haut ne serait pas complete (pas de champ manquant) alors que ma ligne actuelle serait incomplete
---------Si oui, alors j'efface ma ligne actuelle
---------Si non, je garde les deux ligne mais je note qu'il y a un doublon et j'indique quel est le(s) champ(s) qui differe
---Si non, je passe a la ligne d'apres
4) je retrie dans l'ordre d'origine
5) j'efface ma colonne qui a servit pour le tri

Ca m'a l'air raisonnablement logique, ca fonctionne a priori comme il faut mais je ne suis pas convaincue que le code soit

Au niveau code ca donne ca

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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
Sub doublon()
'sous procédure de vérification des doublons
'masquer les actions de la macro
Application.ScreenUpdating = False
derlgn = Workbooks(classeuract).Sheets(1).Cells(Workbooks(classeuract).Sheets(1).Columns(1).Cells.Count, 1).End(xlUp).Row
'numéroter les lignes de facon à pouvoir les remettre dans le bon ordre à la fin
For m = 6 To derlgn
    Workbooks(classeuract).Sheets(1).Cells(m, 22) = m - 5
Next m
 
'trier la plage pour regrouper les doublons
    Workbooks(classeuract).Sheets(1).Sort.SortFields.Clear
    Workbooks(classeuract).Sheets(1).Sort.SortFields.Add Key:=Range("B6:B" & derlgn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    Workbooks(classeuract).Sheets(1).Sort.SortFields.Add Key:=Range("D6:D" & derlgn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    Workbooks(classeuract).Sheets(1).Sort.SortFields.Add Key:=Range("E6:E" & derlgn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    Workbooks(classeuract).Sheets(1).Sort.SortFields.Add Key:=Range("H6:H" & derlgn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    Workbooks(classeuract).Sheets(1).Sort.SortFields.Add Key:=Range("I6:I" & derlgn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    Workbooks(classeuract).Sheets(1).Sort.SortFields.Add Key:=Range("K6:K" & derlgn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    Workbooks(classeuract).Sheets(1).Sort.SortFields.Add Key:=Range("P6:P" & derlgn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    Workbooks(classeuract).Sheets(1).Sort.SortFields.Add Key:=Range("Q6:Q" & derlgn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
 
    With Workbooks(classeuract).Sheets(1).Sort
        .SetRange Range("A5:V" & derlgn)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
'on part du bas pour éviter ls problemes quand on suprime la ligne ne cours
For m = derlgn To 6 Step -1
    'si le pn fournisseur est le même sur la ligne m et la ligne m-1, alors
    If Workbooks(classeuract).Sheets(1).Cells(m, 2) = Workbooks(classeuract).Sheets(1).Cells(m - 1, 2) Then
        'vérifier si tout les champs significatif sont identiques, si oui, effacer la ligne
        If Workbooks(classeuract).Sheets(1).Cells(m, 2) & Workbooks(classeuract).Sheets(1).Cells(m, 4) & Workbooks(classeuract).Sheets(1).Cells(m, 5) & Workbooks(classeuract).Sheets(1).Cells(m, 8) & Workbooks(classeuract).Sheets(1).Cells(m, 9) & Workbooks(classeuract).Sheets(1).Cells(m, 11) & Workbooks(classeuract).Sheets(1).Cells(m, 16) & Workbooks(classeuract).Sheets(1).Cells(m, 17) = Workbooks(classeuract).Sheets(1).Cells(m - 1, 2) & Workbooks(classeuract).Sheets(1).Cells(m - 1, 4) & Workbooks(classeuract).Sheets(1).Cells(m - 1, 5) & Workbooks(classeuract).Sheets(1).Cells(m - 1, 8) & Workbooks(classeuract).Sheets(1).Cells(m - 1, 9) & Workbooks(classeuract).Sheets(1).Cells(m - 1, 11) & Workbooks(classeuract).Sheets(1).Cells(m - 1, 16) & Workbooks(classeuract).Sheets(1).Cells(m - 1, 17) Then
            Workbooks(classeuract).Sheets(1).Rows(m & ":" & m).Delete
        End If
    End If
 
    'si le pn fournisseur est le même sur la ligne m et la ligne m-1, alors
    If Workbooks(classeuract).Sheets(1).Cells(m, 2) = Workbooks(classeuract).Sheets(1).Cells(m - 1, 2) Then
        'vérifier si la seconde ligne est incomplete et que la premiere est complete, alors
        'effacer la seconde
        'normalement, à cause du tri, ca sera toujours dans ce sens la, les vides étant trié
        'apres les pleins
        If (Replace(Workbooks(classeuract).Sheets(1).Cells(m, 18), "Nom trop long", "") <> "") And (Replace(Workbooks(classeuract).Sheets(1).Cells(m - 1, 18), "Nom trop long", "") = "") Then
            Workbooks(classeuract).Sheets(1).Rows(m & ":" & m).Delete
        End If
    End If
 
    'si le pn fournisseur est le même sur la ligne m et la ligne m-1, alors
    If Workbooks(classeuract).Sheets(1).Cells(m, 2) = Workbooks(classeuract).Sheets(1).Cells(m - 1, 2) Then
        'traiter les doublons quand les deux sont complets ou tout les deux sont incomplets avec
        'des différences entres les deux lignes
            'si la différence est au niveau du prix d'achat
            If Workbooks(classeuract).Sheets(1).Cells(m, 4) <> Workbooks(classeuract).Sheets(1).Cells(m - 1, 4) Then
                k = m
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m, 18) = Workbooks(classeuract).Sheets(1).Cells(m, 18) & "doublon différence prix d'achat"
                k = m - 1
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) = Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) & "doublon différence prix d'achat"
            End If
            'si la différence est au niveau du prix de vente
            If Workbooks(classeuract).Sheets(1).Cells(m, 5) <> Workbooks(classeuract).Sheets(1).Cells(m - 1, 5) Then
                k = m
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m, 18) = Workbooks(classeuract).Sheets(1).Cells(m, 18) & "doublon différence prix de vente"
                k = m - 1
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) = Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) & "doublon différence prix de vente"
            End If
            'si la différence est au niveau de la famille
            If Workbooks(classeuract).Sheets(1).Cells(m, 8) <> Workbooks(classeuract).Sheets(1).Cells(m - 1, 8) Then
                k = m
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m, 18) = Workbooks(classeuract).Sheets(1).Cells(m, 18) & "doublon différence famille"
                k = m - 1
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) = Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) & "doublon différence famille"
            End If
            'si la différence est au niveau de la sous famille
            If Workbooks(classeuract).Sheets(1).Cells(m, 9) <> Workbooks(classeuract).Sheets(1).Cells(m - 1, 9) Then
                k = m
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m, 18) = Workbooks(classeuract).Sheets(1).Cells(m, 18) & "doublon différence sous-famille"
                k = m - 1
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) = Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) & "doublon différence sous-famille"
            End If
            'si la différence est au niveau du type (récupel ou non)
            If Workbooks(classeuract).Sheets(1).Cells(m, 11) <> Workbooks(classeuract).Sheets(1).Cells(m - 1, 11) Then
                k = m
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m, 18) = Workbooks(classeuract).Sheets(1).Cells(m, 18) & "doublon différence type (récupel)"
                k = m - 1
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) = Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) & "doublon différence type (récupel)"
            End If
            'si la différence est au niveau de la quantité minimum
            If Workbooks(classeuract).Sheets(1).Cells(m, 16) <> Workbooks(classeuract).Sheets(1).Cells(m - 1, 16) Then
                k = m
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m, 18) = Workbooks(classeuract).Sheets(1).Cells(m, 18) & "doublon différence Qté min"
                k = m - 1
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) = Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) & "doublon différence Qté min"
            End If
            'si la différence est au niveau de la devis d'achat
            If Workbooks(classeuract).Sheets(1).Cells(m, 17) <> Workbooks(classeuract).Sheets(1).Cells(m - 1, 17) Then
                k = m
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m, 18) = Workbooks(classeuract).Sheets(1).Cells(m, 18) & "doublon différence devise achat"
                k = m - 1
                Excel.Run ("retourchariot")
                Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) = Workbooks(classeuract).Sheets(1).Cells(m - 1, 18) & "doublon différence devise achat"
            End If
    End If
Next m
 
'je remet un derlgn pour le réactualiser apres la supression des doublons
derlgn = Workbooks(classeuract).Sheets(1).Cells(Workbooks(classeuract).Sheets(1).Columns(1).Cells.Count, 1).End(xlUp).Row
 
'remettre les informations dans l'ordre en utilisant la colonne V qu'on a numéroté en début de procédure
Workbooks(classeuract).Sheets(1).Sort.SortFields.Clear
Workbooks(classeuract).Sheets(1).Sort.SortFields.Add Key:=Range("V6:V" & derlgn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
 
With Workbooks(classeuract).Sheets(1).Sort
    .SetRange Range("A5:V" & derlgn)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
 
Workbooks(classeuract).Sheets(1).Columns("V:V").Delete
 
Application.ScreenUpdating = True
End Sub

le fichier a la structure suivante
colonne A : le n° de ligne dans le fichier du commercial ou se trouve l'article (entier)
colonne B : le n° d'article fournisseur (texte)
colonne C : premier champ de description de l'article (texte)
colonne D : prix d'achat de l'article (nombre)
colonne E : prix de vente de l'article (nombre)
colonne F : nom de la grille de tarif utilisée (texte)
colonne G : département de l'article (entier)
colonne H : catégorie de l'article (entier a 5 chiffre)
colonne I : sous catégorie (texte a 3 chiffres)
colonne J : le n° du fournisseur (entier a maximum 6 chiffres)
colonne K : si l'article est un appareil qui est concerné par la taxe récupel (1 ou 16)
colonne L : champ vide
colonne M : un code a calculer a partir de la colonne K (texte)
colonne N : champ vide
colonne O : second champ de description de l'article (texte)
colonne P : quantité minimum d'article a acheté (entier)
colonne Q : devise utilisée pour l'achat de l'article (texte)
colonne R : colonne utilisée pour indiquer tout les problemes (doublon avec différence, champ requis absent, champ qui ne respecte pas le format attendu,...)
les colonnes S, T et U sont utilisées aussi (mais ne contiennent pas de donnée utiles pour les doublons). V est la premiere colonne libre

classeuract : contient le nom du classeur a utilisé qui est initialisé dans la procédure principale
retourchariot : est une petite macro qui ajout un chr(10) dans la colonne R (celle des commentaires) avant d'ajouter un nouveau commentaire s'il y en avait déja un. Elle utilise un paramettre entier (k) qui lui indique quelle est la ligne concernée

Alors, a votre avis, est-ce que j'ai cherché midi a quatorze heure. Et si oui, comment est-ce que je simplifie ca?
Est-ce que ca fait gagner quelque chose de commencer par virer les doublons sans différence avec l'outil remove duplicate?

Merci beaucoup a tout ceux qui ont pris le temps de lire tout ca :p

[Pierre Fauconnier]

Salut.

Pour moi, commence par virer les doublons avec l'outil idoine du 2007. Ainsi, tu traiteras moins de lignes pour la suite (ajout des problèmes rencontrés, etc, ...)

[EmmanuelleC]

Je ne pense pas pouvoir le faire a la source a cause de deux problemes :
- Les doublons doivent rester dans le fichier du commercial (par exemple, un article peut faire partir d'un ou plusieur pack d'articles ou etre acheté a la piece, dans ce cas, elle apparait une fois dans chaque pack et une fois indépendement).
Je ne veux pas les garder pour mon importation, mais je ne peux pas lui saccager son fichier

-le fichier du commercial compte en général plusieurs onglets. Ca n'est pas un probleme pour ma macro qui les parcours les un apres les autres, mais a ce que je connais de l'outil anti doublon 2007, il ne fonctionne qu'en interne a un onglet non? Hors il peut avoir des doublons identiques entre onglets (par exemple un onglet par machine et des pieces qui peuvent servir pour plusieurs machines se retrouvent une fois dans chaque onglet)

Je peux par contre le faire dans mon fichier apres import, mais ca veut dire boucler une fois pour importer, faire tourner l'outil anti doublon et puis boucler une seconde fois pour indiquer les problemes alors qu'actuellement, je ne boucle qu'une fois et j'en profite quand je met quelques chose dans une cellule pour vérifier si c'est un contenu cohérent (par exemple, quand je remplis le n° du fournisseur qui doit etre un entier, j'ai un if qui vérifie si c'est vide ou non numérique ou len() >6, si oui, je remplis la colonne probleme en signalant un probleme surle n° de fournisseur)

Je n'y connais rien du tout en optimisation, ca vaut le coup de boucler une fois de plus (a ce que j'en ai vu, au pire, la boucle concerne 4000 lignes)?

[Pierre Fauconnier]

C'est toujours délicat sans voir le(s) fichier(s), car c'est toujours au cas par cas.

Perso, dans une feuille de travail, j'importerais toutes les lignes de toutes les feuilles (a priori, une boucle sur les différentes feuilles avec import en bloc des données de chaque feuille et éventuellement ajout d'une colonne pour mentionner la source), puis traitement anti-doublons, puis ajout des problèmes, ... L'avantage étant de ne boucler qu'une seule fois sur les lignes, et uniquement sur les lignes qui doivent être traitées plutôt que sur des doublons.

Mais je précise que ce n'est que suppositions, et que chaque cas est unique.

[EmmanuelleC]

ok, je te remercie beaucoup

J'avais fait le choix de n'importer que les lignes d'articles (parce qu'il y a des lignes d'autre chose, nom de catégories, description de l'article, notes personnelles,...) en espérant gagner du temps de traitement mais je ne suis pas sure d'avoir fait le bon choix par rapport a un import en gros et puis effacer les lignes parasites.

je vais essayer de trouver comment mesure mon temps d'exécution

[Pierre Fauconnier]

Au début de la macro

Code :

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

1
2
3
4
5
6
dim Debut as date, Fin as date
Debut = now()
...
...
Fin = now()
msgbox format(fin-debut,"nn:ss")