Compare 2 Excel sheets cell by cell

This code will open two excel sheet and compare each sheet cell by cell, if any changes there in cells , it will highlight the cells in red color in the first sheet.
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:Documents andSettingsmohan.kakarlaDesktopDocs1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:Documents and

Settingsmohan.kakarlaDesktopDocs2.xls”)

Set objWorksheet1= objWorkbook1.Worksheets(1)

Set objWorksheet2= objWorkbook2.Worksheets(1)

   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
       Else
           cell.Interior.ColorIndex = 0
       End If
   Next

set objExcel=nothing
Author: Mohan Kumar Kakarla

February 27, 2008 - Posted by quicktestprofessional | Excel Automation | AOM, Automation Testing, Compare 2 Excel sheets, Compare 2 Excel sheets cell by cell, Excel Automation, QTP, QTP Additional Faqs, QTP AOM, VBS, VBScripting

87 Comments »

Dear Mohan, Your work is excellent on QTP and thanks for that.
I have a sunngestion for you. Why dont you make a page and put the different methods of VBS and VB on it. That way new people who are not really familiar with programing concepts will be benefited big time.

Comment by Pari | March 5, 2008 | Reply
- Dear Mohan, Your work is excellent on QTP and thanks for that
  
  Comment by ravindra | May 24, 2012 | Reply
Thanks for the sugession.very soon I will do that.

Comment by quicktestprofessional | March 6, 2008 | Reply
- Could you please enhance the script to compare excel sheets with multiple sheets.
  
  many thanks
  maruti
  
  Comment by maruti | May 8, 2009 | Reply
- Hi Mohan,
  
  First of thanks to you for your job, because it helps us lot to develop carrer in the QTP.
  
  My Quastation is:
  I have a multiple excel file and i want to replace value of the File. menas open the first excel file and compare the value(Like “A”) in sheet if value (“A”) is present ion the file replace it with the value (“B”, do same things for all file inside the folder.
  
  Comment by Nitin Singh | December 15, 2011 | Reply
- I am new to qtp and i am using version 11. I have no idea on where to begin to make the above code work. I am getting an error message on the first line. can you help me please. can u please send to my email address?
  
  Comment by tee | October 8, 2012 | Reply
Mohan,

Your help for QTP is great.
Thank you for all your hard work and this site provides a lot of information about QTP.

Thanks,
Pradeep

Comment by Pradeep | March 6, 2008 | Reply
actually i9 am new to the QTP..But your Tips helped me a lot
thanks

Comment by sujana | May 21, 2008 | Reply
Hi Mohan,

Thanks for providing great information about QTP.

I was trying to use your code for comparing two XL spread sheets. But I have noticed that this code doesn’t validate each column value. How do we make this happen?

Thanks again,
Kumar

Comment by Kumar | May 26, 2008 | Reply
Thanks

Comment by nagaraju pokala | June 23, 2008 | Reply
I would like to compare two different excel files (source-A with destination-B) and store the difference information in another excel file called Results-C.

How should I do it… you help is appreciated.

Thanks
Prasad

Comment by Prasad | December 1, 2008 | Reply
Prasad,
Here is the code to compare two different excel files (source-A with destination-B) and store the difference information in another excel file called Results-C.

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:\Documents and Settings\kmohankumar\Desktop\1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:\Documents and Settings\kmohankumar\Desktop\2.xls”)
set newWorkbook=objExcel.Workbooks.Add
Set objWorksheet1= objWorkbook1.Worksheets(1)

Set objWorksheet2= objWorkbook2.Worksheets(1)
Set objNewWorksheet= newWorkbook.Worksheets(1)

For Each cell In objWorksheet1.UsedRange
If cell.Value objWorksheet2.Range(cell.Address).Value Then
objNewWorksheet.Range(cell.Address).Value=cell.Value
objNewWorksheet.Range(cell.Address).Interior.ColorIndex = 3’Highlights in red color if any changes in cells
End If
Next

set objExcel=nothing

