macro to compare two excel file.

Diena

New Member
Joined
Jun 12, 2023
Messages
4
Office Version
  1. 2021
Platform
  1. Windows
  2. MacOS
Hello everyone, I need help to create a macro code that compares two excel files automatically.

I have a price list that I need to update every month and when the new file is uploaded, we put the old file in one with the old ones and only use the new one. So I have an old file and a new file with the same information, the only thing that can change is the purchase price or the quantity. So I only have two columns to compare.

So I'd like to find a macro code that helps me compare these two files automatically, and that allows me to put the references that actually have a change in a new folder that will be the archive of the changes. In this third sheet, when there's a change in the price list, I want the line for that reference to be copied into this file.


I've done something similar in the past but haven't found a solution for this one.

Here's the code I have at the moment, but it doesn't work :

File1 = is the old file.
File2 = the new file.
Sheet1 = the sheet that have to be compare from the old file.
Sheet2 = the sheet that have to be compare from the new file.
SummaryResults = the archive file.
VBA Code:
Sub CompareExcelversionFiles()
    Dim File1 As Workbook
    Dim File2 As Workbook
    Dim SummaryResults As Workbook
    Dim Sheet1 As Worksheet
    Dim Sheet2 As Worksheet
    Dim SheetHist As Worksheet
    Dim line As Long
    Dim lastLineHist As Long
    
    ' Specify path and file names
    Set File1 = Workbooks.Open("/Users//Downloads/Test/File1.xlsx")
    Set File2 = Workbooks.Open("/Users//Downloads/Test/File2.xlsx")
    Set history = Workbooks.Open("path_to_history.xlsx")
    
    ' Specify the name of the sheets to be compared
    Set Sheet1 = Sheet1.Sheets("Sheet1")
    Set Sheet2 = Sheet2.Sheets("Sheet2")
    Set SheetHist = History.Sheets("SummaryResults")
    
    ' Set the last line of archive
    derniereLigneHist = feuilleHist.Cells(feuilleHist.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through lines in both files
    For line = 1 To Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row

        ' Checks if the line in version 2 is different from version 1
        If Not Sheet1.Cells(row, 1).Value = Sheet2.Cells(row, 1).Value Then
            ' Copy line from version 1 to history
            Sheet1.Rows(line).Copy sheetHist.Rows(lastLineHist + 1)
            lastLineHist = lastLineHist + 1
        End If
    Next line
    
    ' Close files without saving them
    File1.Close SaveChanges:=False
    File2.Close SaveChanges:=False
    archvie.Close SaveChanges:=True
    
    ' Free memory
    Set File1= Nothing
    Set File2 = Nothing
    Set archive= Nothing
    Set File1= Nothing
    Set File2 = Nothing
    Set feuilleHist = Nothing
    
    MsgBox "Comparison complete. Modified lines have been copied to history."
End Sub
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi @Diena
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

You will need to make various adjustments to the macro. Here is a guide to the changes you should make:

1. Update the folder and path of each of the 3 files.​
Rich (BB code):
  ' Specify path and file names
  Set File1 = Workbooks.Open("C:\trabajo\files\File1.xlsx")             'is the old file
  Set File2 = Workbooks.Open("C:\trabajo\files\File2.xlsx")             'the new file
  Set File3 = Workbooks.Open("C:\trabajo\files\path_to_history.xlsx")
--------------------​
2. Update the sheet name for each of the workbooks.​
Rich (BB code):
  ' Specify the name of the sheets to be compared
  Set sh1 = File1.Sheets("Sheet1")            'is the old file
  Set sh2 = File2.Sheets("Sheet2")            'the new file
  Set sh3 = File3.Sheets("SummaryResults")
--------------------​
So I only have two columns to compare.
3. You mention that you need to compare 2 columns, so you must put in the macro what those two columns are, in the example I put "A" and "B", adjust to your need.​
Rich (BB code):
    If sh1.Range("A" & i).Value <> sh2.Range("A" & i).Value Or _
       sh1.Range("B" & i).Value <> sh2.Range("B" & i).Value Then

NOTE: You must take special care in all the names of folders, files and sheets. Any name you don't put correctly will cause the macro to fail.

--------------------​

4. Put the macro in a module of your macro book.​
VBA Code:
Sub CompareExcelversionFiles()
  Dim File1 As Workbook, File2 As Workbook, File3 As Workbook
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim i As Long, lastLineHist As Long
 
  ' Specify path and file names
  Set File1 = Workbooks.Open("C:\trabajo\files\File1.xlsx")             'is the old file
  Set File2 = Workbooks.Open("C:\trabajo\files\File2.xlsx")             'the new file
  Set File3 = Workbooks.Open("C:\trabajo\files\path_to_history.xlsx")
 
  ' Specify the name of the sheets to be compared
  Set sh1 = File1.Sheets("Sheet1")            'is the old file
  Set sh2 = File2.Sheets("Sheet2")            'the new file
  Set sh3 = File3.Sheets("SummaryResults")
  lastLineHist = sh3.Range("A" & Rows.Count).End(xlUp).Row + 1
 
  ' Loop through lines in both files
  For i = 1 To sh1.Range("A" & Rows.Count).End(xlUp).Row
    ' Checks if the line in version 2 is different from version 1
    If sh1.Range("A" & i).Value <> sh2.Range("A" & i).Value Or _
       sh1.Range("B" & i).Value <> sh2.Range("B" & i).Value Then
      ' Copy line from version 1 to history
      sh1.Rows(i).Copy sh3.Rows(lastLineHist)
      lastLineHist = lastLineHist + 1
    End If
  Next
 
  ' Close files
  File1.Close SaveChanges:=False
  File2.Close SaveChanges:=False
  File3.Close SaveChanges:=True
 
  MsgBox "Comparison complete. Modified lines have been copied to history."
End Sub

If you perform the steps in the guide properly you will not have problems.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
 
Upvote 0
Thank you so much, it really help me a lot. Thanks again
 
Upvote 0
Dear DanteAmor,

I found this code very useful to apply to my scenario, however having troubles applying a different range than just two columns A,B
I my case, I have two files to compare with range A:AK. I tried multiple scenarios, however VBA doesn't work
Can you please advise on how to change this part:

If sh1.Range("A" & i).Value <> sh2.Range("A" & i).Value Or _
sh1.Range("B" & i).Value <> sh2.Range("B" & i).Value Then

To compare two files with range A:AK(37 columns)

Much appreciated
 
Upvote 0
Hi @Vladee and welcome to MrExcel Forum.

I made the changes over my original macro. You must adapt it to your needs.

VBA Code:
Sub CompareExcelversionFiles()
  Dim File1 As Workbook, File2 As Workbook, File3 As Workbook
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
  Dim i As Long, lastLineHist As Long, j As Long
 
  ' Specify path and file names
  Set File1 = Workbooks.Open("C:\trabajo\files\File1.xlsx")             'is the old file
  Set File2 = Workbooks.Open("C:\trabajo\files\File2.xlsx")             'the new file
  Set File3 = Workbooks.Open("C:\trabajo\files\path_to_history.xlsx")
 
  ' Specify the name of the sheets to be compared
  Set sh1 = File1.Sheets("Sheet1")            'is the old file
  Set sh2 = File2.Sheets("Sheet2")            'the new file
  Set sh3 = File3.Sheets("SummaryResults")
  lastLineHist = sh3.Range("A" & Rows.Count).End(xlUp).Row + 1
 
  ' Loop through lines in both files
  For i = 1 To sh1.Range("A" & Rows.Count).End(xlUp).Row
    ' Checks if the line in version 2 is different from version 1
    For j = Columns("A").Column To Columns("AK").Column
      If sh1.Cells(i, j).Value <> sh2.Cells(i, j).Value Then
        ' Copy line from version 1 to history
        sh1.Rows(i).Copy sh3.Rows(lastLineHist)
        lastLineHist = lastLineHist + 1
        Exit For
      End If
    Next
  Next
 
  ' Close files
  File1.Close SaveChanges:=False
  File2.Close SaveChanges:=False
  File3.Close SaveChanges:=True
 
  MsgBox "Comparison complete. Modified lines have been copied to history."
End Sub

😇
 
Upvote 0

Forum statistics

Threads
1,223,954
Messages
6,175,603
Members
452,658
Latest member
GStorm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top