Macro comparaison deux fichiers Excel
Bonjour à tous,
je tente de comparer deux fichiers Excel (2017 vs 2018) séparés.
Pour éviter de vérifier "à la main" ligne par ligne les informations, je voulais savoir s'il était possible de les comparer à l'aide d'une macro.
étant débutant en informatique (et particulièrement en VBA) , je voulais votre avis sur mon raisonnement et éventuellement une proposition de votre part.
Je pense que les étapes de la macro devront être les suivantes :
pour chaque feuille du fichier 2017,
pour chaque référence (colonne A)
Si le nom est également présent dans le fichier 2018
Mettre dans la colonne J = la quantité inscrite dans la colonne E du fichier 2018
sinon
écrire la nouvelle référence dans la colonne K
En gros, l'idée est de tout mettre sur le fichier 2017 car je ne sais pas faire autrement mais peut être est il plus simple de créer un nouveau fichier ?
Que pensez vous de ce problème ?
Est-ce réalisable sous VBA ?
Merci d'avance pour vos réponses,
Bien cordialement,
Bonjour MAVERICK39, le forum
Pourquoi écrire la nouvelle référence dans la colonne K
alors que les références se situent initialement en colonne A
klin89
@KLIN89 Merci pour ta réponse.
Si il y de nouvelles references en 2018 (par rapport à 2017), j'aimerais les écrire dans la colonne K pour bien les différencier.
C'est juste visuel mais on pourrait effectivement les mettre en colonne A à la suite des références 2017
Salut Maverick,
dis-moi qu'il y a bien d'autres feuilles avec bien d'autres lignes sinon, est-ce trop long de recopier simplement les colonnes visées ?
Même manuellement, depuis le moment de ton post, ce serait déjà fait...
Il y a un truc que je n'ai pas capté?
A+
Bonsoir Curulis
Evidemment, j'ai une trentaine d'onglets à valider.
Pour pouvoir déposer mes fichiers, j'ai préféré les rendre le plus petits possible et laisser 3 onglets dans l'exemple
re MAVERICK39
Salut curulis57
Remarque : il y a un doublon dans les références de la feuille "FL EB Doui"
Faut-il s'appuyer sur une autre colonne que la colonne A pour déterminer ces références
Sinon en supposant que les 2 fichiers sont dans le même dossier.
Place ce code dans un module standard du fichier "inventaire 2017", l'autre fichier restant fermé.
Option Explicit
Sub test()
Dim dico As Object, ws As Worksheet, i As Long
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
Application.ScreenUpdating = False
'ouverture du fichier source "inventaire 2018"
With Workbooks.Open(ThisWorkbook.Path & "\inventaire 2018")
For Each ws In .Worksheets
Set dico(ws.Name) = CreateObject("Scripting.Dictionary")
dico(ws.Name).CompareMode = 1
With ws
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
dico(.Name)(.Cells(i, 1).Value) = .Cells(i, 5).Value
Next
End With
Next
.Close False 'fermeture du fichier source
End With
With ThisWorkbook '"inventaire 2017"
For Each ws In .Worksheets
With ws
If dico.exists(.Name) Then
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
If dico(.Name).exists(.Cells(i, 1).Value) Then
.Cells(i, 10).Value = dico(.Name)(.Cells(i, 1).Value)
dico(.Name).Remove .Cells(i, 1).Value
End If
Next
End If
If dico(.Name).Count > 0 Then
.Cells(2, 11).Resize(dico(.Name).Count) = Application.Transpose(dico(.Name).keys)
End If
End With
Next
End With
Set dico = Nothing
Application.ScreenUpdating = True
End Sub
klin89
Bonsoir à tous,
merci énormément KLIN89 ! cela semble fonctionner sur mon tableur.
Je ne suis pas du tout à l'aise avec ton code qui utilise un dictionnaire mais j'ai essayé de continuer avec cette trame intéressante
Pour les nouvelles références, serait-il possible d'ajouter dans les colonnes suivantes la quantité et le prix ? Elles sont situées dans les colonnes 5 et 6 du fichier 2017 et à mettre à côté des nouvelles références.
End If
If dico(.Name).Count > 0 Then
J'ai essayé sur le même modèle que toi mais cela ne semble pas fonctionner ...
.Cells(2, 15).Resize(dico(.Name).Count) = Application.Transpose(dico(.Name).keys)
... Pourrais tu m'aider une dernière fois sur ce point ?
Je n'ai pas l'habitude de travailler sur ces points mais je trouve cet exercice prenant et vraiment intéressant
Merci d'avance pour ton aide vraiment précieuse pour moi
Bonne soirée,
Mav
Salut MAVERICK39, Klin,
autre façon de faire avec des tableaux.
Si j'ai (enfin) compris, résultats en '2017'.
- en [J] les quantités pour références similaires.
J'ai vu au moins une référence manifestement identique mais orthographiée différemment (JAUNES - JAUNE) : la macro l'a considérée comme nouvelle référence.
- en [K-L-M-N], les références '2018' inconnues en '2017'.
Si je n'ai pas trop mélangé mes x,y, ça ne devrait pas être loin...
La macro démarre sur un double-clic dans n'importe quelle feuille de '2017', '2018' devant être ouvert.
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'
Dim sWBk1 As Workbook, sWBk2 As Workbook, sWKs1 As Worksheet, sWKs2 As Worksheet
Dim tData(), tData1, tData2, iIdx%, iOK%
'
Cancel = True
Set sWKb1 = ThisWorkbook
'
For Each sWKb2 In Workbooks
If sWKb2.Name <> sWKb1.Name And InStr(UCase(sWKb2.Name), "INVENTAIRE") > 0 Then
For Each sWKs1 In sWKb1.Sheets
tData1 = sWKs1.Range("A1:E" & sWKs1.Range("A" & sWKs1.Rows.Count).End(xlUp).Row).Value
For Each sWKs2 In sWKb2.Sheets
iIdx = 0
If sWKs2.Name = sWKs1.Name Then
tData2 = sWKs2.Range("A1:F" & sWKs2.Range("A" & sWKs2.Rows.Count).End(xlUp).Row).Value
ReDim tData(WorksheetFunction.Max(UBound(tData1, 1), UBound(tData2, 2)), 5)
tData(0, 0) = "Existants"
tData(0, 1) = "Nouvelles références"
tData(0, 2) = "Ref LOUXOR"
tData(0, 3) = "Quantités"
tData(0, 4) = "Prix"
For x = 2 To UBound(tData2, 1)
iOK = 0
For y = 2 To UBound(tData1, 1)
If tData1(y, 1) = tData2(x, 1) And tData1(y, 2) = tData2(x, 2) Then
tData(y - 1, 0) = tData2(x, 5)
iOK = 1
Exit For
End If
Next
If iOK = 0 Then
iIdx = iIdx + 1
tData(iIdx, 1) = tData2(x, 1)
tData(iIdx, 2) = tData2(x, 2)
tData(iIdx, 3) = tData2(x, 5)
tData(iIdx, 4) = tData2(x, 6)
End If
Next
sWKs1.Range("J:N").ClearContents
sWKs1.Range("J1").Resize(UBound(tData, 1), 5).Value = tData
sWKs1.Range("J:N").EntireColumn.AutoFit
Exit For
End If
Next
Next
Exit For
End If
Next
'
End Sub
A+
Bonjour,
Un exemple de ce que tu peux faire si tu as Power Query ou si tu as la possibilité de le télécharger et de l'installer.
Pas de VBA et une liaison dynamique avec les 2 fichiers source (pour les actualisations si nécessaires)..
Le classeur à 3 feuilles.
2017 : références uniques
2017-2018 : références communes
2018 : références uniques (ajouts).
A te relire.
Cdlt.
Bonjour à tous et grand merci à vous d'avoir passé du temps à répondre , c'est vraiment très gentil
@Curulis , je n'arrive pas à lancer ton code. J'ai essayé de l’insérer dans un module car je ne suis pas arrivé à lancer la macro par doubleclick mais cela ne fonctionne pas
@ Jean Eric, ca à l'air super intéressant ton système !
j'ai réussi à ouvrir et voir tes TCD sans installer powerquery. Dois je quand même l'installer ? Comment puis je sélectionner les deux fichiers à lier (par la liaison dynamique) ?
J'ai tenté de modifier la source comme sur un TCD classique mais il me demande de "choisir la connexion" pour faire une requête ??
Merci d'avance pour vos réponses
Re,
La question est : Disposes-tu de Power Query et dans la négative, as tu la possibilité de le télécharger et de l'installer ?
Cdlt.
Oui je l'ai téléchargé mais je l'avais par défaut apparemment sur mon excel 2019.
J'ai donc un onglet "données " avec lequel je peux manipuler des données "externes"
Re,
Je reviens vers toi avec quelques explications.
Cdlt.
Avec plaisir !
j'aimerais pouvoir utiliser ta solution mais choisir mes deux fichiers :
- inventaire 2017
- inventaire 2018
Salut MAVERICK39,
le code VBA se trouve et doit être collé dans le module 'ThisWorkbook' de ton fichier de travail '2017' réel, le fichier devant alors être enregistré sous type XLSM... ce qui ne l'empêchera pas de fonctionner de manière classique avec les fonctions natives d'Excel.
Je te rassure : VBA n'est pas un cancer quoi que les puristes puissent dire!
Si tu veux mixer '2017' avec les résultats affichés en [K-L-M-N], tu fais signe!
A+
Re,
On va commencer par une nouvelle question:
Sais-tu accéder à la fenêtre de l'éditeur Power Query ?
Dans l'affirmative, Ouvre mon fichier et dans le Ruban de l'éditeur PQ, sélectionne Paramètres de la source des données.
le chemin des 2 fichiers source est à modifier avec le chemin de tes 2 fichiers sur ton PC.
A te relire.
Cdlt.
@ Curulis, merci j'ai réussi à utiliser ta fonction
désolé pour ces demandes de débutant, je ne maitrise pas totalement l'interface VB (pas encore....
@Jean eric, j'ai bien changé les sources de données en modifiant les deux fichiers.
rien n'a changé donc j'ai ensuite cliqué sur "actualiser tout" mais " des problèmes sont apparus durant l'obtention des données" ...
On va finir par y arriver step by step
Re,
Peux me donner le chemin exact de tes 2 fichiers sur ton PC ?
Cdlt.
re
Le code réajusté :
Option Explicit
Sub test()
Dim dico As Object, ws As Worksheet, i As Long
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
Application.ScreenUpdating = False
'ouverture du fichier source "inventaire 2018"
With Workbooks.Open(ThisWorkbook.Path & "\inventaire 2018")
For Each ws In .Worksheets
With ws
Set dico(.Name) = CreateObject("Scripting.Dictionary")
dico(.Name).CompareMode = 1
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
dico(.Name)(.Cells(i, 1).Value) = Array(.Cells(i, 1).Value, .Cells(i, 5).Value, .Cells(i, 6).Value)
Next
End With
Next
.Close False 'fermeture du fichier source
End With
With ThisWorkbook '"inventaire 2017"
For Each ws In .Worksheets
With ws
If dico.exists(.Name) Then
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
If dico(.Name).exists(.Cells(i, 1).Value) Then
.Cells(i, 10).Value = dico(.Name)(.Cells(i, 1).Value)(1)
dico(.Name).Remove .Cells(i, 1).Value
End If
Next
End If
If dico(.Name).Count > 0 Then
.Cells(2, 11).Resize(dico(.Name).Count, 3) = Application.Index(dico(.Name).items, 0, 0)
End If
End With
Next
End With
Set dico = Nothing
Application.ScreenUpdating = True
End Sub
Je persiste, il y a un doublon colonne A de la feuille "FL EB Doui", le résultat est ainsi faussé dans ce cas
klin89
re MAVERICK39,
C'est dingue, on ne peut plus revenir sur les posts précédents pour les modifier
Le code précédent corrigé
Option Explicit
Sub test()
Dim dico As Object, ws As Worksheet, i As Long
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
Application.ScreenUpdating = False
'ouverture du fichier source "inventaire 2018"
With Workbooks.Open(ThisWorkbook.Path & "\inventaire 2018")
For Each ws In .Worksheets
With ws
Set dico(.Name) = CreateObject("Scripting.Dictionary")
dico(.Name).CompareMode = 1
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
dico(.Name)(.Cells(i, 1).Value) = Array(.Cells(i, 1).Value, .Cells(i, 5).Value, .Cells(i, 6).Value)
Next
End With
Next
.Close False 'fermeture du fichier source
End With
With ThisWorkbook '"inventaire 2017"
For Each ws In .Worksheets
With ws
If dico.exists(.Name) Then
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
If dico(.Name).exists(.Cells(i, 1).Value) Then
.Cells(i, 10).Value = dico(.Name)(.Cells(i, 1).Value)(1)
dico(.Name).Remove .Cells(i, 1).Value
End If
Next
If dico(.Name).Count > 0 Then
.Cells(2, 11).Resize(dico(.Name).Count, 3) = Application.Index(dico(.Name).items, 0, 0)
End If
End If
End With
Next
End With
Set dico = Nothing
Application.ScreenUpdating = True
End Sub
klin89