VBScript: Compare 2 Excel Files

by Anshoo Arora ON March 22, 2010 · Posted In All, QTP, VBScript · 75 comments

This post shows a quick and easy way to compare 2 Excel files. I know there are several code snippets on forums that show how to compare do this, so you may ask the following question: how is this technique any different? What makes this technique different is performance. I know there may be faster ways to achieve this, but after much experiment, this was the one that continuously worked at the best speed in comparison to other methods.

The main concept of this technique is performing all validations through a range object, instead of traversing all cells of an Excel sheet (which takes a little longer). Below is the skeleton of how the class is arranged, with documentation. Please use one of the download links above to download this class. There is a usage example shown at the end of this article.

Class clsExcelComparer

Class clsComparer
	'[--- Region Private Variables Start ---]

	Private oExcel		'Excel.Application
	Private arrRangeUno	'Range.Value (array) of the Primary Excel spreadsheet
	Private arrRangeDos	'Range.Value (array) of the Secondary Excecl spreadsheet
	Private oDict		'Scripting.Dictionary containing unmatched cells

	'[--- Region Private Variables End ---]

 
	'[--- Region Public Variables Start ---]

	Public Operation	'0: Only Compare   1: Compare & Highlight Differences

	'[--- Region Public Variables End ---]

 
	'--------------------------------------------------------
	' Name: Function Compare [Public]
	'
	' Remarks: N/A
	'
	' Purpose: Compares differences between 2 Excel Spreadsheets
	'	
	' Arguments:
	'	sWorkBookUno: Primary Excel WorkBook (with complete path)
	'	vSheetUno: Primary Excel Spreadsheet Name
	'	sWorkBookDos: Secondary Excel WorkBook (with complete path)
	'	vSheetDos: Secondary Excel Spreadsheet Name
	'
	' Return: Boolean
	'
	' Author: Anshoo Arora, Relevant Codes
	'
	' Date: 03/17/2010
	'
	' References: N/A
	'--------------------------------------------------------
	Public Function Compare(sWorkBookUno, vSheetUno, sWorkBookDos, vSheetDos)
 
	End Function
 
	'--------------------------------------------------------
	' Name: Function CellsFound [Private]
	'
	' Remarks: N/A
	'
	' Purpose: Finds the dissimilar cells between 2 sheets
	'	
	' Arguments: N/a
	'
	' Return: Integer
	'
	' Author: Anshoo Arora, Relevant Codes
	'
	' Date: 03/17/2010
	'
	' References: N/A
	'--------------------------------------------------------
	Private Function CellsFound()
 
	End Function
 
	'--------------------------------------------------------
	' Name: Sub Class_Terminate [Private]
	'
	' Remarks: N/A
	'
	' Purpose: Disposes the Excel.Application object
	'	
	' Arguments: N/A
	'
	' Author: Anshoo Arora, Relevant Codes
	'
	' Date: 03/17/2010
	'
	' References: N/A
	'--------------------------------------------------------
	Private Sub Class_Terminate()
 
	End Sub
 
End Class
 
'--------------------------------------------------------
' Name: Function CompareExcelSheets
'
' Remarks: N/A
'
' Purpose: Constructor for Class clsComparer
'	
' Arguments:
'	sWorkBookUno: Primary Excel WorkBook (with complete path)
'	vSheetUno: Primary Excel Spreadsheet Name
'	sWorkBookDos: Secondary Excel WorkBook (with complete path)
'	vSheetDos: Secondary Excel Spreadsheet Name
'	Operation: 0: Compare Only   1: Compare & Highlight Differences
'
' Return: Boolean
'
' Author: Anshoo Arora, Relevant Codes
'
' Date: 03/17/2010
'
' References: N/A
'--------------------------------------------------------
Function CompareExcelSheets(sWorkBookUno, vSheetUno, sWorkBookDos, vSheetDos, Operation)
 
End Function

Download clsComparer (772) | View clsComparer (724)

Usage (Example)

Consider the following 2 Excel Sheets, with a few dissimilar cells:

Sheet #1 Original

Sheet #2 Original

There are 2 main operations that can be done through our code. 1. We can simply check if both Excel Sheets are similar and 2. we can check as well as highlight the differences between the two. To only compare the 2 sheets, without highlighting the differences the Operation argument will have a value of 0. On the other hand, to compare as well as highlight the values, the Operation argument will have a value of 1. Let’s first consider a case where the 2 sheets are only to be compared:

Compare Only

'Only Compare:
MsgBox CompareExcelSheets("C:\Test1.xls", "Sheet1", "C:\Test2.xls", "Sheet1", 0)

Because the 2 sheets are not similar, the following will be the output upon execution of the above statement:

Compare Only

Also, the 2 sheets will remain unchanged.

When changing the value of Operation to 1, we will notice that, not only does the code compare the 2 sheets, but also highlights the differences.

Compare and Highlight Differences

'Compare and highlight differences:
MsgBox CompareExcelSheets("C:\Test1.xls", "Sheet1", "C:\Test2.xls", "Sheet1", 1)

Upon execution of the above statement, the following 2 sheets will be highlighed in areas where they differ:

Sheet #1 With Highlighted Differences

Sheet #2 with Highlighted Differences

Limitations

1. This code will work with Excel Sheets with the same number of rows and columns in each (like ranges).

Update: RelevantCodes.Comparer COM DLL

Download RelevantCodes.Comparer

Snapshot of the assembly can be found here.

To register the assembly to your PC, the following code can be used:

RegAsm C:\RelevantCodes.dll /codebase

or

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe C:\RelevantCodes.dll /codebase

Once done, the code can be used in QTP like this:

Set oCompare = CreateObject("RelevantCodes.Comparer")

oCompare. should give you the proper intellisense to use all methods. The assembly compares the following items:

Arrays
Excel Sheets
Images
Integers
Strings
TextFiles

I hope you find this useful :)

Tagged as: Check Differences Between Excel Sheets, Compare, Compare 2 Excel Sheets, Compare Excel, Compare Excel Books, Compare Two Excel Sheets, Excel.Application, VBScript, VBScript Excel

Leave a Comment

{ 73 comments… read them below or add one }

Ameena February 19, 2012 at 5:21 pm

Anshoo,

How formula can be applied to a sheet full of numbers
=(A1*5.5%)+A1

Regards

Ameena

VBScript: Compare 2 Excel Files

Class clsExcelComparer

Usage (Example)

Compare Only

Compare and Highlight Differences

Limitations

Update: RelevantCodes.Comparer COM DLL

Tags

Recent Comments

Subscribe

CONNECT