March 23, 2018

Git-xltrail: An open-source Git command-line extension for Excel files

Posted by Björn Stiel Comments

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

Commands
--------

* 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
--- /dev/null
+++ b/Book1.xlsb/VBA/Module2
+Option Explicit
+
+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
+
+End Function
+

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

Upcoming features

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.

If you would like to see a feature, you are very welcome to open an issue or contribute to the project.

Documentation

Do you want more free Git Excel tips?
About Us
Free Products
Contact Details

Zoomer Analytics GmbH
Eichbühlstrasse 19
8004 Zurich
Switzerland

info@xltrail.com

© 2018 Zoomer Analytics LLC. All rights reserved.