Comparing two Excel Files

1.
Create 2 excel file test and test1 and 1 columns FirstSheetColumn. It checks first columns data only.

TrueOrFalse = "Same"
DataTable.Import("C:\test.xls")
FirstTableCount = DataTable.GetRowCount
DataTable.Import("C:\test1.xls")
SecondTableCount = DataTable.GetRowCount

if (FirstTableCount <> SecondTableCount) then
msgbox "Both files are not Equal"
else
For I = 1 To FirstTableCount
DataTable.Import("C:\test.xls")
Datatable.SetCurrentRow(I)
value1 = DataTable.Value("FirstSheetColumn")
DataTable.Import("C:\test1.xls")
Datatable.SetCurrentRow(I)
value2 = DataTable.Value("SecondSheetColumn")
if(value1 <> value2) then
TrueOrFalse = "Different"
Exit For
end if
Next
end if
msgbox(TrueOrFalse)

Set ExpObjectExcelApp = CreateObject("Excel.Application")
ExpObjectExcelApp.Workbooks.open ("C:/Test1.xls")
ExpSheet = "Sheet1"

2.
Set ActObjectExcelApp = CreateObject("Excel.Application")
ActObjectExcelApp.Workbooks.open ("C:/Test2.xls")
ActSheet = "Sheet1"

Set ExpectedDataSheet = ExpObjectExcelApp.Worksheets(1).UsedRange
Set ActualDataSheet = ActObjectExcelApp.Worksheets(1).UsedRange

ExpObjectExcelApp.Worksheets(1).UsedRange.Font.Color = vbBlack
ActObjectExcelApp.Worksheets(1).UsedRange.Font.Color = vbBlack

For Each Cell In ExpectedDataSheet.Cells
RowIndex = Cell.Row
ColumnIndex = Cell.Column
If Cell.Value <> ActualDataSheet.Cells(RowIndex, ColumnIndex).Value Then
Cell.Font.Color = vbRed
ActualDataSheet.Cells(RowIndex, ColumnIndex).Font.Color = vbRed
Reporter.ReportEvent micFail,"miss match", "row = " & RowIndex &" Column = "& ColumnIndex
Else
Cell.Font.Color = vbGreen
ActualDataSheet.Cells(RowIndex, ColumnIndex).Font.Color = vbGreen
End If
Next

ExpObjectExcelApp.ActiveWorkbook.Save
ActObjectExcelApp.ActiveWorkbook.Save

ExpObjectExcelApp.Quit
ActObjectExcelApp.Quit

Connecting Excel and Checking values

Filename = "c:\sample.xls"
MyWorkSheet = "[Sheet1$]" 'special syntax, sheet name is test

ActiveCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
ActiveCon = ActiveCon & Filename
ActiveCon = ActiveCon & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;"""

set rsExcel = CreateObject("ADODB.Recordset")
rsExcel.LockType = 1
rsExcel.ActiveConnection = ActiveCon

rsExcel.Open "Select * from " & MyWorkSheet

msg = ""

for j = 0 to rsExcel.fields.count -1
msg = msg & rsExcel.fields(j).name & " "
next

msg = msg & vbcrlf
msg = msg & vbcrlf

do while not rsExcel.eof
for j = 0 to rsExcel.fields.count -1
msg = msg & rsExcel.fields(j) & " "
next
msg = msg & vbcrlf
rsExcel.movenext
loop
rsExcel.close
msgbox msg