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.