Fucntionality : Compare 2 Excel sheets and store the Result into new Excel sheet

This function is used to compare two excel file and to store the results in another file.
It takes the File Path for the First & Second Excel Files to be compared, Takes the Sheet Numbers of both the excel Sheets.    
(1) First File: This parameter defines the path for the First Excel File
(2) Second File: This parameter defines the path for the Second Excel File
(3) First Sheet: This parameter specifies the Sheet Name in the specified First Excel File
(4) Second Sheet: This parameter specifies the Sheet Name in the specified Second Excel File
(5) Result File: This parameter defines the path where the output file needs to be stored

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

Example:-
__________________________________________

CompareExcelSheets("C:\Actual\One.xls","C:\Test\Two.xls","Sheet1","Sheet2","C:\Result\ComparisionOutput.xls")                                                                                                                                                                                                                                                           

'########################################################################################################################

Function CompareExcelSheets(FirstFile,SecondFile,FirstSheet,SecondSheet,ResultFile)

   Dim objFSO,ff,sf,x,y,ExcelApp1,SheetCount1,ExcelApp2,SheetCount2,xyz,i,j,Row1Count,Row2Count,Col1Count,Col2Count,ColCount,RowCount,RowNumber

   On Error Resume Next

 Set objFSO = CreateObject("Scripting.FileSystemObject")

ff=0

sf=0

x=0

y=0

'Check for the given file existence

If (objFSO.FileExists(FirstFile)) then

               Set ExcelApp1 = CreateObject("excel.application")

       ExcelApp1.workbooks.open(FirstFile)

       SheetCount1 = ExcelApp1.Application.Workbooks(1).worksheets.count

'Check for the given sheetname existence

            For i=1 to SheetCount1

               If ExcelApp1.Application.Workbooks(1).worksheets(i).name = FirstSheet Then x=1

            Next

'           msgbox ExcelApp1.Worksheets(FirstSheet).Rows.count

Else

ff=1

End If

'Check for the given file existence

If (objFSO.FileExists(SecondFile)) Then

               Set ExcelApp2 = CreateObject("excel.application")

       ExcelApp2.workbooks.open(SecondFile)

       SheetCount2 = ExcelApp2.Application.Workbooks(1).worksheets.count

'Check for the given sheetname existence              

            For j=1 to SheetCount2

               If ExcelApp2.Application.Workbooks(1).worksheets(j).name = SecondSheet Then y=1

            Next

            'msgbox ExcelApp1.Worksheets(SecondSheet).Rows.count

Else

sf=1

End If

'File1 Doesn't Exist then Quit the Function

If ff=1 Then

            Reporter.ReportEvent 1,"Please Verify File Path and Name Of First File       "&FirstFile,""

            Exit Function

End If

'File2 Doesn't Exist then Quit the Function

If sf=1 Then

            Reporter.ReportEvent 1,"Please Verify File Path and Name Of Second File        "&SecondFile,""

            Exit Function

End If

'Sheet1 Doesn't Exist then Quit the Function

If x=0 then

               Reporter.ReportEvent 1,"Please Verify Sheet Name Of First File            "&FirstSheet,""

               Exit Function

End If

'Sheet2 Doesn't Exist then Quit the Function

If y=0 then

               Reporter.ReportEvent 1,"Please Verify Sheet Name Of Second File            "&SecondSheet,""

               Exit Function

End If

If (objFSO.FileExists(ResultFile)) Then

            xyz=0

            else

            Reporter.ReportEvent 1,"Please Verify File Path and Name Of Result File       "&ResultFile,""

            Exit Function

End If

ExcelApp1.workbooks.close

            Set ExcelApp1 = Nothing

ExcelApp2.workbooks.close

            Set ExcelApp2 = Nothing

'   Add both sheets FirstExcel and SecondExcel

            datatable.AddSheet("FirstExcel")

            datatable.AddSheet("SecondExcel")

            'Import data to FirstExcel Sheet from FirstSheet of FirstFile

Execute "datatable.ImportSheet" & Chr(34) & FirstFile & Chr(34) & "," & Chr(34) & FirstSheet & Chr(34) & ", " & chr(34) &_

 "FirstExcel" & chr(34)

 'Import data to SecondExcel Sheet from SecondSheet of SecondFile

Execute "datatable.ImportSheet" & Chr(34) & SecondFile & Chr(34) & "," & Chr(34) & SecondSheet & Chr(34) & ", " & chr(34) &_

 "SecondExcel" & chr(34)

    'Assign the no.of Rows of FirstExcel to Row1Count and SecondExcel to Row2Count

            Row1Count = datatable.GetSheet("FirstExcel").getrowcount

            Row2Count = datatable.GetSheet("SecondExcel").getrowcount

            'Assign the no.of Columns of FirstExcel to Col1Count and SecondExcel to Col2Count

            Col1Count = datatable.GetSheet("FirstExcel").GetParametercount

            Col2Count = datatable.GetSheet("SecondExcel").GetParametercount

'If Row1Count = 0 Then

            '              Reporter.ReportEvent 1, "Check For Empty Sheet         "&FirstExcel,""

                        '   Exit Function

'End If

