By Björn Stiel, CTO at Zoomer Analytics (Mar 23, 2018)
Git pre-commit hooks and Spreadsheet Compare
Git can be tweaked to make it play nicely with Excel workbook files (that is, to be able to show diffs). Pre-commit hoooks can be set-up to automatically export the workbook’s VBA code as stand-alone .bas files on each commit. And Microsoft’s Spreadsheet Compare can be used as a custom differ for Excel workbook file changes.
However, these tweaks require some technical plumbing work which has to be repeated for each and every user and computer.
Introducing git-xltrail: An open-source Git command-line extension
In order to make Git Excel-ready out of the box, we created git-xltrail, an open-source, free Git extension. git-xltrail comes with a custom Excel workbook differ that understands the VBA code inside your workbook (handling spreadsheets is on the roadmap).
Also, git-xltrail takes care of the correct Git configuration so that all the Excel oddities (such as temporary files) are handled correctly. Currently, git-xltrail supports Windows only, but we are working on a Mac version.
To get started, download and run the latest installer. This installs the git-xltrail extension and the custom workbook differ on your computer, and configures your environment variables so that Git can find the extension (you can find more information here.
As soon as the installation process is complete, open a new command-line window and run git xltrail to display the help page:
C:\Users\Bjoern\Developer> git xltrail
git-xltrail/0.1.0 (windows; Python 3.6.4;)
git xltrail 
Git xltrail is a system for managing Excel workbook files in
association with a Git repository. Git xltrail:
* installs a special git-diff for Excel files
* makes Git ignore temporary Excel files via .gitignore
* git xltrail install:
Install Git xltrail.
* git xltrail uninstall:
Uninstall Git xltrail.
* git xltrail version:
Report the version number.
Getting started with git-xltrail
Run git xltrail install once to make git-xltrail work across all your (existing and new) repositories. Alternatively, you can install git-xltrail on a per-repository basis. In the repository’s root folder, run: git xltrail install --local.
When installing git-xltrail in local mode, git-xltrail creates .gitattributes (or amends it if it already exists). Make sure .gitattributes is tracked as part of your repository.
Let’s have a look at what git-xltrail does (the examples are available at https://github.com/ZoomerAnalytics/git-xltrail-examples but you can really do it with any git repository that contains Excel workbook files).
C:\Users\Bjoern\Developer> git diff 04b45b99c883e5d184a20cfd73e4556ef8d06bfd 429ee1ff383b8c706aa69c6a87f3a2c50fa1bcd1
diff --xltrail a/Book1.xlsb b/Book1.xlsb
+Function Count_once(Count_range As Range) As Long
+Dim strAddress As String
+Dim lMaxRow As Long, lEndCol As Long, lStartCol As Long
+Dim lColCount As Long
+Dim lLoop As Long, lArrElement As Long
+Dim lArray() As Long
+ lMaxRow = Rows.Count
+ lColCount = Count_range.Columns.Count
+ lEndCol = Count_range.Columns(lColCount).Column
+ ReDim lArray(lColCount)
+ lStartCol = Count_range.Columns(1).Column
+ For lLoop = lStartCol To lEndCol
+ lArray(lArrElement) = Cells(lMaxRow, lLoop).End(xlUp).Row
+ lArrElement = lArrElement + 1
+ Next lLoop
+ lMaxRow = WorksheetFunction.Max(lArray)
+ strAddress = Range(Count_range.Cells(1, 1), _
+ Cells(lMaxRow, lEndCol)).Address
+ Count_once = Evaluate("sumproduct((" & strAddress & "<>"""")/" _
+ & "countif(" & strAddress & "," & strAddress & "&""""))") - 1
Play around with it by editing the VBA inside your working copy’s Book1.xlsb and/or the text file README.md in the repository and diff your working copy versus the head version:
C:\Users\Bjoern\Developer> git diff
You are now in full control of your Excel VBA changes. What’s next for git-xltrail? We are planning to support MacOS and also to extend the diff functionality to worksheets. Merging is another topic on the roadmap.