Comment by quicktestprofessional | December 4, 2008 | Reply
- hi mohan
  Set objExcel = CreateObject(“Excel.Application”)
  objExcel.Visible = True
  Set objWorkbook1= objExcel.Workbooks.Open(“C:\Documents and Settings\kmohankumar\Desktop\1.xls”)
  Set objWorkbook2= objExcel.Workbooks.Open(“C:\Documents and Settings\kmohankumar\Desktop\2.xls”)
  set newWorkbook=objExcel.Workbooks.Add
  Set objWorksheet1= objWorkbook1.Worksheets(1)
  
  Set objWorksheet2= objWorkbook2.Worksheets(1)
  Set objNewWorksheet= newWorkbook.Worksheets(1)
  
  For Each cell In objWorksheet1.UsedRange
  If cell.Value objWorksheet2.Range(cell.Address).Value Then —this line will make error message?
  objNewWorksheet.Range(cell.Address).Value=cell.Value
  objNewWorksheet.Range(cell.Address).Interior.ColorIndex = 3′Highlights in red color if any changes in cells
  End If
  Next
  
  Comment by quanwen | December 2, 2011 | Reply
hi mohan,

I want to know how to abort from q QTP script. i.e the execution should be stopped..and script should be aborted

Comment by ravi | December 8, 2008 | Reply
hi mohan,

Is there any way we can find the scripting errors in a web application using QTP scripting

Comment by ravi | December 8, 2008 | Reply
Thanks a lot. This site provides a lot of information for me.

Comment by AMT | April 2, 2009 | Reply
Hi Mohan,

I am trying to compare 2 excels row by row, by importing them in to the DataTable. I vaguely remember that the best way to do this is to get all the data in to arrays & then compare. Any pointers please?

Comment by Chandrasekaran | June 1, 2009 | Reply
VB 6.0 sample codes for comparing 2 excel file

Hello Mohan,

Need your help, do you have some sample codes in VB6.0 for comparing 2 excel files?
Location of 2 files & the specific sheets & columns were already predefined at the script or in the .ini form.

functionality:
program will compare if there are identical(not different) data found on the defined column.
a window will pop showing the identical data on the list box.
if no identical found, window will still pop but showing a label “No identical data found”

ex. file 1, sheet1, column 3 will be compared to file 2, sheet1, column 3

thank you very much,

Roan

Configuration: Windows XP Internet Explorer 6.0

Comment by roan | August 2, 2009 | Reply
Hi mohan,
thanks for your excel automation it really helped me a lot kindly let me knw how to convert this piece of code in function and take filename in runtime as passing the function argument instead of giving the path of the address (“C:\result\lotexpt.xls”)

code

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:\result\lotexpt.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:\result\lotact.xls”)
set newWorkbook=objExcel.Workbooks.Add
Set objWorksheet1= objWorkbook1.Worksheets(1)
Set objWorksheet2= objWorkbook2.Worksheets(1)
Set objNewWorksheet= newWorkbook.Worksheets(1)

For Each cell In objWorksheet1.UsedRange
If cell.Value objWorksheet2.Range(cell.Address).Value Then

objNewWorksheet.Range(cell.Address).Value=cell.Value
objNewWorksheet.Range(cell.Address).Interior.ColorIndex = 3′ Highlights in red color if any changes in cells’
else
objNewWorksheet.Range(cell.Address).Interior.ColorIndex=0
End If
Next
set objExcel=nothing

thanks

Comment by subash | September 11, 2009 | Reply
Hi Mohan, Plz mail a copy of queries with answers to my mail-id , as this would help me a lot in real time.

Comment by Hema | September 14, 2009 | Reply
I tried above core with 2 excel sheets ,

but it was faling like

For Each cell In objWorksheet1.UsedRange

above line So pls let me know what changes i need to made

Comment by nanibabu | October 23, 2009 | Reply
what i was looking for i got it really thanks but i don’t know how to use it can i use it as a macro because i have two diffrent excel sheet where two data are almost same data but some time its diffrent so i just want to create one template where i just need to copy paste the data and by clicking on that mcro switch it should show the diffrence in red color front .
my sheet1 name is APPLICATION and SHEET2 is Source
so the result should show in the application sheet

Comment by Arpan | November 9, 2009 | Reply
I need you help .
I am a newbie in using excel and now i have a problem regarding using macro in the vbe coding .

I have to do comparing within two worksheets and i have to display the differences between the two worksheets in a new worksheets .
All these 3 worksheets is in the same workbook.
The two worksheets that i need to compare is namely Par Q1 and Par Q2.
I have to compare the information from row 16 to row 82 in Par Q1 and fromn row 17 to row 82 in Par Q2 ( both worksheet only using column 2 to compare information) . The differences found will be shown in a new worksheets name Par Miss ( which is created by me ).

