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,

18inventaire-2017.zip (17.67 Ko)
20inventaire-2018.zip (18.02 Ko)

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.

22maverick39.xlsx (49.57 Ko)

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
tous deux situés dans le même dossier

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

doublons

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

Rechercher des sujets similaires à "macro comparaison deux fichiers"