Ask a Question

Re-using script to compare excel in other keyword tests


Re-using script to compare excel in other keyword tests


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



    '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


            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 = ""




  If DiffCount=0 Then

    sMsg = "No mismatches found."


    resBook.SaveAs resultFile

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

  End If





  objExcel1.displayalerts = True


  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


    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


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!

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

Showing results for 
Search instead for 
Did you mean: