[VBA] Macro pour comparer 2 fichiers Excel
Bonjour à toutes et à tous,
Je viens solliciter votre aide sur ce forum, après des heures d'essais sur une macro que je n'arrive pas à faire fonctionner comme je le souhaite.
Dans les grandes lignes, je dois comparer 2 fichiers Excel et en faire ressortir les différences de manière net et précise. Ces fichiers représentent les ventes par vendeurs entre une année X et Y
Etant débutant en Visual Basic, j'ai cherché sur notre ami Google une macro permettant de faire cela. Après multiples modifications pour correspondre à mes fichiers, le résultat obtenu était vraiment moyen...
La macro compare en effet 2 feuilles différentes dans 2 fichiers Excel, mais ne prend pas en compte le nom du vendeur, ni la différence de lignes de la feuille.
Voici le code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
With ws2.UsedRange
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
DiffCount = 0
For c = 1 To maxC
Application.StatusBar = "Comparing cells " & Format(c / maxC, "0 %") & "..."
For r = 1 To maxR
cf1 = ""
cf2 = ""
On Error Resume Next
cf1 = ws1.Cells(r, c).FormulaLocal
cf2 = ws2.Cells(r, c).FormulaLocal
On Error GoTo 0
If cf1 <> cf2 Then
DiffCount = DiffCount + 1
Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
End If
Next r
Next c
Application.StatusBar = "Formatting the report..."
With Range(Cells(1, 1), Cells(maxR, maxC))
.Interior.ColorIndex = 19
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error Resume Next
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error GoTo 0
End With
Columns("A:IV").ColumnWidth = 20
rptWB.Saved = True
If DiffCount = 0 Then
rptWB.Close False
End If
Set rptWB = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox DiffCount & " cells contain different formulas!", vbInformation, _
"Compare " & ws1.Name & " with " & ws2.Name
End Sub
Sub ApplyCompareWorksheets()
' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sales Master"), _
Workbooks("Discount Matrix April 2011 - Copie.xls").Worksheets("Sales Master")
End SubMerci d'avance pour me consacrer un peu de temps, bonne journée.
Bonjour,
Ou sont les fichiers joints?
Bonjour
Tu aurais du fournir les fichiers car juste avec la macro, on voit ce qu'elle fait mais on ne sait pas ce que TU veux
Tu peux joindre un fichier avec deux feuilles contenant chacune une version des informations que tu veux analyser
Et dans une autre feuille un exemple de ce que tu veux trouver comme renseignement sur les différences qui peuvent exister
A te lire
Désolé, j'ai oublié de joindre les dossiers.
Voici donc les deux fichiers Excel, contenant seulement les feuilles concernées.
Edit:
Pour le résultat obtenu, ci-joint une autre feuille Excel.
En gros, si la différence obtenu après la comparaison est "positive", cette même différence apparaît dans la cellule en vert. Si en revanche la différence est "négative", elle apparaît en rouge dans la cellule.
Je fais cela afin de voir facilement les chiffres de mes vendeurs. Mais comme j'ai des tonnes de fichiers Excel à examiner, ça me prend énormément de temps... C'est pour cela que j'aimerais créer une macro qui me permette de faire tout cela en un "ALT+F8".
Cordialement
Bonsoir Banzai64,
Je viens d'ouvrir "BigEarl Result V001.xlsx". Tout marche pour le mieux!
Je tenais à savoir si tous mes fichiers que je souhaite comparer doivent se nommer "xxxxxxxxxxxxxxxMatrix xxxxx 2010 - EP" et "xxxxxxxxxxxxxxxMatrix xxxxx 2011 - EP" ? Si jamais l'année change, dois-je modifier quelque chose dans le code?
Pour être plus explicite, je souhaite en gros savoir comment ta macro fonctionne.
En tous cas, je te remercie tout de même pour avoir dépenser de ton temps pour m'aider.
D'ici là, bonne soirée à vous internautes.
Bonsoir
Quelques explications dans le code
Plus une autre manière de faire
Les 2 façons marchent très bien. Si je comprend bien, la 2ème te permet de choisir tes deux fichiers manuellement?
Et pour mettre les points sur les "i", tous mes fichiers doivent se nommer " - EP" si je ne m'abuse? Car j'ai essayer sans, et j'ai un message d'erreur sur mon Workbook.
Encore merci pour ton aide,
Passez une bonne journée internautes.
Bonjour
Oui il faut que tes fichiers finissent par " -EP", comme cela je peux trouver l'année
Mais si tu n'es pas intéressé par l'année (je m'en sers ensuite pour noter en colonne D), on peut supprimer cette partie
A toi de choisir
Bonne journée
Non pour l'année cela me va très bien, il suffira juste que je renomme mes fichiers dans la base de données, ce qui n'est pas un problème.
Donc si je comprend bien, je peux maintenant ouvrir n'importe quel fichier de n'importe quelle année, et la comparaison se fera? (Car dans ta description sur la feuille Excel, tu indiques n'importe quel fichier du style
"xxxxxxx Matrix 2010/2011 - EP")
De plus, si jamais mes fichiers contiennent plusieurs feuilles, mais je souhaite seulement comparer la feuille "Sales Master" (celle sur laquelle tu as travaillé). Cela pose-t-il un problème?
Encore merci.
Edit:
Je viens de faire le test moi-même, et j'ai eu ma réponse. En changeant l'année cela ne cause pas de problème (Génial!). En revanche, si mon fichier contient d'autres feuilles en plus de "Sales Master", un message d'erreur apparaît.
Bonjour
Si les noms de tes fichiers ont un autre format, il serait peut-être intéressant de modifier la macro
J'utilise la 1ère page de chaque fichier, sans connaître le nom
Mais on peut nommer cette page si toujours identique
A la place de
With Sheets(1)remplacer par
With Sheets("Nom_De_La_Page")A voir exactement ce que tu veux
Je viens de remplacer par
With Sheets("Sales Master")à tous les endroits, mais en effectuant un test, je reçois un message d'erreur.
Bonjour
Je viens d'essayer et pas de soucis
Quel est le message d'erreur ?
Le message d'erreur affiché est:
"Erreur d'exécution '1004':
La méthode 'Open' de l'objet 'Workbooks' a échoué".
Et là il me propose "Fin" ou "Débogage".
Si tu peux m'envoyer ton code, afin que je vois ou j'ai fait une erreur dans le remplacement peut être?
Merci
Edit:
J'utilise différent fichier que ceux que je t'ai donner, cela peut être aussi une raison? Ces fichiers contiennent 3 feuilles: "Sheet1"; "Sheet2"; "Sales Master"
Bonjour,
Je viens seulement de faire le test avec "BigEarl Result V003.xls" et j'ai toujours le même message d'erreur, ce qui est plutôt bizarre.
Pourtant, il y a bien:
With Sheets("Sales Master")Je ne peux malheureusement pas envoyer mes fichiers Excel qui sont confidentiels.
Une autre question toutefois, est-il possible de comparer des tableaux contenant plus de colonnes avec ce code?
Cordialement
Bonjour, m'etant inspire de ton exemple et d'autres j'ai prefere continuer la discution plutot que de creer un nouveau sujet.
J'ai en effet le meme besoin, a quelques similitudes pretes, la difference etant que je souhaiterais pouvoir selectionner n'importe quels fichiers (le nom peut changer).
J'ai mon fichier principal qui comparera un autre fichier (au choix), mais les memes colonnes et donnees.
J'ai cette partie de code pour ouvrir mon fichier:
Files = Application.GetOpenFilename("Excel Workbook (*.xlsx),*.xlsx")
If Files = False Then Exit Sub
Ensuite j'aimerais pouvoir travailler sur le fichier selectionne et c'est ici le debut de mes problemes:
Workbooks.Open Filename:=Files
ActiveWorkbook.Sheets.Select
Je vous laisse mes fichiers en piece jointe.
Merci de votre aide!
Merci beaucoup! Au premier coup d'oeil, c'est exactement ce que j'essayais de faire!
Je reste admiratif (et un peu contrarie) de la vitesse de reponse ...
J'analyse ton code, avec ton accord et si j'ai un soucis de comprehension je reviendrais vers toi.
Merci encore.
Bonne annee
A.
J'ai voulu apporter une modification au fichier, mais je pense que je ne comprends pas une subtilite du code.
J'essaie d'avoir une deuxieme condition (le poids) en fonction de la premiere (la reference).
J'ai essaye avec un deuxieme IF, puis deux boucle for (en commentaire actuellement), mais aucun ne prend en compte ma condition Elseif dans ma seconde condition if(le poids).
Est-ce possible avec la synthaxe "Is Nothing Then"?
Voici ce que j'ai fait.
Merci