Hope you can get back to me as soon as possible
Reply me in my email .
Million Thanks 🙂

Comment by Chuan | December 10, 2009 | Reply
Hi Mohan,
I was going through the code for excel compare. The system throws an error. I am new to this . Kindly help.

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:\Documents and Settings\kmohankumar\Desktop\1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:\Documents and Settings\kmohankumar\Desktop\2.xls”)
set newWorkbook=objExcel.Workbooks.Add
Set objWorksheet1= objWorkbook1.Worksheets(1)

Set objWorksheet2= objWorkbook2.Worksheets(1)
Set objNewWorksheet= newWorkbook.Worksheets(1)

For Each cell In objWorksheet1.UsedRange
If cell.Value objWorksheet2.Range(cell.Address).Value Then
objNewWorksheet.Range(cell.Address).Value=cell.Value
objNewWorksheet.Range(cell.Address).Interior.ColorIndex = 3 ‘Highlights in red color if any changes in cells
End If
Next

set objExcel=nothing

The error that the code throws is :
Error: Expected Then

Comment by Sugandhesh | January 20, 2010 | Reply
- hi sugandesh
  
  just make a change in above code as
  
  if cell.range objWorksheet2.Range(cell.Address).value then
  
  it will work fine
  hope this is the error i think,if it is wrong let me know
  
  Comment by anil | April 14, 2011 | Reply
  - Comment by anil | April 14, 2011
  - if cell.range objWorksheet2.Range(cell.Address).value then
    
    in above code after cell.range just type not equal to symbol as one lessthan and one greater than
    
    Comment by anil | April 14, 2011
Hi Mohan,

I followed the same code which u have given for 2 excel sheet comparsion and results in the 3 sheet. I see an error Expected Then

Comparing the two excel reports
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:\Documents and Settings\c057898\Desktop\Expected_report”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:\Documents and Settings\c057898\Desktop\Retreived_report”)
Set newWorkbook=objExcel.Workbooks.Add
Set objWorksheet1= objWorkbook1.Worksheets(1)
Set objWorksheet2= objWorkbook2.Worksheets(1)
Set objNewWorksheet= newWorkbook.Worksheets(1)
For Each cell In objWorksheet1.UsedRange
If cell.Value objWorksheet2.Range cell.Address).Value Then
objNewWorksheet.Range(cell.Address).Value=cell.Value
objNewWorksheet.Range(cell.Address).Interior_ColorIndex=3’Highlights in red color if any changes in cells’
EndIf
Next
Set objExcel=nothing

Expected then in this line is the error
If cell.Value objWorksheet2.Range cell.Address).Value Then

Please let me know what is the problem one more thing was my excel sheet has 6 tabs ( sheets ) in the file .Does it work for my test. Thankyou

Lakshmi

Comment by lakhsmi | February 5, 2010 | Reply
Please tell me how i can use it plz

Comment by Dharam | March 26, 2010 | Reply
I would like to know how to add chart to excel through vb script. When i write a syntax it gives me error message

(wsheet.shapes.addchart 92). This does not work. My script is like this

Set xlapp=createobject(“Excel.application”)
xlapp.visible=True
set wbook=xlapp.workbooks.open(“D:\Jitu.xls”)
set wsheet=wbook.worksheets(1)
rc=wsheet.usedrange.rows.count
msgbox rc

P=0
f=0

for i=2 to rc
Status=wsheet.cells(i,2)
If status=”Passed” then
p=p+1
else
wsheet.cells(i,2).font.colorIndex=3
f=f+1
End if
Next

wsheet.cells(2,3)=P
wsheet.cells(2,4)=F

wsheet.cells.Range(“C1:D2”).select
wsheet.addchart 92

wbook.save
wbook.close
xlapp.quit
set wbook=Nothing
set Xlapp=Nothing

Please help

Comment by Jithendar | April 1, 2010 | Reply
Try this.

Dim objExcel
Dim objWorkbook1
Dim objWorkbook2
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:\2.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:\1.xls”)
set newWorkbook=objExcel.Workbooks.Add
Set objWorksheet1= objWorkbook1.Worksheets(1)

Set objWorksheet2= objWorkbook2.Worksheets(1)
Set objNewWorksheet= newWorkbook.Worksheets(1)

For Each cell In objWorksheet1.UsedRange
If cell.Value objWorksheet2.Range(cell.Address).Value Then
objNewWorksheet.Range(cell.Address).Value=cell.Value
objNewWorksheet.Range(cell.Address).Interior.ColorIndex = “3” ‘Highlights in red color if any changes in cells
End If
Next

set objExcel=nothing

Comment by Generaluse | May 6, 2010 | Reply
Hi Mohan,

I am unable to read the xls file exported from a web page using the ADODB Object. It throws an error saying. The External filw fomar is different.

Any suggestions

Comment by Sree | May 15, 2010 | Reply
Hi Mohan,
I want to write a macro in VBScript in MS-Excel.
This is my requirement,
I have to compare the values of 2 columns in a sheet and if two values matches together, that cells should be removed, if the value in column in A is not found in B, then shade the Cells in A with a color, If the Values in Column B is not found in Column A, then shade them in different color.

Please help me..

Thank you,
Sendil Kumar RS.

Comment by Sendil | June 11, 2010 | Reply
hi mohan,

how can we compare two excel files A and B
where A having limited data and b having multiple data.
example In A 10 colums with different values in each column, and 10 columns in B with the same values of A but more than once (that value contains no of times in B)

Rasheed

Comment by Rasheed | June 22, 2010 | Reply
Hello,

how can i compare two Microsoft Office Access .*mdb?

THX for any Reply

Comment by Mostan | August 9, 2010 | Reply
hi.. i’m still new in vbscript..
i’m using ur code and when i run it show all the code back..
am i missing something? like how to call/run that script?

Comment by zeera | August 24, 2010 | Reply
this script doesn’t work

Comment by Deepa | October 17, 2010 | Reply
The test run cannot continue due to a syntax error.

Expected ‘Then’

Line (15): “If cell.Value objWorksheet2.Range(cell.Address).Value”.

Comment by Deepa | October 17, 2010 | Reply
Hi Mohan,

You are excellent mohan and i am infront of u to know about how to connect to Oracle DB using QTP. Is there any function please share with me. And u know this will help me a lot to querry and get some data from database and to save into excel sheet.

Thanks
Dwaraka

Comment by Dwaraka | November 11, 2010 | Reply
hi all,
i need to compare excel sheet having lakhs of data in fraction of time..can i get such code which takes minimum time to compare two excel sheets

Comment by priyanka | November 18, 2010 | Reply
Hi,

i want to know how to open “C:\Windows\System32\drivers\etc” >” hosts” file in notepad , as this file is un known type i would need to open it in Notepad format

thanks
Vishu

Comment by Vishnu | December 16, 2010 | Reply
Thanks for your info on Excel Comparision.

I have Tried to compare two excel sheets with numbers it works well.But where as it is faliling to Comparing the characters with case insensitive..say for ex: in Sheet1 i have data for col1 as Kareem where as in sheet2 i have KaReEm then it is failing to compare. Could you please refine and publish the script.

Thanks in Advance.
Kareem

Comment by kareem | February 4, 2011 | Reply
I need the formula to compare 2 results say the result in C1 was 1.9 and D1 was 1.964 what is the similarity I have an answer as 101.68% but not sure how to get there?

Comment by Elaine Rodel | April 10, 2011 | Reply
i make registration form where u can create use id and password which store in excel sheet (name of sheets Password1) but i am unable to compare value is shows
Run-time error ‘424’:
object required

Username = txtUsername.Value
Password = txtPassword.Value

ActiveWorkbook.Sheets(“Password1”).Activate
If Username = “sa” And Password = “Password” Then
UserForm3.Show
UserForm1.Hide

ElseIf StrComp(password1.Offset(0, 2).Text, txtUsername.Value, vbTextCompare) = 0 And StrComp(password1.Offset(0, 1).Text, txtPassword.Value, vbTextCompare) = 0 Then
UserForm3.Show
UserForm1.Hide
Else

MsgBox (“You have entered an invalid username or password. Please press the back button, enter the correct details and try again. Don’t forget that the password is case sensitive. Forgotten your password? Click here!”)
End If
End Sub

Comment by anand | April 30, 2011 | Reply
Hi Mohan,
I want to get a particualar word from notepad in the paragraph for my automation.
I am using qtp10. Can u please help me.
Thanks in Advance.

