cancel
Showing results for 
Search instead for 
Did you mean: 

Comparing 2 xls files with the specific column.

Highlighted
Occasional Contributor

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 available in b.xls then compare A.A1, A.D1  with B.A1, B.E1. 

 

would like to compare all the values in the same way.

 

SCRIPT

 



Reading from A.xls
Sub Readfromexcel()  
   Set Excel = Sys.OleObject("Excel.Application")
   Excel.Workbooks.Open("A.xls")
    RowCount = Excel.ActiveSheet.UsedRange.Rows.Count
   ' log.message RowCount
    ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count
    'log.message ColumnCount
    'For i = 1 To RowCount
    for SI = 1 to ColumnCount  
      For SJ = 1 To RowCount
        Ss = Excel.Cells(SJ,SI)
        'log.message S
        SCV = "3"    
            IF Ss = SCV then
                  For SInn = 2 To rowcount
                    SCC = Excel.Cells(SInn,SJ)
                    SHV = Excel.Cells(SInn,SI)
                        If SHV >  0 then
                            log.message "State:" & Ss
                            log.message "Charge Code is avaliable in Source file:" & SCC
                            log.message "Value is Zero is avaliable in Source file:" & SHV
                            
                                '' Export from VIP Net
                            
                            
                                                    
                            Else
                            log.message "State:" & Ss
                            log.message "Charge Code is avaliable in Source file Price is zero:" & SCC
                            log.message "Value is Zero is avaliable in Source file:" & SHV                          
                        end if              
                    
                  Next              

                      
            end if    
                Exit For        
       Next      
  Next
  Excel.Quit
End Sub
 
Reading B.xls
 
Sub Readfromexcel()  

                       Set Excel = Sys.OleObject("Excel.Application")
                        'Opening the VIP Net export file
                              Excel.Workbooks.Open("C:\Users\saravanan.doraiswamy\Documents\BpSoftware\HF_SourceFile\ChargeItems20161215.xls")
                              VRowCount = Excel.ActiveSheet.UsedRange.Rows.Count
                              log.message VRowCount
                              VColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count
                              log.message VColumnCount
                                              For EVIPI = 1 to VColumnCount  
                                                            For EVIPJ = 1 To VRowCount
                                                              EVIPs = Excel.Cells(EVIPJ,EVIPI)
                                                            
                                                               ECV = "E"          
                                                                  IF EVIPs = ECV then
                                                                        For EVIPInn = 2 To Vrowcount
                                                                        EVIPHV = Excel.Cells(EVIPInn,EVIPJ)  
                                                                         If   EVIPHV > 0 then
                                                                        EVIPCC = Excel.Cells(EVIPInn,EVIPI)      
                                                                                          log.message "Charge Code:" & EVIPHV
                                                                                          log.message "Price:" & EVIPCC    
                                                                       end if    
                                      Next                                               

                             
 
 
 
5 REPLIES 5
Highlighted
Super Contributor

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.

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

Highlighted
Community 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

 


Thanks
Shankar R

LinkedIn | CG-VAK Software | Bitbucket | shankarr.75@gmail.com

“You must expect great things from you, before you can do them”


Extension Available

Highlighted
Occasional 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

Highlighted
Community Hero

 

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

Thanks
Shankar R

LinkedIn | CG-VAK Software | Bitbucket | shankarr.75@gmail.com

“You must expect great things from you, before you can do them”


Extension Available

New Here?
Join us and watch the welcome video:
Announcements
Join TechCorner Challenge!
Want a fun and easy way to learn TestComplete? Try solving weekly TechCorner challenges and get into the Leaderboard!


Challenge Status

Get properties of a web page element

See replies!

Compare images using the Region Checkpoint

See replies!

Compare HTML table with Excel file and correct data in Excel file

Participate!

How to execute remote test and obtain results via Test Runner REST API

Participate!
Top Kudoed Authors