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:
Post a Comment