'If Row2Count = 0 Then

            '              Reporter.ReportEvent 1, "Check For Empty Sheet        "&SecondExcel,""

                        '   Exit Function

'End If 

'If any of the files are empty then quit the function

If Row1Count=0 or Row2Count = 0 Then

                           Reporter.ReportEvent 1, "Check For Empty", "Sheet"

                           Exit Function

End If 

                        'Check for the no.of Columns of both the sheets and if they are not equal then quit the function

                        If Col1Count <> Col2Count Then

                                    Reporter.ReportEvent 1, "Mismatch", "Columns"

                                    Exit Function

                        Else

                                    'If the no.of Columns of both the files are equal then add the Output sheet

                                    datatable.AddSheet "Output"

                                    ColCount = Col1Count

                                    'Print the Column Headings to the Output Sheet

                                    For i = 1 to ColCount

                                                datatable.GetSheet("Output").addparameter datatable.GetSheet("FirstExcel").getparameter(i).name, nothing

                                    Next

                                    'Add another column as Remarks to the Output Sheet for writing the Difference after Comparision

                                    datatable.GetSheet("Output").addparameter "Remarks",nothing

                                    'Compare the Rows of First and Second Data Sheets                

                                    If Row1Count < Row2Count Then

                                                RowCount = Row1Count

                                                datatable.GetSheet("Output").setcurrentrow Row1Count +1

                                                Row1Number = Row1Count +1

                                                'If the Second Sheet has got more no.of rows it displays a remark in Output Sheet

                                                For i =1 to (Row2Count - Row1Count)

                                                datatable.GetSheet("Output").getparameter(ColCount +1).value = "All Rows Does not Exist in First Sheet"

                                                datatable.GetSheet("Output").setcurrentrow Row1Number +i

                                                Next

                                    Else

                                                RowCount = Row2Count

                                                datatable.GetSheet("Output").setcurrentrow Row2Count +1

                                                Row2Number = Row2Count +1

                                                'If the First Sheet has got more no.of rows it displays a remark in Output Sheet

                                                For i =1 to (Row1Count - Row2Count)

                                                datatable.GetSheet("Output").getparameter(ColCount +1).value ="All Rows Does not Exist in Second Sheet"

                                                datatable.GetSheet("Output").setcurrentrow Row2Number +i

                                                Next

                                    End If

   

                                    RowNumber = 1

                                    datatable.GetSheet("FirstExcel").setcurrentrow 1

                                    datatable.GetSheet("SecondExcel").setcurrentrow 1

                                    datatable.GetSheet("Output").setcurrentrow 1

                                    B=True

                                    While datatable.GetSheet("FirstExcel").Getcurrentrow <= RowCount

                                                For i = 1 to ColCount

                                                            'Check the data in FirstExcel is same as SecondExcel and if they are same then write All Columns Match, else write the Column no where the mismatch is

                                                            If datatable.GetSheet("FirstExcel").getparameter(i).value = datatable.GetSheet("SecondExcel").getparameter(i).value Then

                                                                        datatable.GetSheet("Output").getparameter(i).value = datatable.GetSheet("FirstExcel").getparameter(i).value

                                                                                    If datatable.GetSheet("Output").getparameter(ColCount +1).value = "" Then

                                                                                    datatable.GetSheet("Output").getparameter(ColCount +1).value = "All Columns Match"

                                                                                    'B=True

                                                                                    End If

                                                            Else

                                                                        datatable.GetSheet("Output").getparameter(i).value = datatable.GetSheet("SecondExcel").getparameter(i).value

                                                                        If datatable.GetSheet("Output").getparameter(ColCount +1).value = ""  Then

                                                                                    datatable.GetSheet("Output").getparameter(ColCount +1).value ="Column " &  i  & " Does Not Match"

                                                                                    B=False

                                                                        Else

                                                                                    If datatable.GetSheet("Output").getparameter(ColCount +1).value = "All Columns Match" Then

                                                                                                datatable.GetSheet("Output").getparameter(ColCount +1).value = "Column " &  i  & " Does Not Match"

                                                                                                B=False

                                                                                    Else

                                                                                                datatable.GetSheet("Output").getparameter(ColCount +1).value = datatable.GetSheet("Output").getparameter(ColCount +1).value & "," & " Column " & i  & " Does Not Match"                 

                                                                                                B=False

                                                                                    End if

                                                                        End if

                                                End If

                                    Next

                                    RowNumber = RowNumber +1

                                    datatable.GetSheet("FirstExcel").setcurrentrow RowNumber

                                    datatable.GetSheet("SecondExcel").setcurrentrow RowNumber

                                    datatable.GetSheet("Output").setcurrentrow RowNumber

                        Wend

                        End if

'Export the OutputDataSheet data into Given ResultFilePath

Execute "datatable.Exportsheet"  & chr(34) & ResultFile & chr(34) & "," & Chr(34) & "Output" & Chr (34)

If B=False Then

            CompareExcelSheets=False

                                      '  Reporter.ReportEvent 1, "Mismatch", "Columns"

            Else

            CompareExcelSheets=True

                                    '           Reporter.ReportEvent 0, "Match All", "Columns"

End If

End Function

############################################################################