VSTS Compare Excel Spread Sheets

I have many customers who keep spread sheets in version control for various reasons. Why they do it is not the purpose of this post. The point is about using the Compare feature in TFS/VSTS on Excel Spread sheets. I recently discovered an easy way to compare the difference between two spread sheets, and how to hook that up to Visual Studio so I could right click compare right from Team Explorer.

This is for Office 2016. You can do it for older versions however the location of SpreadSheetCompare.exe may be different.

Here is the trick:

Write a batch file that writes the file names of the two files you want to compare to another file. Then call SpreadSheetCompare.exe passing in the file containing the two file names.

dir %1 /B /S > c:\temp\temp.txt 
dir %2 /B /S >> c:\temp\temp.txt 
"C:\Program Files (x86)\Microsoft Office\Root\Office16\DCF\SPREADSHEETCOMPARE.EXE" "c:\temp\temp.txt"

You can store the batch file in version control that way as long as the users get latest on this file they will have it locally. Or just give it to the people who want to do this and they can put it anywhere on their machine.

Hook this up to the Compare menu item in Team Explorer and VS: In Visual Studio open Tools –> Options, navigate to the “Source Control” section specifically “Visual Studio Team Foundation Server”. Open the “Configure User Tools…” dialog.

Add the .xlsx file extension calling your new batch file and passing in the two files to compare (default behaviour).

image

Now when you right click compare an Excel spread sheet in version control you will launch the SpreadSheetCompareTool that comes with office.

The link is to a video showing that. Which I tried to imbed in my blog but there are issues apparently that I don’t have time to research right now.