Comment by Priya | May 13, 2011 | Reply
Hi Mohan
How to use filter option in Excel while automation.How to make it enable. can u please provide the same with an example.
Thanks in Advance.

Comment by Priya | May 13, 2011 | Reply
this is for all user

Plz correct “

Comment by Pankaj | June 7, 2011 | Reply
can we give the tolerence values….

Comment by ejiaj | June 14, 2011 | Reply
HI Mohan,

How can we converting excel to XML using qtp? can you please help me in this

Comment by Sham | July 7, 2011 | Reply
Hi Mohan,
Here is the scenario that I need to test
• I have two folders, Folder 1 and Folder2
• Folder 2 is mirror of Folder 1 in terms of number of files and name of the files e.g. if Folder1 has File1, File2, File3 then Folder 2 will also have File1, File 2 and File3
• List of xls in Folder1 is available in a separate excel sheet say Name.xls
• I have to compare File1 from Folder1 to File1 from Folder2
• I have to compare the entire workbook (cell to cell comparison of each sheet in a file)
• I have to repeat this for all the files existing in Folder1 with it’s respective file in Folder2
• The differences in the file has to be capture in separate excel file e.g. File1 in Result1, File2 in Result2 n so on
• Also Result file should capture both the values and the specified info e.g :: : Old value: changed value

Please help!

Comment by Nisha | July 20, 2011 | Reply
Sheet Number/Name:Column name from row1: Row number: Old value: changed value

Comment by Nisha | July 20, 2011 | Reply
Hi Mohan
How to Compare two columns from opened workbook.
currenty i have excel open and want to compare value from opened workbook with saved one…
how can we do this?

please suggest any solution for this.

Thanks in advance.

Comment by jayanth | August 19, 2011 | Reply
I’m confused! I have i think added this macro to excel, tried to run it and nothing happens… Excel does not even open according to this post its supposed to open 2 docs? Or does it mean it scans two worksheets on an already open document?

My head seriously feels battered!

Thanks

Darren

Comment by Darren | September 9, 2011 | Reply
hi mohan my name is sreeanth y i have one exmple about the vb and file system object

1 my qustion is who to fatch to ptiqular value in the vb and file system

2 give me some exalmple of on that and give me some more examples of vb scripting

regards

y sreeanth i will waithing for ur reply

Comment by y sreenath | September 14, 2011 | Reply
hi Mohan Kumar Kakarla
thanks for your awesome help…it really helps me a lot. but just a query. Mine Scenario is First excel file sheet available list of stocks. 2 nd excel sheet which is built manually by user and i Need to compare both. so in new excel file sheet1 , first column will be column from first file, second column will be matching value from second file. third column will be status of matched or unmatched. just the condition is the result file should show all the records whether matched or unmatched.. can you please suggest on the same.

Comment by Vinay Mehta | September 15, 2011 | Reply
Hi
iam shannu

sir I need to compare an excel sheet which has 4 columns with another 300 file in a folder which has 6 colums and the result should be printed in a separate file with 6 colums
for example i have a file file1 and folder velo which has 300 files

855.0094808 728.589158 777.8768 33.08248714 66 16,1 1000 file1
678.0094808 234.589158 787.8768 45.08902714 23 16,1 1000

855.0094808 728.589158 777.8768 33.08248714 Velo folder files

855.0094808 728.589158 777.8768 33.08248714 66 16,1 1000 Result

Comment by shannu | September 28, 2011 | Reply
thank you mohan it worked fine for me

Comment by raja viknesh | October 5, 2011 | Reply
Can somebody paste full code please, I m new to VBscript. unable to run this above code.

Comment by Sam | November 14, 2011 | Reply
Firstly, I enter first name and last name, hit search button, to find data in another web page with address and other details for that user. Secondly, I want to compare address populated in the fields of that web page with the address present in the table existing at back end for nearly 10k users.
Please suggest how to proceed

If any thing is missing,please let me know

Comment by Krishna | December 19, 2011 | Reply
In the test report, plan to use QTP regression testing, the status quo is front have the content of the create reports to excel function, and then through the database query results and the content of the report contrast, I now have the idea of a,
A: database query export to excel, then front and export excel, have two excel than operation,
Do a template excel file is empty, every time in contrast to a excel file template, deduces the ado data according to the file data format excel into, but now does not know how to realize, consult everybody, thank you very much
this is my email:quanwen20006@163.com,hope your letter

