Automate xlCompare with Visual Basic Scripts

xlCompare gives you an easy and efficient way to automate your job. xlCompare uses Visual Basic Macros, well-known to every Microsoft Excel user, to extend abilities of the software.
Instead of Microsoft Excel, which stores macros inside workbook, xlCompare uses macros stored on the disk as text files with .VBS extension. VBS means Visual Basic Script.
Use Run Macro command on the Tools tab to access list of available macros.
After using this command xlCompare gives you this window:
Here you can run the macro you need of edit this list:
Run
The most important command. It runs selected macro.
As an alternate way to can select macro in list and double click with mouse or press Enter to run it.
Add
Browse for .vbs file on disk and add it to this list.
Delete
Remove selected macro from list.
Open in Folder
Open this file in Windows Explorer. Use this feature when you need to open script file for modification.
Close
Closes window without running macro.

How to create new macro?

  1. Create VBS file on disk. You can create text file and rename it's extension.
  2. Open it with Notepad or other edit which is suitable for editing Visual Basic scripts.
  3. Write text of your macro.
  4. Note: Your code should have Main procedure, which is executed by xlCompare.
  5. Save file on disk.
  6. Use Tools | Run Macro in xlCompare.
  7. Press Add button and add your file to xlCompare.
  8. Press Run button to run script.

Differences between xlCompare and Microsoft Excel objects

Object Model used by Microsoft Excel and xlCompare is almost similar. xlCompare support all Excel's objects. So, if you know how to write macros for Excel, there will be no problems with writing macros for xlCompare.
The only difference is, that Excel has one worksheet in one Window. xlCompare has 2 worksheets opened in same window for comparison. So, you need to access Workbooks and Windows using Panel object.
What is Panel?
Worksheets you see on the left and on the right side in xlCompare are 2 Panel objects.
Panel Object members:
  • Book - Workbook opened in panel. This object is the same as Workbook object in Excel
  • Window - Window object associated with panel. This object is the same as Window object in Excel
How to access it?
  • Application.Panel( index ) returns Panel object. Index 1 means left panel and 2 right panel. All other values are invalid.
  • Application.ActivePanel returns Panel object which has input focus at this moment.
Example:
Replace text in the column A of selected worksheet. 
			Sub Main()

				call Application.ActivePanel.Window.ActiveSheet.Range("A:A").Replace( "what", "with" )

			End Sub
Please, contact us if you need our assistance in automating xlCompare.