Search This Blog

Welcome to Machers Blog

Blogging the world of Technology and Testing which help people to build their career.

Monday, May 9, 2011

Comparing Two excel files and finding mismatch rows

Set objExcel = CreateObject ("Excel.Application")
objExcel.Visible = True

Set resultWb = objExcel.Workbooks.Add
Set resultWs = resultWb.Worksheets("Sheet1")

resultrow =1
Set objWorkbook1= objExcel.Workbooks.Open("C:\Excel\BI_TEST\TEST_090602_1.xls")
Set objWorkbook2= objExcel.Workbooks.Open("C:\Excel\BI_PROD\PROD_090602_1.xls")

Set objWorksheet1= objWorkbook1.Worksheets(1)
Set objWorksheet2= objWorkbook2.Worksheets(1)
Const xlAscending = 1'represents the sorting type 1 for Ascending 2 for Desc
Const xlYes = 1
'Set objRange =objWorksheet1.UsedRange 'which select the range of the cells has some data other than blank
'Set objRange2 = objWorksheet1.Range("A1") 'select the column to sort

'objRange.Sort objRange2, xlAscending, , , , , , xlYes

'Set objRange12 =objWorksheet2.UsedRange 'which select the range of the cells has some data other than blank
'Set objRange22 = objWorksheet2.Range("A1") 'select the column to sort

'objRange12.Sort objRange22, xlAscending, , , , , , xlYes

resultWs.Cells (resultrow, 1).Value ="Cell Address"
resultWs.Cells (resultrow, 2).Value ="Sheet1 Value"
resultWs.Cells (resultrow, 3).Value ="Sheet2 Value"

dim counter
counter = 0
For Each cell In objWorksheet1.UsedRange
If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
cell.Interior.ColorIndex = 3'Highlights in red color if any changes in cells
resultrow = resultrow+1
resultWs.Cells (resultrow, 1).Value =cell.Address
resultWs.Cells (resultrow, 2).Value =cell.VALUE
resultWs.Cells (resultrow, 3).Value= objWorksheet2.Range(cell.Address).Value
End If
counter = counter +1
Next

resultWb.SaveAs("C:\Excel\Result\Result_090602_1.xls")

No comments: