Compare

Identifies any differences between

•all the worksheets in one workbook all of the worksheets in another

•a subset of the worksheets in one workbook with the equivalently named worksheets in another

•one worksheet with another worksheet

Applies to

IOAKAddIn, IOAKAPI

SYNTAX

Set result1 = expression1.Compare

Set result2 = expression2.Compare(bookOrSheets1, bookOrSheets2, testFormulas, ignoreCase, styleA1, alignRows, alignColumns, highlightDifferences, writeReport, groupEquivalentChanges, rowAlignmentColumns, columnAlignmentRows)

expression1   Required.  An expression that returns an IOAKAddIn object.

expression2   Required.  An expression that returns an IOAKAPI object.

result1:   An OAKResult enumeration indicating the success or otherwise of the action.

result2   An ICompareResult object that gives access to the results of the comparison.

BookOrSheets1 , BookOrSheet2   Required.  The workbook or worksheet(s) to be compared.  One of

•an Excel workbook object

•an array of workbook objects

•a collection of worksheet objects

•an Excel worksheet object

•an array of worksheet objects

•a collection of worksheet objects.

testFormulas   Required Boolean.  True if the comparison is to be made in terms of cell formulas. False if the comparison is to be made in terms of cell values.

ignoreCase.  Required Boolean. True if the comparison is to be sensitive to case, so that Dog and dog are reported as different. False if the comparison is to be case insensitive, so that Dog and dog are not reported as different.

styleA1.  Required Boolean. True if the reports produced by the comparison are to display formulas in A1 notation. False if they are to display formulas in R1C1 notation.  (The comparison is performed using R1C1 notation; this switch just controls how the differences are presented.)

alignRows   Required Boolean. True if OAK is to attempt to minimize the differences reported between the workbooks or worksheets by inserting blank rows to align the headings in the columns specified in rowAlignmentColumns before comparing the cell contents. False if OAK is to make its comparison without inserting any rows.

alignColumns   Required Boolean. True if OAK is to attempt to minimize the differences reported between the workbooks or worksheets by inserting blank columns to align the headings in the rows specified in columnAlignmentRows before comparing the cell contents. False if OAK is to make its comparison without inserting any columns.

writeReport Required Boolean.  True if OAK is to generate a new workbook summarizing the differences it has found. False if OAK is to generate no report workbook.

groupEquivalentChanges Required Boolean. True if OAK is to attempt to minimize the differences reported between the workbooks or worksheets by grouping blocks of equivalent differences, which is likely better for a human reader. False if OAK is to report each cell difference separately, which may be better for further machine processing of the OAK output.

rowAlignmentColumns Required Variant.  A string indicating which columns hold the headings to be used for row alignment, if alignRows is True, in the format "A:B".

columnAlignmentRows Required Variant.  A string indicating which rows hold the headings to be used for column alignment, if alignColumns is True, in the format "2:3".

Remarks

When applied to an IOAKAddIn object, the Compare method activates the same dialog box as is presented when the Compare command is selected manually from the OAK user interface.  The action that follows is controlled by the data gathered by the dialog box from the user.

When applied to an IOAKAPI object, the Compare method performs a comparison between the workbook or worksheets specified by the parameters submitted to the method.

One might expect the method has nothing to do if both alignRows and writeReport are set to False.  But that is not so; it needs to perform a comparison, without alignment, so that result2 (an ICompareResult object) will be defined, and in particular so that result2.TotalDifferences is accurate.

Example

The code fragment used as an example in the introduction to scripting shows OAK identifying which spreadsheet in a specified directory is most like the active workbook.  It illustrates the use of the Compare and UndoCompareModifications methods, and the ICompareResult interface.