Function fnVerify_DtSheets_Equal(strSourceFile, strSourceSheetIndex, strTargetFile,strTargetSheetIndex)
On Error Resume Next
' Function to compare two Excel files (more specific to two diff Excel sheets. )
' t compares each and every row and col and identifies the differences and reports all in line by line.
DataDifferences = 0
CompareReport = ""
If Not fnFileExists_InSpecifiedPath(strSourceFile) Then
Call DoneResult("Specified Source File is not found", "Not able to compare Excel Sheets")
Exit Function
ElseIf Not fnFileExists_InSpecifiedPath(strTargetFile) Then
Call DoneResult("Specified Target File is not found", "Not able to compare Excel Sheets")
Exit Function
End If
DataTable.ImportSheet strSourceFile ,strSourceSheetIndex ,1
DataTable.ImportSheet strTargetFile ,strTargetSheetIndex ,2
GbSheetRowGount = DataTable.GetSheet(dtGlobalSheet).GetRowCount
LcSheetRowCount = DataTable.GetSheet(dtLocalSheet).GetRowCount
GlobalColumnCount = DataTable.GetSheet(dtGlobalSheet).GetParameterCount
LocalColumnCount = DataTable.GetSheet(dtLocalSheet).GetParameterCount
If Trim(GbSheetRowGount) = Trim(LcSheetRowCount) and Trim(GlobalColumnCount) = Trim(LocalColumnCount) Then
Counter = 1
'msgbox "both sheets row counts are equal...."
For var_RowId = 1 to GbSheetRowGount
DataTable.SetCurrentRow(var_RowId)
For var_ColumnId =1 to GlobalColumnCount
GbSheetValue = Trim(DataTable.Value(var_ColumnId, dtGlobalSheet))
LcSheetValue = Trim(DataTable.Value(var_ColumnId, dtLocalSheet))
If Trim(GbSheetValue) <> Trim(LcSheetValue) Then
DataDifferences = DataDifferences + 1
CompareReport = CompareReport & "Difference " & DataDifferences & " at :--- > Row : " & var_RowId & " , Column : " & var_ColumnId & " values : ' " & GbSheetValue & "' , '" & LcSheetValue & "' " & vbCr
End If
GbSheetValue =Null
LcSheetValue=Null
Next
Next
If DataDifferences <> 0 Then
Reporter.Filter = rfEnableAll
Call DoneResult("Comparision is failed", CompareReport & "TOTAL " & DataDifferences & " Data differences found ")
fnVerify_DtSheets_Equal = False
Else
Reporter.Filter = rfEnableAll
Call DoneResult("Comparision is succeeded", "Data Sheets are Equal: Source File = '" & strSourceFile & "' and Target file = '" & strTargetFile & "'")
fnVerify_DtSheets_Equal = True
End If
Else
Reporter.Filter = rfEnableAll
Call DoneResult("Comparision is failed","Data Sheets Row or Columns are not equal...." & vbCr & "Source sheet Rows & Columns are : " & GbSheetRowGount & " , " & GlobalColumnCount & " and" & vbCr & "Target Sheet Rows & Columns are : " & LcSheetRowCount & " , " & LocalColumnCount)
fnVerify_DtSheets_Equal = False
End If
End Function
On Error Resume Next
' Function to compare two Excel files (more specific to two diff Excel sheets. )
' t compares each and every row and col and identifies the differences and reports all in line by line.
DataDifferences = 0
CompareReport = ""
If Not fnFileExists_InSpecifiedPath(strSourceFile) Then
Call DoneResult("Specified Source File is not found", "Not able to compare Excel Sheets")
Exit Function
ElseIf Not fnFileExists_InSpecifiedPath(strTargetFile) Then
Call DoneResult("Specified Target File is not found", "Not able to compare Excel Sheets")
Exit Function
End If
DataTable.ImportSheet strSourceFile ,strSourceSheetIndex ,1
DataTable.ImportSheet strTargetFile ,strTargetSheetIndex ,2
GbSheetRowGount = DataTable.GetSheet(dtGlobalSheet).GetRowCount
LcSheetRowCount = DataTable.GetSheet(dtLocalSheet).GetRowCount
GlobalColumnCount = DataTable.GetSheet(dtGlobalSheet).GetParameterCount
LocalColumnCount = DataTable.GetSheet(dtLocalSheet).GetParameterCount
If Trim(GbSheetRowGount) = Trim(LcSheetRowCount) and Trim(GlobalColumnCount) = Trim(LocalColumnCount) Then
Counter = 1
'msgbox "both sheets row counts are equal...."
For var_RowId = 1 to GbSheetRowGount
DataTable.SetCurrentRow(var_RowId)
For var_ColumnId =1 to GlobalColumnCount
GbSheetValue = Trim(DataTable.Value(var_ColumnId, dtGlobalSheet))
LcSheetValue = Trim(DataTable.Value(var_ColumnId, dtLocalSheet))
If Trim(GbSheetValue) <> Trim(LcSheetValue) Then
DataDifferences = DataDifferences + 1
CompareReport = CompareReport & "Difference " & DataDifferences & " at :--- > Row : " & var_RowId & " , Column : " & var_ColumnId & " values : ' " & GbSheetValue & "' , '" & LcSheetValue & "' " & vbCr
End If
GbSheetValue =Null
LcSheetValue=Null
Next
Next
If DataDifferences <> 0 Then
Reporter.Filter = rfEnableAll
Call DoneResult("Comparision is failed", CompareReport & "TOTAL " & DataDifferences & " Data differences found ")
fnVerify_DtSheets_Equal = False
Else
Reporter.Filter = rfEnableAll
Call DoneResult("Comparision is succeeded", "Data Sheets are Equal: Source File = '" & strSourceFile & "' and Target file = '" & strTargetFile & "'")
fnVerify_DtSheets_Equal = True
End If
Else
Reporter.Filter = rfEnableAll
Call DoneResult("Comparision is failed","Data Sheets Row or Columns are not equal...." & vbCr & "Source sheet Rows & Columns are : " & GbSheetRowGount & " , " & GlobalColumnCount & " and" & vbCr & "Target Sheet Rows & Columns are : " & LcSheetRowCount & " , " & LocalColumnCount)
fnVerify_DtSheets_Equal = False
End If
End Function
No comments:
Post a Comment