Comment by quanwen | December 28, 2011 | Reply
8. write the input given by user in to text file
1. name
2.location
3.ID

any one helpe me the ans

Comment by sreenath y | January 11, 2012 | Reply
I have this done in VB. I pretty much used exactly the same code, but I made some slight tweeks. I am getting an error
“COMException was unhandled”
“Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))”
The error occurs in the If statement

Module Module1

Sub Main()
Dim objExcel
Dim objWorkbook1
Dim objWorkbook2
Dim objWorksheet1
Dim objWorksheet2
‘instantiated objects

objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
objWorkbook1 = objExcel.Workbooks.Open(“C:\Users\armstrs\Documents\iyrul\PFM 12original.xlsx”)
objWorkbook2 = objExcel.Workbooks.Open(“C:\Users\armstrs\Documents\iyrul\PFM 12.xlsx”)

objWorksheet1 = objWorkbook1.Sheets.Item(1)

objWorksheet2 = objWorkbook2.Sheets.Item(1)
‘defined objects

‘runs for loop to compare worksheets
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
Else
cell.Interior.ColorIndex = 0
End If
Next

objWorkbook1.Workbooks.Save()
objWorkbook1.Workbooks.Close()
objWorkbook2.Workbooks.Save()
objWorkbook2.Workbooks.Close()
objExcel = Nothing
‘Author: Mohan Kumar Kakarla
End Sub

Private Sub objExcel()
Throw New NotImplementedException
End Sub

End Module

Comment by Shane Armstrong | January 20, 2012 | Reply
- Thank you very much by the way
  
  Comment by Shane Armstrong | January 20, 2012 | Reply
For all those asking about the error they received in Post # 8. Here is my update: (make sure you update your excel path)
There might be a better fix, but just adding my 2 cents since we are all trying to help and learn.

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“h:\qa\qtp\Tony1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“h:\qa\qtp\Tony2.xls”)
set newWorkbook=objExcel.Workbooks.Add
Set objWorksheet1= objWorkbook1.Worksheets(1)
Set objWorksheet2= objWorkbook2.Worksheets(1)
Set objNewWorksheet= newWorkbook.Worksheets(1)
For Each cell In objWorksheet1.UsedRange
If cell.Value objWorksheet2.Range(cell.Address).Value Then
objNewWorksheet.Range(cell.Address).Value=objWorksheet1.Range(cell.Address).value
objNewWorksheet.Range(cell.Address).Interior.ColorIndex = 3
Else
objNewWorksheet.Range(cell.Address).Interior.ColorIndex = 0
End If
Next

set objExcel=nothing

Comment by Tony | March 1, 2012 | Reply
Hi

Can u improve this code to highlight both the excel sheets, where there are differences?

Comment by Bala | March 6, 2012 | Reply
Hi

Can u pls. Improve this code by highlighting the differences in both the excel files?
If one excel sheet has 10 rows and other has 5 rows, then the code will compare only the 5 rows, then other rows will not be compared?
Or this code will work only in cases where usedrange is same in both excel files?

Comment by Bala | March 6, 2012 | Reply
dai karla gokamuka

Comment by joseph | March 12, 2012 | Reply
Hi Mohan,
Can you help me in this scenario. I have a big excel with columns such as loanid, balance, fee amount, city and many other columns. I will write code/script based on requirements. Like if city is newyork and balance is xxx then borrower should charge the fee amount.. What I want to do is all requiremetns write in script/vbs file and take data from test sheet and compare. if the data is htis and that then i will want to fill the excel sheet respectively. Can you guide me in this approach.

Thanks

Comment by Ehtesham | April 19, 2012 | Reply
hi i’m new with this.. one question.. how do you compare an excel sheet and an html source code? is there a code that will check if the data in the excel sheet is existing in the html source code? Thank you 🙂

Comment by Kayle | June 29, 2012 | Reply
Hai,

Am new to vbs program, without QTP is it work through notepad.

Regards
Divakar

Comment by Divakar | July 5, 2012 | Reply
Beautiful code – so simple yet so powerful. Thanks for this!

Comment by grits | July 16, 2012 | Reply
can you help me ‘comparison of two excel sheets row by row using vbscript’

Comment by praveen kumar | November 23, 2012 | Reply
Hello Mohan,

