16

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:

  1. Normally, to learn how to automate some Excel functionality, I use Record Macro.
  2. If that fails, I look down the list of addable references, to see if I'm missing something obvious.

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?

410 gone
  • 782
  • 14
  • 25
  • 2
    +1 great question. I will look at this at the weekend when I get home and get access to xl13. – brettdj Dec 11 '12 at 10:48
  • 1
    [I deleted my previous comment as it was gibberish...] The method calls will be available since it's a COM addin so if all else fails you can tear into the dll using visual studio to see what methods it exposes then automate it from vba like you would a FileSystemObject. Failing that nothing I've read about the addin for excel would be impossible to reproduce if you threw the right XML transforms around. – James Snell Dec 11 '12 at 20:15
  • That's the two methods I also use to do automation. What actual problems are you having? code didn't appear when recording a macro? code appeared but is too complicated? Can't find the COM add in in the references? – Nick.Mc Dec 13 '12 at 00:55
  • @ElectricLlama thanks for the prompt - I've updated the question with the answers to your questions – 410 gone Dec 13 '12 at 08:17
  • Speardsheet compare Link rot. This is an old post but if you have a fixed set of parameters it would not take long to write. – Reverend_Dude Jun 30 '15 at 21:27
  • @Reverend_Dude thanks, link fixed. Sadly, no fixed set of parameters. – 410 gone Jul 01 '15 at 06:42

3 Answers3

3

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.

Community
  • 1
  • 1
JulienVan
  • 881
  • 1
  • 11
  • 30
  • I invoked SPREADSHEETCOMPARE.EXE with the instruction file pointing to 2 excels that do have a difference, but there is no output file. Where does it store the output of any differences found??? – David Pesetsky Aug 22 '22 at 20:55
1

You can't.

VBA does not cover add'ins as in this case.

Spreadsheet compare is a 3rd party plugin which got swallowed by Microsoft.

If you need scriptable compare you can find those which do for each cell, for each row... kind of thing on the net.

Archlight
  • 2,019
  • 2
  • 21
  • 34
1

  1. Create a runCompare.cmd file:

    REM Execute from command line spreadsheetcompare.exe 
REM 
cd C:\Program Files (x86)\Microsoft Office 2013\Office15\DCF
spreadsheetcompare.exe  C:\reportNames.txt

  2. In C:\reportNames.txt, save in the same line the .xlsx files you wish to compare:

    C:\fileA.xlsx C:\fileB.xlsx

  3. Execute runCompare.cmd.

Pang
  • 9,564
  • 146
  • 81
  • 122
mercedes
  • 169
  • 1
  • 5
  • 1
    I couldn't get this to work until I placed the filenames on 2 SEPARATE LINES. – circlepi314 Jun 10 '21 at 20:50
  • I invoked SPREADSHEETCOMPARE.EXE with the instruction file pointing to 2 excels that do have a difference, but there is no output file. Where does it store the output of any differences found??? – David Pesetsky Aug 22 '22 at 20:56