Forum Discussion
Instead of writing two different program for reading data from sheet A and B. Create two Workbook/Worksheet object and read data. read from one and compare with other if the data matched then do further action.
or
Parameterize your anyone method which is accept data and compare.
or
use Excel method for comparison like -
Function compareExcel
Set exo = createObject("Excel.Application")
exo.visible = True
Set wbo1 = exo.workbooks.open("C:\xxxx\TestExcel_1.xlsx")
'Set wbo2 = exo.workbooks.open("C:\xxx\TestExcel_2.xlsx")
Set WSO1 = wbo1.worksheets(1)
Set WSO2 = wbo1.worksheets(2)
'Set WSO2 = wbo2.worksheets(1)
For each cell in WSO1.usedRange
'Comparing sheet A value with sheet B (Column by Column comparison - You need to modify as per your test)
If Cell.Value = WSO2.Range(Cell.Address).Value Then
cell.Interior.ColorIndex = 0
Else
cell.Interior.ColorIndex = 3
End If
Next 
End Function
might be this help you.
- msal44348 years agoOccasional 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_r8 years agoCommunity Hero
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
- msal44348 years agoOccasional Contributor
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
Related Content
Recent Discussions
- 3 hours agoSubhraDas
- 22 hours agotramuntana