Quick test professional

QTP Tips QTP codes QTP Faqs and more

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

Advertisements

February 27, 2008 - Posted by | Excel Automation | , , , , , , , , ,

87 Comments »

  1. 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

  2. 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

  3. 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

  4. actually i9 am new to the QTP..But your Tips helped me a lot
    thanks

    Comment by sujana | May 21, 2008 | Reply

  5. 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

  6. Thanks

    Comment by nagaraju pokala | June 23, 2008 | Reply

  7. 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

  8. 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

  9. 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

  10. 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

  11. Thanks a lot. This site provides a lot of information for me.

    Comment by AMT | April 2, 2009 | Reply

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

  21. Please tell me how i can use it plz

    Comment by Dharam | March 26, 2010 | Reply

  22. 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

  23. 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

  24. 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

  25. 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

  26. 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

  27. Hello,

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

    THX for any Reply

    Comment by Mostan | August 9, 2010 | Reply

  28. 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

  29. this script doesn’t work

    Comment by Deepa | October 17, 2010 | Reply

  30. 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

  31. 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

  32. 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

  33. 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

  34. 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

  35. 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

  36. 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

  37. 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

  38. 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

  39. this is for all user

    Plz correct “

    Comment by Pankaj | June 7, 2011 | Reply

  40. can we give the tolerence values….

    Comment by ejiaj | June 14, 2011 | Reply

  41. 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

  42. 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

  43. Sheet Number/Name:Column name from row1: Row number: Old value: changed value

    Comment by Nisha | July 20, 2011 | Reply

  44. 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

  45. 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

  46. 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

  47. 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

  48. 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

  49. thank you mohan it worked fine for me

    Comment by raja viknesh | October 5, 2011 | Reply

  50. Can somebody paste full code please, I m new to VBscript. unable to run this above code.

    Comment by Sam | November 14, 2011 | Reply

  51. 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

  52. 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

  53. 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

  54. 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

  55. 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

  56. Hi

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

    Comment by Bala | March 6, 2012 | Reply

  57. 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

  58. dai karla gokamuka

    Comment by joseph | March 12, 2012 | Reply

  59. 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

  60. 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

  61. Hai,

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

    Regards
    Divakar

    Comment by Divakar | July 5, 2012 | Reply

  62. Beautiful code – so simple yet so powerful. Thanks for this!

    Comment by grits | July 16, 2012 | Reply

  63. can you help me ‘comparison of two excel sheets row by row using vbscript’

    Comment by praveen kumar | November 23, 2012 | Reply

  64. 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

  65. QTP Script Blog-http://funandknowledge.blogspot.com

    Comment by QTP Scripts | March 13, 2013 | Reply

  66. Thanks mohan ur script was very helpful….

    Comment by krish | April 8, 2013 | Reply

  67. 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

  68. Hi;

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

    Comment by ravi | September 19, 2013 | Reply

  69. 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

  70. Hello Mohan,

    Comment by Saisree | February 21, 2014 | Reply

  71. Thanks yuor script helped me alot

    Comment by anuhya | May 21, 2014 | Reply

  72. Unless you don’t save both the excel file through your script, it wont work.

    Comment by TestingQA | July 12, 2014 | Reply

  73. 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

  74. 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

  75. 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

  76. 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

  77. 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

  78. 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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: