Compare Excel files

[FredrikS]

I'm looking for a function or a program that can compare two excel files and promt the differences. I have one file that is the original and will get new files that should be "copies" of the original. The problem is that the excel file contains 7 sheets so the possibility to export to text file is time consuming.

Thankful for any tips.

[theDude]

Comparing Excel files for differences

Unfortunately your request lacks sufficient detail to provide the exact solution you have in mind. For future reference, please review the suggested guidelines for posting in the Excel newsgroups (THANKS to Chip Pearson & Gary Brown!!!): http://www.excelforum.com/showthread...ght=guidelines

I think the basic premise of your requirement is EXCELLENT, and would benefit other Excel users as well, so I took a stab at providing a “generic” VBA solution using the following logic:

Only cell values are considered in the evaluation process.

Each worksheet is ‘paired-up’ for comparison (‘original’ sheet 1 is compared to ‘copy’ sheet 1, etc.) and if the sheet count differs between files, it alerts the user and stops executing (it’s difficult to compare something to nothing). If the data range differs between worksheets being compared, it ‘resizes’ the range to cover the difference so an accurate “apples-to-apples” comparison can be performed.

It allows you to compare as many ‘copied’ files to the ‘original’ as you want (user is prompted at the end of a file comparison run to continue or cancel the process). Also, The dialog displays processing statistics at the end of each file comparison; both for the file just processed & running totals for the session if multiple runs are performed.

It captures the mismatches only in an exception log (.txt) file for review. THANKS to ‘Fern’ for this (slightly modified) code segment!!! At the end of the comparison session, the log file will be displayed (if desired) in Excel (if the exception data doesn’t exceed the maximum 65,536 row limit in a worksheet), or in MS Word if it does. The exception log is saved in the C:\Temp file directory.

NOTE: You may experience a memory error using this solution if you attempt to process large data sets with many differences between them. Since data differences are unknown at runtime, my code logic builds an array incrementally only when a difference is detected to store exception data (which helps to conserve memory usage). Depending on which version of Excel you use, the memory limit varies and based on your data may be exceeded during runtime. Please investigate the following URL for more info on Excel memory limits:
http://www.decisionmodels.com/memlimitsc.htm

The attached file VBA Solution.txt contains the program to copy into a VBA module. The attachment orig.xls - Comparison Exception Report.txt is an actual log file from one of my tests for your perusal to help determine if this solution meets your needs.

Alternatively, Excel’s workbook sharing/change tracking functionality (refer to your Excel Help for details) may be a viable option for you.

Hope this helps,
theDude