Page 1 of 1

Compare Excel document to another file

Posted: Thu Dec 06, 2007 3:03 am
by kodyleonard
I'm trying to get a script going that compares the contents of a spreadsheet with another file (another Excel file or .txt file). Anything found in the second file is deleted in the first file. Can anyone help me with this?

Compare Excel document to another file

Posted: Thu Dec 06, 2007 6:48 am
by kodyleonard
I got them comparing the files, but how would I use them to delete matching entries?

Compare Excel document to another file

Posted: Thu Dec 06, 2007 10:38 am
by jvierra
If you want two files to be identical just copy the first file over the second file. They will then both be the same.

Easiest way.

Compare Excel document to another file

Posted: Fri Dec 07, 2007 1:53 am
by jvierra
FC has a switch which will dump ONLY the differences between list 1 and list 2.

To do this with script you would probably want to load both files into sorted arrays and then walk them and compare them in collated order. This is a pretty sophisticated script.

Start with using the FileSystemObject to open both files and read them into arrays then compare teh arrays in a loop structure.

For teh Excel you would open both spreadsheets and do a cell by cell comparison assuming the spreadsheets are nearly identical to begin with. If you know the potentail liits you can narrow the comparison using that as a criteria.

Example - all values are in column A betwenn rows 1 and 100. Blank cells are ignored.

for i = 1 to 100
if sheet1.cell(1,i) <> sheet2.cell(1, i ) then
' report difference.
end if
next

If you start with something like this perhaps we can get you giong in the right direction.