How to compare multiple excel sheets

Hi,

I have a perl script to export workbook data into several
text files inside a separate directory. How can I configure
BC2 to try to compare two directories instead of two files?

Please see script at the end. It's not pretty, but I think
it should work... At least I get one dir and inside it one
text file for each worksheet.

Thanx,

--jouni

#!/usr/bin/perl -w

use strict;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;

if ((scalar(@ARGV)) != 2)
{
print "Usage: $0 infile outfile\n";
exit;
}

use Cwd;
my $debug = 0;

my $folder = $ARGV[0]; # input filename
$folder =~ s/[\<\>\?\[\]\:\|\*\\\.\s]//g;
mkdir $folder;
chdir $folder or die "Cannot chdir to $folder\n";
print "DIR: ".cwd()."\n";
print $folder . "\n" if $debug;

my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{DisplayAlerts} = 0; # don't ask, just overwrite

# Original for BC2 which compares only first sheets of two excel files
#my $Book = $Excel->Workbooks->Open($ARGV[0]);
#$Book->Worksheets(1)->SaveAs($ARGV[1],xlTextMSDOS);
#$Book->Close(0);

my $Book = $Excel->Workbooks->Open($ARGV[0]);
my $curr_sheet = $Book->Worksheets->Count;

while ($curr_sheet != 0) {
print $Book->Worksheets($curr_sheet)->Name . "\n";
$Book->Worksheets($curr_sheet)->SaveAs(cwd()."\\".$Book->Worksheets($curr_sheet)->Name,xlTextMSDOS);
--$curr_sheet;
}
$Book->Close;

# end of file

[Chris]

Are you trying to run BC from the command line for a quick compare?
bc2 /qc file1 file2?
The quick compare command only works with file comparisons, not folder comparisons.

Quick compare of directories is on the wish list.

If you just want to find out if the Excel files are equivalent, have your script make BC loop over all of the files using a quick compare and test the dos error level.

If you're looking for differences in the files, you can use a BC script with the file-report command on the two folders your script generates.

[Erik]

MS Excel is available at:
http://www.scootersoftware.com/morerules

This add-on rule uses Excel to create comma-separated text from XLS workbooks so that BC2 can compare them.

Sorry,

Forgot to add background info: I use BC2 with perforce (version
control system) and need to see what are differences between
two excel files. My perl script works ok with first sheet
in workbook, but ignores all the following sheets. This is
causing problems, obviously..

Therefore I modified the perl script to export all sheets into
separate files under temp folder, but I am not able to setup
BC2 to compere the folders.. Is this the wishlist item you
mentioned?

I checked your MSExcel plugin, but unfortunately it also
handles only the first sheet. Furthermore the use experience
is more detailed (csv) but less readable (ms text) :)

Looking forward your next update, but in the meanwhile I do
need a solution == have to modify my perl script to export
all the sheets into same file.. Not so easy anymore.

..you know, I would really love to see the folder comparison.
Then I could export all readable text into separate files,
all the formulas into different ones and the macros into one
final separate file. Divide and conquer == good usability,
easy to check what are the diffs.

Thanx,

--jouni

[Erik]

Why not run your script to export sheets of file1 to folder1, and file2 to folder2, and then compare the folders in the Folder Viewer?

You can change the output format by editing "xls2csv.vbs". For example, using "42" instead of "xlCSV" will produce tab delimited plain text.

Ok,

How can I setup the rules to do "compare the folders in the Folder Viewer"? I initialize BC2 from perforce...

This is the definition for "user supplied diff application":
D:\Program Files\Beyond Compare 2\BC2.exe

I am able to give optional command line args, if I only
knew what to give.. Right now there are none, I believe
(it's not checked and it's grayed, but there are %1 %2 on
the textfield as parameters)

Thanx,

--jouni

[Erik]

Here are the possible command line parameters:
<ul type="square"> Named Session - Opens the specified session in the Folder Viewer. (eg. BC2.exe "MySession")
Pair of folders - Opens a new session in the Folder Viewer with the specified base folders using the default session settings. (eg. BC2.exe C:\Folder1 C:\Folder2)
Pair of files - Opens the specified files in the File Viewer. (eg. BC2.exe C:\File1.ext C:\Filer2.ext)
Script file - Automatically executes a list of commands without using a viewer. (eg. BC2.exe @C:\Script.txt)[/list]

Hello,

I tried the Excel-Plugin but it doesn't work.
I allways get the message that the ActiveX component is not able to generate an object: 'Scripting.FileSystemObject'

I work with W2k Professional, Excel 97-SR2 and Beyond Compare 2.1.2

Is the plugin wrong, wont it work with my Excel version or is it colliding with my security rules? :confused:

regards
Peter

[Craig]

We haven't tested it with tighter security settings, but it sounds like that's what's causing the problem. As far as I can tell, you'll need to change your settings in IE, in the "Security" tab of the "Internet Options" dialog. The setting you'll probably need to change is the "Initialize and script ActiveX controls not marked as safe".

If you only want to change this setting for files on your local system, you can try this tweak to add a "My Computer", in addition to the existing "Internet", "Local Internet", "Trusted Sites", and "Restricted Sites":
http://www.updatexp.com/tip18.html

It's for Windows XP, but it's probably the same under IE6 and Windows 2000.

Here's Microsoft's knowledge base article on the security zones too: http://support.microsoft.com/default.aspx?scid=kb;EN;Q174360&LN=EN

[sean]

Jouni
Did you get this working?
If I understand this correctly, the approach is to replace the call to BC (in your source control tool) by your own script which generates the temp folders itself and then calls BC on the folders, rather than using an excel file filter and calling your script from within BC.

[jouni]

Sorry,

I was not able to make it work :( It was not urgent need, only a matter of convenience. However I still believe it would be possible in some way, which I didn't figure out.

Yes, I was able to replace call to BC with a call to my own script which exported files and then called BC with those dirs as parameters. The problem had something to do with timing, BC2 deleted one temp file before I had a chance to deal with it or something... Also calling BC from script created second session, which wasn't too nice.

Btw plan B could be to export everything into same file one sheet after another. That would leave the problem of what to do with formulas and VBA for excel scripts.

--jouni