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