Forum Discussion

msal4434's avatar
msal4434
Occasional Contributor
8 years ago

Comparing 2 xls files with the specific column.

Hi There,   I need a little help to compare attached sheets A and B.   I am able to read A and B files separately and can display the output.     Read value  A1, D1 from A.xls if A1 value avai...
msal4434's avatar
msal4434
Occasional Contributor

Thank you for your reply.

 

We changed excel file name and try to run the script we are getting the attached error.

 

Can you please let us know where we are going wrong...?

 

If I want to verify specific rows and columns do I need to change this syntax " Cell.Value = WSO2.Range(Cell.Address).Value"

 

 

For Eg. If I want verify A1 and F1 in sheet 1 = A1 and G1 in sheet 2 How should I change the syntax.

shankar_r's avatar
shankar_r
Community Hero
8 years ago

Hi,

 

I was doing the same kind of validation long back using Excel VBA.In Test Complete, I used to compare the excel file column and rows using below code. It was working fine for me. Maybe you could try this.

 

Function CompareExcelCells()

      Dim excelObject
      Dim WB_A,WB_B,WS_A,WS_B
      Dim path_A,path_B
      Dim A_RowCount,iR
      
      path_A = "D:\************************\A.xlsx"
      path_B = "D:\************************\B.xlsx"
      
      Set excelObject = Sys.OleObject("Excel.Application")
      'Open Excel files
      Set WB_A = excelObject.Workbooks.Open(path_A)
      Set WB_B = excelObject.Workbooks.Open(path_B)
      
      Set WS_A = WB_A.Sheets("Sheet1")
      Set WS_B = WB_B.Sheets("Sheet1")
      
      A_RowCount = WS_A.UsedRange.Rows.Count
      
      For iR = 1 To A_RowCount
            
            If(WS_A.Cells(iR,1).Value = WS_B.Cells(iR,1).Value) Then
                  If(WS_A.Cells(iR,4).Value = WS_B.Cells(iR,5).Value) Then
                        Log.Message("Value matched " & WS_A.Cells(iR,1).Value)
                        WS_A.Cells(iR,1).Interior.ColorIndex = 0
                  Else
                        WS_A.Cells(iR,1).Interior.ColorIndex = 3  
                  End If
            Else
                        WS_A.Cells(iR,1).Interior.ColorIndex = 3  
            End If
            
      
      Next 
      
      WB_A.Save()
      WB_B.Save()
      excelObject.Quit()
      Set excelObject = Nothing

End Function

 

  • msal4434's avatar
    msal4434
    Occasional Contributor
    8 years ago

    Hi Shankar,

     

    Thank you for your solution.

     

     

    Need one more solution.

    -Read A1(Column1) from Sheet1 and find  A1(Column1) in Sheet2.

    If there is a match than Read G1(Column 7) from Sheet 1 and F1(Column 13) in Sheet2 and check if they match or not? 

     

    Thanks and Regards 

     

    Sal

    • shankar_r's avatar
      shankar_r
      Community Hero
      8 years ago

       

      In this case, you just change the Column Number in the below highlighted code.

       

      If(WS_A.Cells(iR,1).Value = WS_B.Cells(iR,1).Value) Then
	  If(WS_A.Cells(iR,4).Value = WS_B.Cells(iR,5).Value) Then
			Log.Message("Value matched " & WS_A.Cells(iR,1).Value)
			WS_A.Cells(iR,1).Interior.ColorIndex = 0
	  Else
			WS_A.Cells(iR,1).Interior.ColorIndex = 3  
	  End If
Else
			WS_A.Cells(iR,1).Interior.ColorIndex = 3  
End If