Excel WorkSheet comparison

[mdes]

The .XLS comparison is comparing the active worksheets and not the first worksheets as stated in the file format description.
To compare the first sheets, the line

With xls.ActiveWorkbook

should be changed to

With xls.ActiveWorkbook.WorkSheets(0)

in the script Helpers\XLS_to_CSV_Single.vbs .

On the other end, it could be useful to give the BC users the possibility to spcify the sheet nr (as a parameter)

[Lutz]

Salut Michel!

In the past I changed an XLS...vbs to compare all worksheets by putting a "FOR EACH WS IN ActiveWorkbook.WorkSheets"-loop around the existing code for one Worksheet, but I don't have it anymore. Perhaps you can give this a try and post the result here.

Greetings Lutz

[Michael Bulgrien]

If creating your own vbScript, you'll need to load your workbook first:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("filename.xls")
For Each objWorksheet In objWorkbook.Worksheets
.
.
.
Next

[mdes]

In the past I changed an XLS...vbs to compare all worksheets by putting a "FOR EACH WS IN ActiveWorkbook.WorkSheets"-loop around the existing code for one Worksheet, but I don't have it anymore. Perhaps you can give this a try and post the result here.

This case is already covered by "XLS_to_CSV.vbs" available under Microsoft Excel workbooks on Additional file format downloads for version 3

So, forget this thread

[Aaron]

Correct.

We do offer the XLS_to_CSV.vbs for all the sheets, but do not have a Data Compare rule using it by default since it will 'dump' all of the rows from all of the sheets into a single table (and introduce a blank line for each seperation between worksheets).

However, after installing the additional Text File Format, you can clone your Data Compare rule, and tell it to use CSV.vbs instead of CSV Single.vbs.

[Robin]

I am using BC v3.10 and I am attempting to compare worksheets in Excel. I changed the code for the XLS_to_CSV_Single.vbs as suggested by Michel. I get a conversion error when I try to compare my spreadsheets. What have I done wrong? I kept the file name the same.
Thanks!

' XLS_to_CSV_Single.vbs
'
' Converts <input file> from an Excel workbook to a comma-separated text file
' and saves the results to <output file>. Requires Microsoft Excel.
'
' Usage:
' WScript XLS_to_CSV_Single.vbs <input file> <output file>

' WdSaveFormat constants, taken from the Word type library
Const xlCSV = 6 ' Comma-separated values
Const xlUnicodeText = 42 ' Tab delimited text file

Dim fso, xls
Set fso = CreateObject("Scripting.FileSystemObject")
fso.DeleteFile WScript.Arguments(1)
Set xls = CreateObject("Excel.Application")
xls.DisplayAlerts = False

On Error Resume Next

xls.Workbooks.Open WScript.Arguments(0), , True
If Err = 0 Then
With xls.ActiveWorkbook.WorkSheets(0)
.SaveAs WScript.Arguments(1), xlCSV
.Close False
End With
End If

xls.Quit

[Aaron]

Instead of manually editing code, try installing our additional file format for Excel files, available here:

http://www.scootersoftware.com/download.php?zz=kb_moreformats

Then make a clone of the Data Compare Excel rule. Change the External Conversion to use the same .vbs as the new text compare format you just installed.

The topmost Excel file format in the Tools menu -> File Formats dialog is the rule that will be used automatically.

[Robin]

Got it! Thank you!

[Aaron]

No problem.

Please note that there are two side effects to using it this way:

1) All the sheets are dumped into a single sheet for comparison.
2) There will be introduced line breaks between each sheet, so you will have a couple of blank lines introduced as well.

[misi01]

I'm sorry, but for the idiots amongst us (me)

could you explain in a little more detail how to use the plug-in to compare XLS files. I have downloaded the XLS_to_CSV_Single.vbs file to HELPERS under the BC3 directory. I have imported the AdditionalFormats.bcpkg file and imported it into BC3. I see this as one of the file formats for *.xls;*.xlsm;*.xlsx. In addition, the conversion tab shows "wscript Helpers\XLS_to_CSV_Single.vbs %s %t" (without the quotes).

I include 2 screen captures - one that shows the XLS file and one after using the same file as input to BC3. Are these results as they should be, or am I missing something completely (I was expecting to see the xls values as nice" csv values in BC3, but maybe I misunderstood that ?)


Or.... am I supposed to save each XLS as a CSV (via save as in Excel) and then compare the results using BC3 ????

[Aaron]

Hello,

It looks like you have edited your settings in a different way. In one of your screenshots, you are opening your Excel files with the Comma Separated Values file format.

The default file format opens the a single pane in the Data Compare. You should see just one pane, but no editing is required to view the rows and columns of your Excel file.

The additional File Format from the download page is a Text Compare rule. It will open all pages of the Excel .xls file in the Text Compare using a different conversion method and display it as a plain text file (similar to a csv file).

The edit I suggest is to make a clone of the original Data Compare rule, but to replace the External Conversion line XLS_to_CSV_Single.vbs with the conversion line from the downloaded rule based on XLS_to_CSV.vbs.

If you convert the XLS file to CSV yourself, before opening them in BC3, then that will work too. These external conversions merely automate that process.

[misi01]

Getting there

Okay - downloaded the XLS_to_CSV.vbs and that's looking better

I changed the file format definition as you suggested (?), and the results are now converted as expected. I hadn't understood that I should use Data Compare rather a normal Text/Folder compare.

One thing I don't understand - why is the Excel data shown in BC in a different order to what it's stored in Excel ? (ie, column A seems to be sorted in ascending order via the VBS code)

[Michael Bulgrien]

I haven't seen your files, so I can't be sure what the conversion did.
Be aware, however, that a data compare does resort the data by default. If you don't want the data resorted, go into the Session Settings -> Columns tab and check the "Unsorted alignment" checkbox.

[Aaron]

Hello,

You do *not* need to use the Data Compare.

By default, we will show one sheet in the Data Compare. If you are using single sheet Excel files, then this can be a good solution for you.

The second method is to download and use our Text Compare file format for Excel files. This will open all of the sheets and display them, in order, in the Text Comapre. This will compare the text in the file and align it using standard text alignment.

The third option is a workaround/hack to combine these two methods. I would only recommend it if the previous two methods do not meet your needs. You can use the text compare's conversion in a data compare rule. This will place all of your sheets into a single sheet, and as Michael says: by default it will sort them. It will also insert blank, line break rows since the conversion was original designed for Text comparison. When sorted, these blank rows will generally appear at the top.

If you would like to disable sorting, go to the Session Settings, Columns tab, and enable Unsorted alignment. Also, set your Key column to a unique ID column; otherwise you will want to disable it. The key column is used for alignment: if it matches exactly, then the two rows will align. If it does not match, then the rows will not align. You can have multiple key columns (First Name, Last Name) as a combination to provide a unique identifier.

[jmoverocel]

Hi Everyone

I cannot compare Excel spreadsheets at all. Excel 2007 crashes every time.

Recently I saw a traceback message for the first time, but neglected to take a screenshot. It referenced line 19 from XLS_to_CSV.vbs. Could not open file (?) [maybe?]

Something silly on my part?

Any ideas?

My installed version is 3.1.11 build 12204.
Windows Vista Business SP2
Excel 2007 (12.0.6535.5002) SP2

Many thanks for your consideration,

jim

Couldn't do my job without BC3!