Ask a Question

Re-using script to compare excel in other keyword tests

kandy1984
Contributor

Re-using script to compare excel in other keyword tests

Hi,



The software that im testing produces excels and i need to compare them against the expected excels. I have written a script which compares 2 excels but i want them to be re-used in other keyword tests so that i can just specify the 2 excel names for that particular keyword test.



The script at the moment takes in 2 hard-coded file names.



-------------------------------------------------------------------------------------------------------




Sub Sequence


  


    xlfile1 = "C:\Users\Test\Desktop\1.xlsx"


    xlfile2 = "C:\Users\Test\Desktop\2.xlsx"


    


    resfile = "C:\Users\Test\Desktop\Results.xlsx"


    


    Message1 = ExcelCompare(xlfile1, xlfile2, resfile)


 


 


  if Message1 = "No mismatches found." then


    Log.Message(Message1)


  else


    Log.Error("Files were not the same.")


  end if 


    


 


End Sub


 


 


'Function to compare two excel files


Function ExcelCompare(firstFile, secondFile, resultFile)


  'Declaring varaibles 


  Dim objExcel, objSpread1, objSpread2


  Dim x1 ,x2, y1, y2, maxR, maxC, DiffCount, PDiffCount


  Dim cf1, cf2, fOffset, sMsg


  


  'Creating object of the two Excel files


  Set objExcel = CreateObject("Excel.Application")


  objExcel.displayalerts = false


  Set objSpread1 = objExcel.Workbooks.Open(firstFile)


  Set objSpread2 = objExcel.Workbooks.Open(secondFile)


  


  If not IsNull(resfile) Then


    Dim resBook


    Set resBook = objExcel.Workbooks.Add


    resBook.Sheets(1).Name = "Result"


    Set resWorkSheet = resBook.WorkSheets("Result")


    Call PrepareResultExcelFile(firstFile, secondFile, resWorkSheet)


    Dim resOffSet


    resOffSet = 6


  End If


  


  strCount = SheetsNumber(objSpread1, objSpread2)


  DiffCount = 0


  PDiffCount = 0


 


  'Loop to identify the differences per worksheet


  For i = 1 To strCount 


  


    'Getting the row and column count of the first worksheet 


    Set objWorksheet1 = objSpread1.Worksheets(i)


    With objWorksheet1.UsedRange


      x1 = .Rows.Count


      y1 = .Columns.Count


    End With


    For tOff = 1 to x1 


      If (objWorksheet1.Cells(tOff,1) <> "") Then


        fOffset = tOff


        Exit For


      End If


    Next


    


    'Getting the row and column count of the the second worksheet 


    Set objWorksheet2 = objSpread2.Worksheets(i)


    With objWorksheet2.UsedRange


      x2 = .Rows.Count


      y2 = .Columns.Count


    End With


    maxR = x1


    maxC = y1


    If maxR < x2 Then


      maxR = x2


    End If


    If maxC < y2 Then


      maxC = y2


    End If


    


    'Loop to find the differences between the two files (cell by cell)


    cf1 = ""


    cf2 = ""


    For c = 1 To maxC 


      For r = 1 To (maxR+fOffset) 


        On Error Resume Next


          cf1 = LTrim(RTrim(objWorksheet1.Cells(r, c).Value))


          cf2 = LTrim(RTrim(objWorksheet2.Cells(r, c).Value))


          PDiffCount = DiffCount


        If Isnumeric(cf1) And Isnumeric(cf2) Then


          If Abs(cf1-cf2) >= 1 Then


            DiffCount = DiffCount+1


          End If


          Else


            If cf1 <> cf2 Then


            DiffCount = DiffCount+1


          End If


        End If


        If not IsNull(resfile) Then 


          If DiffCount >= (PDiffCount+1) Then


            objWorksheet1.Cells(r,c).Interior.ColorIndex = 3


            objWorksheet2.Cells(r,c).Interior.ColorIndex = 3


            resWorkSheet.Cells(resOffSet, 1) = objSpread1.Worksheets(i).Name


            resWorkSheet.Cells(resOffSet, 2).Formula = "=Address("&r&", "&c&", 4)"


            resWorkSheet.Cells(resOffSet, 3) = objWorksheet1.Cells(r, c).Value


            resWorkSheet.Cells(resOffSet, 4) = objWorksheet2.Cells(r, c).Value


            resOffSet = resOffSet + 1


          End If


        End If


        cf1 = ""


        cf2 = ""


      Next


    Next


  Next


  If DiffCount=0 Then


    sMsg = "No mismatches found."


  Else


    resBook.SaveAs resultFile


    sMsg =  DiffCount & " items mismatches. " & vbLF & "The result file available at : " & resultFile


  End If


  


  resBook.Close


  objSpread1.Close


  objSpread2.Close


  objExcel1.displayalerts = True


  objExcel1.Quit


  Set objSpread1 = Nothing


  Set objSpread2 = Nothing


  Set objExcel1 = Nothing


  Set resBook = Nothing


  


  ExcelCompare = sMsg


End Function


 


'Geting the number of worksheets used


Function SheetsNumber(objSpread1, objSpread2)


  Dim strCount, strCount1, strCount2 


  strCount1 = objSpread1.Worksheets.Count


  strCount2 = objSpread2.Worksheets.Count


  If strCount2 < strCount1 Then


    strCount = strCount1


  Else


    strCount = strCount2  


  End If


  SheetsNumber = strCount


End Function


 


'Preparing the Headers and details in the Result File


Sub PrepareResultExcelFile(firstFile, secondFile, resWorkSheet)


  resWorkSheet.Cells(1,1) = "This is a result file which highlights the differences between the files ..."


  resWorkSheet.Cells(2,1) = "File 1 : " + firstFile


  resWorkSheet.Cells(3,1) = "File 2 : " + secondFile


  resWorkSheet.Cells(4,1) = "'================================================================================================================="


  resWorkSheet.Range(resWorkSheet.Cells(1,1), resWorkSheet.Cells(1,12)).Merge


  resWorkSheet.Range(resWorkSheet.Cells(2,1), resWorkSheet.Cells(2,12)).Merge


  resWorkSheet.Range(resWorkSheet.Cells(3,1), resWorkSheet.Cells(3,12)).Merge


  resWorkSheet.Range(resWorkSheet.Cells(4,1), resWorkSheet.Cells(4,12)).Merge


  resWorkSheet.Cells(5,1) = "Sheet Name"


  resWorkSheet.Cells(5,1).Font.Bold = True


  resWorkSheet.Cells(5,2) = "Cell"


  resWorkSheet.Cells(5,2).Font.Bold = True


  resWorkSheet.Cells(5,3) = "Data in File 1"


  resWorkSheet.Cells(5,3).Font.Bold = True


  resWorkSheet.Cells(5,4) = "Data in File 2"


  resWorkSheet.Cells(5,4).Font.Bold = True


  resWorkSheet.Columns.AutoFit


End Sub


 

---------------------------------------------------------------------------------------------------------



What do i have to do to re-use them in other keyword tests? I hope someone can help me with this.



Thanks for helping!



Sudha
1 REPLY 1
TanyaYatskovska
SmartBear Alumni (Retired)

Hi Sudha,

 


From keyword tests, you can call the ExcelCompare function via the Run Keyword Test operation, and specify different Excel files for the comparison as the function parameters.


 

---------
Tanya Yatskovskaya
SmartBear Community and Education Manager



cancel
Showing results for 
Search instead for 
Did you mean: