Building Version Control in Excel

February 15, 2010 by datapig Leave a reply »

One of the hardest things to manage in Excel solutions is version control. You know, that embarrassing knowledge that there are orphaned versions of your application floating through your company. And that horrifying sense that these useless versions are actually being used. There is nothing more embarrassing than asking one of your users to try and catch the splash screen to read off the version number. That's just sad dude…sad.

In this post, I'll show you a couple of techniques that will help you hack your own version control for your Excel solutions (BTW: This works for Access too).

The Basic Idea

The basic idea behind this technique is actually very simple. You place a version number internally into your Excel solution. Then you keep a version number in network file or a server table.

When your Excel solution opens, it calls back to your server/network file and looks at the version number there. If the internal number matches, the external number, then the Excel solution continues. If the two numbers don't match, then Excel pops up a message and quits.

There are certainly dozens of ways to get this to work. However, I'll show you two: A method for those of you who don't use a database server, and a method for those of you who have access to a database server.

I don't have a database server:

If you don't have the luxury of a database server, you can implement version control using an XML file. Here are the steps:

Step 1: Create your XML file

Open up Notepad or some other text editor and enter this XML code. Notice the Version element (in line three) is tagged with a 1.

PLAIN TEXT

Visual Basic:

<VersionCheck>
<Version>1<Version>
</VersionCheck>

As you create new versions of your application, the idea is to edit this XML code to increment the version number.

Step 2: Save your XML file

Save your Notepad file as a VersionCheck.xml and place it into a location your users can get to. Typically, this means saving your XML file onto a shared drive or a network directory.

Step 3: Create an XML Map to Your VersionCheck File

Open your Excel program/solution, then go to the Developer tab on the Ribbon. There, you will click Import under the XML group.

Select the XML file you are mapping.

Then assign a place to park it. In this case, I'll just pick A1.

Make sure you give the tab with your XML map a distinct name. Here, I'm using VersionCheck.

Step 4: Build an On Open Event

Once you have your XML map in place, you need to create an Open event for the workbook that will:

Refresh the XML Map
Compare the internal version number with the external number
Close if there is no match

Open the VBE and click "ThisWorkbook". Then choose Workbook from the Object selector.

Enter the following code. Notice that you are setting a Constant called ThisVersion. This is what you'll use as the internal version number.

PLAIN TEXT

Visual Basic:

Private Sub Workbook_Open()
Const ThisVersion As Integer = 1 '<--Change this internal version number
'Refresh XML Binding
ActiveWorkbook.XmlMaps("VersionCheck_Map").DataBinding.Refresh
'Exit if external version number doesn't match internal number
If Sheets("VersionCheck").Range("A1").Value <> ThisVersion Then
MsgBox "Please download the current version", vbCritical, "You don't have the lastest version!"
ThisWorkbook.Close SaveChanges:=True
Exit Sub
End If
End Sub

The idea is that each time you build a new version of your application; you will increment both the internal ThisVersion constant and the number in the XML - so they match.

Any user using the latest file, will have the correct match. Any user using an older file will get a message telling them to get the new version.

I have a database server:

If you've got access to a database server, then it's a bit simpler for you.

First, create a new table in your database called VersionCheck, with one field called Version. Fill it with the number 1.

Next, enter this code in the workbook's Open Event.

PLAIN TEXT

Visual Basic:

Private Sub Workbook_Open()
'Be Sure to set a Reference to the Microsoft ActiveX Data Objects Library.
Dim MyRecordset As ADODB.Recordset
Dim MySQL As String
'Change this to the appropriate database connection string
Const MyConnection As String = "Provider=sqloledb;" & _
"Data Source=204.204.20.24;" & _
"database=SOSDB;" & _
"User ID=SOSUSER;" & _
"Password=SOSPASSWORD"
Const MyVersion As Integer = 1 '<;--Change Version Number Here
'Start Recordset and connect to server
Set MyRecordset = New ADODB.Recordset
MySQL = "SELECT [Version] FROM [dboVersionCheck]"
MyRecordset.Open MySQL, MyConnection, adOpenStatic, adLockReadOnly
'Exit if external version number doesn't match internal number
If MyRecordset!Version <> MyVersion Then
MsgBox "Please download the current version", vbCritical, "You don't have the latest version!"
Exit Sub
End If
End Sub

Notes:
- Obviously, your users will need to be able to hit the designated external server or network file for this to work.
- As a courtesy, I typically take the user to the download site automatically. But that's for another post.

- I know about SharePoint version control - this is different.

If you have a different/better way to handle version control in your applications, I'd love to hear it.

RELATED STUFF

Posted in Excel VBA

Tags: Excel Power Tips VBA

