Sunday, March 29, 2009
QTP Script 31 - How to compare range of cells in two excel files and highlight the cells with different values in the first file?
(追記) (追記ここまで)
myrange=inputbox("enter range of cells e.g. A1:A5")
Excel object is being created here
Set Exl_Obj = CreateObject("Excel.Application")
Exl_Obj.Visible = True
Two files sac1 and sac2 are opened
Set WB_Obj_1= Exl_Obj.Workbooks.Open("C:\sac1.xls")
Set WB_Obj_2= Exl_Obj.Workbooks.Open("C:\sac2.xls")
Cells in the first sheet of both the files are compared
Set WS_Obj_1= WB_Obj_1.Worksheets(1)
Set WS_Obj_2= WB_Obj_2.Worksheets(1)
Exl_Obj.workbooks("sac1.xls").save
Exl_Obj.workbooks("sac1.xls").close
Exl_Obj.workbooks("sac2.xls").save
Exl_Obj.workbooks("sac2.xls").close
Exl_Obj.Application.Quit
set Exl_Obj=nothing
Excel object is being created here
Set Exl_Obj = CreateObject("Excel.Application")
Exl_Obj.Visible = True
Two files sac1 and sac2 are opened
Set WB_Obj_1= Exl_Obj.Workbooks.Open("C:\sac1.xls")
Set WB_Obj_2= Exl_Obj.Workbooks.Open("C:\sac2.xls")
Cells in the first sheet of both the files are compared
Set WS_Obj_1= WB_Obj_1.Worksheets(1)
Set WS_Obj_2= WB_Obj_2.Worksheets(1)
For Each cell In WS_Obj_1.Range(myrange)
If cell.Value WS_Obj_2.Range(cell.Address).Value Then
cell.Interior.ColorIndex = 6
Else
cell.Interior.ColorIndex = 0
End If
Next
Exl_Obj.workbooks("sac1.xls").save
Exl_Obj.workbooks("sac1.xls").close
Exl_Obj.workbooks("sac2.xls").save
Exl_Obj.workbooks("sac2.xls").close
Exl_Obj.Application.Quit
set Exl_Obj=nothing