Need your expert help, any reason why the below codes in VB6.0 for comparing 2 excel files gives an error ?

Thanks
Regards
anand

Set xlApp = CreateObject(“Excel.Application”)
xlApp.Visible = True

Set objWorkbook1 = objExcel.Workbooks.Open(“C:\BFG\Test1.xls”)
Set objWorkbook2 = objExcel.Workbooks.Open(“C:\BFG\Test2.xls”)

Set objWorksheet1 = objWorkbook1.Worksheets(1)

Set objWorksheet2 = objWorkbook2.Worksheets(1)

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
Else
cell.Interior.ColorIndex = 0
End If
Next

Set objExcel = Nothing

Comment by Anand Naidoo | March 11, 2013 | Reply
QTP Script Blog-http://funandknowledge.blogspot.com

Comment by QTP Scripts | March 13, 2013 | Reply
Thanks mohan ur script was very helpful….

Comment by krish | April 8, 2013 | Reply
If Guys…
I tried “If cell.Value = objWorksheet2.range(cell.Address).Value Then”
and it worked for me… Hope this helps…..

Comment by Narasimha | August 3, 2013 | Reply
Hi;

i have doubt in comparision the text of excel file with notepad.

Comment by ravi | September 19, 2013 | Reply
Thank you so much for your script! You just saved my life and me from several days of manual comparing 🙂

Comment by Mathew | October 15, 2013 | Reply
Hello Mohan,

Comment by Saisree | February 21, 2014 | Reply
Thanks yuor script helped me alot

Comment by anuhya | May 21, 2014 | Reply
Unless you don’t save both the excel file through your script, it wont work.

Comment by TestingQA | July 12, 2014 | Reply
Hi
I have one workbook and has three different sheets a,b and c. a has all data b and c has the same coulmn headings as a but b has only some data extracted from a and c has extracted less data than b. I want a code to link b and c with a so that whenever I update data in a it will automatically update in b and c. at the moment i am using following code
Sub auto_open()
‘
‘ Macro1 Macro
‘

Sheets(“data”).Select
Range(“A1009:I1200”).Select
Selection.Copy
Sheets(“last_pay_logcabin”).Select
Range(“a308:i1200”).Select
ActiveSheet.Paste
Sheets(“since_loan”).Select
Range(“a104:i1200″).Select
ActiveSheet.Paste
End Sub
is it possible to update sheets b and c whenever insert some data in sheet a
sheet a=”data
sheet b=”last_pay_logcabin”
sheet c=”since_loan”

Comment by Narendra | August 20, 2014 | Reply
hi mohan..

I am new to QTP.. i need the qtp script to get input from excel and then check the corresponding price details in dynamic webtable and update the price in same excel.. do you help how we create code for this scenario… kindly do the needful.

Comment by sathya | September 26, 2014 | Reply
what if i want to print the cell address where the match is not found?can we do it by using for loop here?

Comment by mamta | July 1, 2015 | Reply
The problem with this code is it does not work if the order of the columns or rows is different (which is very likely to happen in real-world scenarios).

Here is the VB Script I ofen use to address this problem.

‘ This programs assumes that first column in both the tables is a key column such as primary key with no duplicates
‘ order of the columns or rows in both tables in not a problem, data cells can be compared in any order but the key should match
‘ If there are some rows or columns in table1 which does not exist in table2, then this script catches them, but does not catch additional rows or colums from table2 which do not exist in table1

Set objExcel = CreateObject(“Excel.Application”)

Set objWorkbook2= objExcel.Workbooks.Open(“P:\file2.xlsx”)
Set objWorkbook1= objExcel.Workbooks.Open(“P:\file1.xlsx”)

‘objExcel.Application.Visible = True

Set objWorksheet1= objWorkbook1.Worksheets.item(1)
Set objWorksheet2= objWorkbook2.Worksheets.item(1)

Set objResultWorkbook = objExcel.Workbooks.Add()
Set objResultWorksheet = objResultWorkbook.Worksheets(“Sheet1”)
Set objLogWorksheet = objResultWorkbook.Worksheets(“Sheet2”)

x = objWorksheet1.Cells(1,1).CurrentRegion.Columns.count

flag1 = false
flag2 = false
flag3 = false