You can follow any responses to this entry through the RSS 2.0 Feed feed. You can leave a response, or trackback from your own site.

12 Responses

Add your comment

Daniel says:

February 15, 2010 at 3:29 pm

For version control/automatic updates, I have implemented a class that wraps the WinHTTP API, and use it to download a plain TXT file from the download server. The version number is in the first line of the text file; subsequent lines contain a summary of the changes that is displayed in the userform, so that the users can decide whether they want to update or not. The same clsWinHTTP class is also used to download the update itself. All in all, users have to click just once, the rest is automatic.

"And that horrifying sense that these useless versions are actually being used." -- In those cases where users send me feedback, I'm often surprised what old versions are still being used. I think a lot of people have updating angst!
Drew says:

February 15, 2010 at 5:00 pm

@daniel

I would love to see in action. Can you post some example code in here? Really the part I'm interrested is the auto downloading of the new version. We don't have a HTTP server so I would like to use datapig's version control along with the ability to auto download.

Thanks
dermotb says:

February 15, 2010 at 5:09 pm

Sometimes your updates won't be critical, so you may want to specify a minimum version required - any version older than this will not run, while any version that is newer will simply tell you there is a newer version, and suggest you get it.

Using a spreadsheet to download a newer version of itself is kinda tricky, so one solution is to launch your app with a separate little spreadsheet that checks versions and does any updating, then it launches the main spreadsheet.

Oh, and I might just save the version number as text in an ordinary text file or web page. No need for databases and XML unless you're building industrial strength software.
Daniel says:

February 16, 2010 at 1:58 am

Drew,

