Version control for Excel workbooks, Part 2
20 April 2009 2 Comments
(stackoverflow rep: 4824, Project Euler 72/241 complete)
Not a month ago (about seven , actually) I started to gibber about source code version control and Excel. Then Stuff got In The Way and I was derailed. Well, Ruby-on-Railed, really – I’ve been almost totally immersed in intranet development and related matters and mostly having lots of fun.
Then along came Internal Audit (shudder). Some degree of consternation appeared to exist about the potential (albeit slight) vulnerability of our Source Control solutions (SourceSafe for Visual Studio and Excel stuff, subversion for the webby bits). Combined with the newly-available – and rather sexy – new enterprise SourceForge that had materialised, we bit the bullet and set out to put everything into the Company-Approved Solution. Which dragged me seven months into the past to ask again – how does one diff an XLS?
That’s not a rhetorical question, I do have a sort of answer. I’m thinking along the following lines:
- Some VBA component-extraction code in an add-in
- Perform the export on a Save or Close
- Save VBA references as a separate text file
I’m not going to get into dumping worksheet content, although there are arguments for doing so.
Excel exposes the handy WorkbookBeforeClose
and WorkbookBeforeSave
events on the Application object. We’ll need an Application object and it’ll have to be declared WithEvents
, so we need a class. I’m going to call it WorkbookDecomposer:
Option Explicit Public wkbk As Workbook Public WithEvents xlapp As Application Private Sub xlapp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) Set wkbk = Wb Decompose End Sub
(We’ll have the same code for the Close event). Each component can be exported, and the references can be dealt with something like this:
Public Sub ExportReferences() Dim ref As Reference With New FileSystemObject With .OpenTextFile(.BuildPath(wkbk.path, wkbk.Name & ".references"), ForWriting, True) For Each ref In wkbk.VBProject.References .WriteLine RefToString(ref) Next End With End With End Sub
To start the thing, we can have a module with a little Sub such as:
Option Explicit Option Private Module Public decomposer As WorkbookDecomposer Public Sub StartDecomposer() Set decomposer = New WorkbookDecomposer Set decomposer.xlapp = Application End Sub
Now, as long as we don’t lose state, running this code once will give us an object that will automatically export copies of our code for storing separately in our code control system. So we can diff. The decomposer can be extended to shell out useful source control instructions such as commit, perhaps automatically, perhaps not.
I don’t like that “as long as we don’t lose state” thing. When we’re coding in VBA we lose state all the time. The best I can come up with is to use an OnTime macro to keep trying to restart the decomposer object:
If decomposer Is Nothing Then Set decomposer = New WorkbookDecomposer Set decomposer.xlapp = Application Debug.Print "Decomposer (re)started @ " & Now End If Application.OnTime Now + TimeSerial(0, 0, 10), "StartDecomposer"
It sort of works, but it’s potentially intrusive when we have incomplete/invalid code (hardly unlikely in the scenario under consideration) and the OnTime macro may fail to fire for many reasons.
What are the alternatives? Custom external scripts? An external process that monitors changes on XLS files and auto-exports?