For i = 1 To objWorksheet1.Cells(1,1).CurrentRegion.Rows.Count
For j = 1 To objWorksheet2.Cells(1,1).CurrentRegion.Rows.Count
If objWorksheet1.Cells(i, 1) = objWorksheet2.Cells(j, 1) then
flag1 = true
For k = 1 To objWorksheet1.Cells(1,1).CurrentRegion.Columns.Count
For l = 1 To objWorksheet2.Cells(1,1).CurrentRegion.Columns.Count
If objWorksheet1.Cells(1, k) = objWorksheet2.Cells(1, l) then
flag2 = true
objResultWorksheet.Cells(i, k) = objWorksheet1.Cells(i, k)
objResultWorksheet.Cells(i, k+x+1) = objWorksheet2.Cells(j, l)
If objWorksheet1.Cells(i, k) = objWorksheet2.Cells(j, l) then
objResultWorksheet.Cells(i, k).Interior.ColorIndex = 35
objResultWorksheet.Cells(i, k+x+1).Interior.ColorIndex = 35
Else
objResultWorksheet.Cells(i, k).Interior.ColorIndex = 45
objResultWorksheet.Cells(i, k+x+1).Interior.ColorIndex = 45
End If
End If
Next
If flag2 = false then
objResultWorksheet.Cells(i, k) = objWorksheet1.Cells(i, k)
objResultWorksheet.Cells(i, k).Interior.ColorIndex = 45
objResultWorksheet.Cells(i, k+x+1).Interior.ColorIndex = 45
End If
flag2 = false
Next
End If
Next

If flag1 = false then
For y = 1 To objWorksheet1.Cells(1,1).CurrentRegion.Columns.Count
objResultWorksheet.Cells(i, y) = objWorksheet1.Cells(i, y)
objResultWorksheet.Cells(i, y).Interior.ColorIndex = 45
Next
For z = 2 To objWorksheet2.Cells(1,1).CurrentRegion.Columns.Count
objResultWorksheet.Cells(i, x+z).Interior.ColorIndex = 45
Next
End If
flag1 = false
Next
‘objResultWorksheet.Columns.AutoFit

colname = “Sheet1 columns – ”
objLogWorksheet.Activate
matchFlag = true
For a = 1 To objResultWorksheet.Cells(1,1).CurrentRegion.Rows.Count
objLogWorksheet.Cells(a, 1) = objResultWorksheet.Cells(a, 1)
For b = 1 To objResultWorksheet.Cells(1,1).CurrentRegion.Columns.Count
If objResultWorksheet.Cells(a, b).Interior.ColorIndex = 45 then
colname = colname & ” ‘” & objResultWorksheet.Cells(1, b) & “‘”
matchFlag = false
End If
Next
If matchFlag = false then
objLogWorksheet.Cells(a, 2).Value = “Data mismatch : Refer to ” & colname & ” for more details”
objLogWorksheet.Cells(a, 2).Interior.ColorIndex = 45
else
objLogWorksheet.Cells(a, 2).Value = “Data matches”
objLogWorksheet.Cells(a, 2).Interior.ColorIndex = 35
End If
colname = “”
matchFlag = true
Next

datestamp = DatePart(“yyyy”,Date) & Right(“0” & DatePart(“m”,Date), 2) & Right(“0” & DatePart(“d”,Date), 2)
timestamp = Right(“0” & Hour(Now), 2) & Right(“0” & Minute(Now), 2) & Right(“0” & Second(Now), 2)

objResultWorkbook.SaveAs “P:\Results_” & datestamp & “_” & timestamp & “.xlsx”

For Each objWindow In objExcel.Windows
objWindow.Activate
Set WBook = objExcel.ActiveWorkbook
‘WBook.Saved = True
WBook.Close
Next

objExcel.Quit
Set objExcel = Nothing

Wscript.Quit

Comment by Medabalimi | August 20, 2015 | Reply
Can Anyone plz post the code to Compare only singe row i.e. 1st row from two different Excel Files.

Comment by Vaibhav | October 8, 2015 | Reply
Hi sir,
I was trying very hard but I could do it and need your help sir.

I am looking for a QTP vbscript code to compare two excel sheet for only specific column and upto 5 rows

For example:

In excel-1 sheet-1 I want to compare Column E with excel-2 sheet-1 with same column E for limited rows(like first 5 rows)

Comment by Ramesh Puluri | April 23, 2016 | Reply