to see my autoupdate in action, you can download an older release of my addin (e.g.: http://sourceforge.net/projects/xltoolbox/files/XL_Toolbox_2.62b.exe/download ) and install it. When you start up Excel (and have an internet connection), you should see the update prompt immediately. The addin is open source, so you can examine the code yourself.

As dermotb pointed out, updating "in place" is essentially not possible. My addin downloads the update installer (generated with InnoSetup), then starts the installer with a special "/update" command line switch. This causes the installer to shut down Excel (by sending a WM_CLOSE message through the Windowing system). When the setup process is finished, the installer restarts Excel.
Roy MacLean says:

February 16, 2010 at 1:46 pm

Interesting. Generalizing the pattern, you could use the XML-Map Refresh to perform any initialization with current data. For example, you could load a set of active jobs into a timesheet workbook.
jamescox says:

February 18, 2010 at 1:12 am

Nope, that's no better - and there isn't a 'preview post' button to help me with trial and error.

Therefore, I surrender (somewhat gracefully) and if anyone is actually interested in what the .xml structure looks like, we'll figure out a way...
datapig says:

February 18, 2010 at 1:52 am

James: try again using these vb tags without the quotes

"["vb"]"
Code goes here
"[/"vb"]"
JP says:

February 18, 2010 at 8:20 am

Jan Karel Pieterse has an article on his site about using the URLDownloadToFile API to check a website for updates and grab the latest file. Can't find a link right now though.
jamescox says:

February 18, 2010 at 6:48 pm

I have a need to do version control in other-than-Office applications that do use VBA, and so created

an all-VBA (no worksheet) version that doesn't use a database server. In general, there will be more

than one application that needs version control, the following .xml file structure was created:

PLAIN TEXT
Visual Basic:


<programs>

<program>

<progName>So-So App.xlsm</progName>

<progLocation>H:\Abandon\All\Hope\</progLocation>

<curVersion>3</curVersion>

<curVerDate>2009-11-12</curVerDate>

<minVersion>1</minVersion>

<users1>Alice Appleby, Betty Barton</users1>

<users2>Charlie Clark, Dan Delta</users2>

<resPerson>Fred J. Muggs</resPerson>

</program>

<program>

<progName>Nice App.xlsm</progName>

<progLocation>H:\Abandon\All\Hope\</progLocation>

<curVersion>4</curVersion>

<curVerDate>2009-12-16</curVerDate>

<minVersion>1</minVersion>

<users1>Eloise Eckert, Fred Friendly</users1>

<users2>Garry Goose</users2>

<resPerson>Mortimer Snerd</resPerson>

</program>

<program>

<progName>Killer App.xlsm</progName>

<progLocation>H:\Abandon\All\Hope\</progLocation>

<curVersion>2</curVersion>

<curVerDate>2010-01-23</curVerDate>

<minVersion>2</minVersion>

<users1>Harry Harrison, Ivan Iverson, Janice James</users1>

<users2>Kevin Killough</users2>

<resPerson>Joe Friday</resPerson>

</program>



</programs>
PLAIN TEXT
Visual Basic:
Option Explicit

'Note that as new versions of this workbook are created, the sgVersion value below will need to be changed here and in the VersionMinder.xml file

Public Const sgVersionMinder As String = "J:\AllAccess\Read-Only\VersionMinder.xml"

Public Const sgVersion As String = "1"

Public Const sgProgName As String = "Killer App.xlsm"

Public Sub VersionChecker()

Dim objXML As MSXML2.DOMDocument

Dim objProgList As MSXML2.IXMLDOMNodeList

Dim objProg As MSXML2.IXMLDOMNode

Dim sElementTxt As String

Dim sprogName As String

Dim sprogLocation As String

Dim scurVersion As String

Dim scurVerDate As String

Dim sminVersion As String

Dim susers1 As String

Dim susers2 As String

Dim sresPerson As String

Dim sMsg As String

Dim lI As Integer

'Optional use of a global program name constant (see above) or if you want to enforce a no-renaming policy, go with the ThisWorkbook.Name option

sprogName = sgProgName

' sprogName = ThisWorkbook.Name

Set objXML = New MSXML2.DOMDocument

If Not objXML.Load(sgVersionMinder) Then

Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason

End If

Set objProgList = objXML.SelectNodes("programs/program")

For Each objProg In objProgList

If objProg.SelectSingleNode("progName").Text = sprogName Then

'We have a winner! - so cache the program element texts (Note that if the element is present but there is no entry - eg, <minVersion></minVersion> - it is rendered as a "" string

'Note that for ChildNodes, .Length is the equivalent of .Count for other Excel collective objects, and that in walking the collection, the first index number is zero

For lI = 0 To objProg.ChildNodes.Length - 1

sElementTxt = objProg.ChildNodes(lI).Text

Select Case objProg.ChildNodes(lI).BaseName

Case "progName"

'No Op - we already know the program name

Case "progLocation"

sprogLocation = sElementTxt

Case "curVersion"

scurVersion = sElementTxt

Case "curVerDate"

scurVerDate = sElementTxt

Case "minVersion"

sminVersion = sElementTxt

Case "users1"

susers1 = sElementTxt

Case "users2"

susers2 = sElementTxt

Case "resPerson"

sresPerson = sElementTxt

Case Else

MsgBox "A new element has been added to the VersionMinder.xml file. There should be a new version of this program for you to download."

End Select

Next lI

Exit For 'Each

End If

Next

If sprogLocation = "" Then

sMsg = "No version information for this workbook was found. If you have changed the name from " & vbLf & vbLf & sgProgName & vbLf & vbLf

sMsg = sMsg & "please rename it as shown above. Keeping the same name will let it inform you when there is a new version available."

MsgBox sMsg, vbCritical, "Version Verification Failed!"

ThisWorkbook.Close SaveChanges:=False

End If

'Do whatever you want to do in terms of just notifying the user and continue running, up to closing the workbook.

If scurVersion <> sgVersion Then

sMsg = "The version of this workbook you are running is version " & sgVersion & vbLf & vbLf

sMsg = sMsg & "Version " & scurVersion & " created on " & scurVerDate & " is available at " & vbLf & vbLf & vbTab & sprogLocation & vbLf & vbLf

sMsg = sMsg & "If you need assistance, you may contact " & sresPerson & ", the person currently responsible for maintaining this workbook."

MsgBox sMsg, vbCritical, "Outdated version detected!"

'In BOFH mode, you could close the workbook at this point

End If

'Environ$("username") could be checked against susers1 as a whitelist of users who are allowed to run the workbook or against susers2 as a blacklist of users who aren't allowed to run it. ("I have no idea why you can't run the workbook - your username is right there in the users2 element!

'Alternately, Environ$("computername") could be used and checked against either susers1 or susers2 to restrict the workbook to be run from specific computers.

End Sub
Supposedly, it's possible to select a single node by spedifying an element value with syntax in VBA that looks like:

Dim objElement As MSXML2.IXMLDOMElement
Set objElement = objXML.SelectSingleNode("//*[@progName=""TXO_Gate_Compliance_Analyzer.xlsm""]")

But I've not been able to puzzle out any version that works. If it DID work, it would (probably) be faster than the looping over all program elements implemented below. If anyone knows what the correct syntax is, I'd love to learn it!

Can anyone take a stab at the right syntax?

BTW - lots of credit to Tim Hastings' blog entry at http://www.nonhostile.com/howto-xml-vb6.asp - it gave

me a big leg up on getting this to work...
Larph says:

September 12, 2010 at 5:22 pm

Great solution to one of my biggest headaches - are you planning on following up with how to take users to the latest version automatically?!

Thx.

Bacon Bits: