Single Post

Header

Sunday, September 30, 2012

Excel Comparison using vb script

' open two excel sheets and compare them. give o/p as : match/mismatch
set xl=createobject("excel.application")
set wb1=xl.workbooks.open("D:\first.xls")
set sh1=wb1.worksheets(1)
r=sh1.usedrange.rows.count
c=sh1.usedrange.columns.count
'set wb2=xl.workbooks.open(inputbox("select execl sheet"))
set wb2=xl.workbooks.open("D:\second.xls")
set sh2=wb2.worksheets(1)
'msgbox r
for i=1 to r
for j=1 to c
temp1=sh1.cells(i,j).value
temp2=sh2.cells(i,j).value
if temp1<>temp2 then

sh1.cells(i,j).font.bold=true
sh1.cells(i,j).font.colorindex=50
sh1.cells(i,j).interior.colorindex=20
sh2.cells(i,j).font.bold=true
sh2.cells(i,j).interior.colorindex=20


end if
next
if i=r and j=c then
msgbox "Both excels are equal"
exit for
end if
next

if i<>r or j<>c then
msgbox "Both Excels are not equal"
end if
wb1.save
wb1.close
wb2.save
wb2.close


set sh1=nothing
set wb1=nothing
set sh2=nothing
set wb2=nothing
set xl=nothing

No comments:

Post a Comment