I'm trying to incorporate the fancy new Spreadsheet Compare function from Excel 2013's Inquire Add-in, into a VBA script.
The plan is to have a macro to automate comparison between two spreadsheets with predefined names, and to export all the differences as a new spreadsheet.
Without success, to date.
Here's what I've tried so far:
Both of those have failed in this case. No code appeared relevant to the Spreadsheet Compare, when I recorded a macro (only the peripheral stuff like cell-select appeared). And none of the addable references looked anything like Spreadsheet Compare.
So how can I script Excel's 2013 Spreadsheet Compare, from VBA?
You can use the View Side-by-Side comparison function if your boss requires you to immediately find out the differences between the two Excel sheets. For example, this is book1 and this is book2, click on View Side-by-Side and you can manually find out the differences in their values row by row.
To compare and merge workbooks: Click the Compare and Merge Workbooks command on the Quick Access toolbar. If prompted, allow Excel to save your workbook. The Select Files to Merge into Current Workbook dialog box will appear. Select another copy of the same shared workbook you want to merge.
I opened a similar question for automating the Spreadsheet Compare tool from a .NET application, but I haven't found any other way yet than executing it from command-line.
You can do this from your VBA add-in. All you need is to locate the executable file SPREADSHEETCOMPARE.EXE (usually in C:\Program Files (x86)\Microsoft Office\Office15\DCF) and to execute it in command-line with an instruction file as input argument.
This instruction file must be an ASCII file with the two Excel file paths to compare written in separate lines.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With