PDA

View Full Version : Compare two xls files and generate a report/result file



VishalkumarT
04-02-2007, 02:17 AM
Dear Friends

I am searching for a VBA code with which I can compare two (or more, may be) xls files, and generate a resulting file which shows where the data or strings are not equal.

My xls files contain several data in terms of worksheets inside them, so for example, one xls file contains 10 worksheets, having different data. And I want to compare this file with the other file, having 10 worksheets of different data; but the format of the worksheets in these both files are the same(the arrangement of rows and columns in the worksheets in both files are the same).

I have tried to compare two worksheets, in which I succeeded so far, but now I want to compare complete files of these worksheets.

Waiting for your help in near future and thank you for the same in advance.

Vishal

Simon Lloyd
04-02-2007, 03:58 AM
Hi, maybe you can adapt this, i use this for comparing single columns in different workbooks maybe you can adapt it!, you need to creat a workbook called Results, it will then deposit the cell value from workbook2 (duptest2) and the address in the results workbook.

Option Explicit
Sub Macro1()
Dim Rng As Range
Dim x, y, found As Variant
For x = 1 To Workbooks("duptest2.xls").Sheets(1).Range("A1").End(xlDown).Row
For y = 1 To Workbooks("duptest1.xls").Sheets(1).Range("A1").End(xlDown).Row
If Workbooks("duptest2.xls").Sheets(1).Cells(x, 1) = Workbooks("duptest.xls").Sheets(1).Cells(y, 1) Then
found = True
y = Workbooks("duptest1.xls").Sheets(1).Range("A1").End(xlDown).Row + 1
End If
Next y
If found = False Then
Set Rng = Workbooks("Results").Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0)
Rng.Value = Workbooks("duptest2.xls").Sheets(1).Cells(x, 1).Value
Rng.Value = Rng.Value & " @ " & Workbooks("duptest2.xls").Sheets(1).Cells(x, 1).Address
End If
found = False
Next x
End Sub

Regards,
Simon

VishalkumarT
04-05-2007, 12:59 AM
Hi

Thanks a lot for your time and reply.

Have a nice Easter ahead !!

Greetings
Vishal