Best way to do Version Control for MS Excel

Question

What version control systems have you used with MS Excel (2003/2007)? What would you recommend and Why? What limitations have you found with your top rated version control system?

To put this in perspective, here are a couple of use cases: 1. version control for VBA modules 2. more than one person is working on a Excel spreadsheet and they may be making changes to the same worksheet, which they want to merge and integrate. This worksheet may have formulae, data, charts etc 3. the users are not too technical and the fewer version control systems used the better 4. Space constraint is a consideration. Ideally only incremental changes are saved rather than the entire Excel spreadsheet.

Answer 1

Use any of the standard version control tools like SVN or CVS. Limitations would depend on whats the objective. Apart from a small increase in size of the repository, i did'nt face any issues

Answer 2

It depends whether you are talking about data, or the code contained within a spreadsheet. While I have a strong dislike of Microsoft's Visual Sourcesafe and normally would not recommended it, it does integrate easily with both Access and Excel, and provides source control of modules.

[In fact the integration with Access, includes queries, reports and modules as individual objects that can be versioned]

The MSDN link is here.

Answer 3

It depends on what level of integration you want, I've used Subversion/TortoiseSVN which seems fine for simple usage. I have also added in keywords but there seems to be a risk of file corruption. There's an option in Subversion to make the keyword substitutions fixed length and as far as I understand it will work if the fixed length is even but not odd. In any case you don't get any useful sort of diff functionality, I think there are commercial products that will do 'diff'. I did find something that did diff based on converting stuff to plain text and comparing that, but it wasn't very nice.

Answer 4

It should work with most VCS (depending on other criteria you might choose SVN, CVS, Darcs, TFS, etc), however it will actually the complete file (because it is a binary format), meaning that the "what changed" question is not so easy to answer.

You can still rely on log messages if people complete them, but you might also try the new XML based formats from Office 2007 to gain some more visibility (although it would still be hard to weed through the tons of XML, plus AFAIK the XML file is zipped on the disk, so you would need a pre-commit hook to unzip it for text diff to work correctly).

Answer 5

If you are looking at an office setting with regular office non technical users than Sharepoint is a viable alternative. You can setup document folders with version control enabled and checkins and checkouts. Makes it freindlier for regular office users.

Answer 6

One thing you could do is to have the following snippet in your Workbook:

Sub SaveCodeModules()

'This code Exports all VBA modules
Dim i%, sName$

With ThisWorkbook.VBProject
    For i% = 1 To .VBComponents.Count
        If .VBComponents(i%).CodeModule.CountOfLines > 0 Then
            sName$ = .VBComponents(i%).CodeModule.Name
            .VBComponents(i%).Export "C:\Code\" & sName$ & ".vba"
        End If
    Next i
End With

End Sub

I found this snippet on the Internet.

Afterwards, you could use Subversion to maintain version control. For example by using the command line interface of Subversion with the 'shell' command within VBA. That would do it. I'm even thinking of doing this myself :)

Answer 7

I'm not aware of a tool that does this well but I've seen a variety of homegrown solutions. The common thread of these is to minimise the binary data under version control and maximise textual data to leverage the power of conventional scc systems. To do this:

• Treat the workbook like any other application. Seperate logic, config and data.
• Separate code from the workbook.
• Build the UI programmatically.
• Write a build script to reconstruct the workbook.

Answer 8

http://www.codeproject.com/KB/office/SourceTools.aspx

Answer 9

in response to mattlant's reply - sharepoint will work well as a version control only if the version control feature is turned on in the document library. in addition be aware that any code that calls other files by relative paths wont work. and finally any links to external files will break when a file is saved in sharepoint.

Answer 10

Turns out this is a really important question!

The need is for all the stuff we use in coding, but with emphasis on visibility, auditibility, and correctness. Excel errors are so insidious because the binary contains data, "code," and results all-in.

I'm at my best developing software, but I have a strong (and painfully gained) background in Quantitative Finance. So I end up rubbing shoulders with accounting and finance types quite often.

Working directly for the CFO of a Fortune 50, kind of his pet coder/analyst/Chief Truth Teller (hay my ego goes where the bank balance grows...). This guy would sell his mother to get some control of spreadsheets just within his own organization, let alone the whole company. I found many ways to version Excel and any required artifacts, but nothing that would help with those key terms "visibility, audit-ability, and correctness."

Our answer at the moment is to take the most important spreadsheets and other home-growns (like BASIC programs written by some department heads kid, SAS procedures done by admins for god's sake!) deconstruct and refactor, ultimately running the needed result into a BI or centrally managed analytic tool with good reporting. This takes a lot of time, but it will pay off. In 6 months, my team of 3 have reviewed about 500 odd-ball reporting/analytic spreadsheets/databases/programs; 37% had some errors and about 20% presented totally incorrect conclusions... and that by finance types who should know better. This is a Global 50 company, kids!

One Possible Answer

I still don't have an Excel repository and differencing tool to recommend. Since I often use excel as a great modeling tool for financial applications, I too needed something. For now its CVS, but thats just file control. In the near future I'm moving all of my personal professional stuff to PALO/Jedox, a BI/DW front-ended by excel. It's overkill for my use, but has a nice, clean audit and repository capability. If you have any solid experience to offer, please feel free to email me at TheOldQuant{a|t}g o o g l e {d|o|t} c o m.

Answer 11

TortoiseSVN is an astonishingly good Windows client for the Subversion version control system. One feature which I just discovered that it has is that when you click to get a diff between versions of an Excel file, it will open both versions in Excel and highlight (in red) the cells that were changed. This is done through the magic of a vbs script, described here.

You may find this useful even if NOT using TortoiseSVN.

Answer 12

I have been looking into this too. It apears that the latest Team Foundation Server 2010 may have an Excel Add-In.

Here is a clue:

http://team-foundation-server.blogspot.com/2009/07/tf84037-there-was-problem-initializing.html

I may be attempting this soon; so feel free to contact me at [email protected]

Regards Jim Slavens