macro to compare two excel file.

[Diena]

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.

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

 

[DanteAmor]

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.​

  ' 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.​

  ' 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.​

    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.​

​

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
--------------​

 

[Diena]

Thank you so much, it really help me a lot. Thanks again

 

[Vladee]

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

 

[DanteAmor]

Hi @Vladee and welcome to MrExcel Forum.

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

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