Compare two Excel sheets

Ever wanted to compare two sheets in a excel file and store the results on another sheet? Or perhaps compare the cells and take one away from the other and store the result. In this article we will show you how you can do this.

What we need to get this script to work is an mrs file. This code might go at the end of a tables run and then open up the output and do the compare. Its actually very simple code, first of we need some variables.

Dim oExcel,oSheet1, oSheet2, oSheet3
Dim iRow,iCol
Dim iLastRow,iLastCol

iLastRow = 10
iLastCol = 10

So here we have some objects that are used to work with the excel sheet, oExcel will hold the workbook itself, oSheet1,oSheet2 and oSheet3 will hold the contents of our sheets we want to compare and then we have some variables to hold the values of where we are in the sheet itself ( iRow & iCol ). Finally we have two variables that store the last row and the last column to be scanned by our code. These values are hard coded , but you could write a function to find the last empty column and row if you so desired.

Next type the following,

Set oExcel = createobject("Excel.Application")

oExcel.Visible = True	

oExcel.Application.Workbooks.Open("D:DATASDLibraryCompareExcelCompare.xls")

Set oSheet1 = oExcel.Sheets["T1"]

Set oSheet2 = oExcel.Sheets["T2"]

Set oSheet3 = oExcel.Sheets["Compare"]

The first line of code allows us to connect to the excel object. You must of course have excel installed for this line to work. Next we make the excel book visible and connect our variables to the desired sheets and the sheet to store the results on ( compare ). This assumes that the sheet “Compare” already exists.

Next type the following,

oSheet1.Activate()

For iRow = 1 to iLastRow

	For iCol = 1 to iLastCol

		If ( oSheet1.cells[iRow][iCol].text <> oSheet2.cells[iRow][iCol].text ) Then
			oSheet3.cells[iRow][iCol].value = "N"
		Else
			oSheet3.cells[iRow][iCol].value = "Y"
		End If

	Next

Next

The first line in this bit of code selects the sheet that you want to start the compare with and then a loop selects the first row to be compared and loops us through the columns on the row and if there is a difference between the cells on sheet 1 and sheet 2 then a “N” is stored in that cell on the compare sheet. If they are the same then a “Y” is stored in that cell.

If you wanted to some sort of calculation on these cells , for example take one away from the other then you could do something like this,

If ( oSheet1.cells[iRow][iCol].text <> oSheet2.cells[iRow][iCol].text ) Then
	oSheet3.cells[iRow][iCol].value = oSheet1.cells[iRow][iCol].value - oSheet2.cells[iRow][iCol].value
Else
	oSheet3.cells[iRow][iCol].value = "Y"
End If

And then finally type this code,

'oExcel.ActiveWorkbook.Save()

'oExcel.Quit()

'Set oExcel = null

This code has been commented out so that you do not lose your changes. Un comment this code if you want to close and save the results.

1 thought on “Compare two Excel sheets”

  1. I truly wanted to post a brief word in order to appreciate you for all the superb concepts you are posting here. My rather long internet look up has finally been compensated with brilliant content to talk about with my co-workers. I would declare that we readers are rather blessed to live in a fantastic place with very many perfect individuals with very beneficial methods. I feel really privileged to have used your entire webpages and look forward to so many more pleasurable moments reading here. Thanks once more for everything. Contributed by 1daycarinsurance.ws.

Leave a Comment

%d bloggers like this: