Manage spreadsheet versioning

Question

We have a lot of VBA code in spreadsheets and a lot of time people save them to local drives. When we want to upgrade the spreadsheets we push a new version out to a shared drive but dont have any way of enforcing that people dont use the old versions of the spreadsheets.

Is there some best practice here to deploy vba spreadsheets so if someone loads an old version it wont open or will ask you to upgrade. It seems like this must be an issue for any custom solution so i would have through MS would have some solution here.

Does microsoft have a standard versioning / deployment solution for this or do i need to come up with some home grown solution (spreadsheet pings a database on startup to check version)

Answer 1

Though I've not done this. I've see people do something similar where they store the code in dlls in the database, and then verify that the local ones are the same on start up.

Answer 2

Add a custom property to the workbook, such as a GUID, Integer, Date, or whatever you need. On startup, check the value and determine if VBA should continue or whatever implementation you decide. Usually, I open the "shared" latest version of the document and inspect its custom property. If user is not using latest version, display a message prompting the user to get the latest version. This is usually good enough. I have done this for years with great success for Access, Word, and Excel VBA.

One real problem is people who ignore the prompt to update. They will do this because they fear losing data, bugs in latest version, and so on. You need to address their concerns and not try to "lock" them in to your "solution" to this problem. I strongly recommend you always provid a means of "importing/upgrading" data to latest version of workbook.

Fairly simple to implement. If you address the above mentioned problem effectively, people will start to trust the "prompt" and you will find this is a really simple and effective solution.

If you workbook is tied to a database, it gets a little more complicated. Generally, you do not allow the user to maintain the data outside of the database. When they want to modify the data, you generate the workbook for them. After they are done modifying data, you import the data. (The workbook is saved for "backup" purposes or the user can maintain a copy of the workbook for reference or an archive.) This has the advantage of eliminating the need to maintain document version since the document is "virtual".

Answer 3

1. Define a current version property. Use whatever way for that, like a constant in VBA.
2. Find a standard way to access the latest version. It could be on a shared drive always on the same path, or whatever other means you have.
3. If current version number < latest version number then:
   • Ask to upgrade
   • Show the upgrade procedure
   • Possibly do some locking on normal VBA operations until the upgrade is done

Answer 4

If its just the VBA code you want to be able to upgrade one solution is to separate all the code into an XLA that sits on an accessible server. then the users Excel has an addin loader that gets the latest version from the server, or you could embed the loader in the workbook. There is a working example reversioning Addin Loader available at http://www.decisionmodels.com/downloads.htm

Answer 5

An easy solution would be:

1. Store a version number somwhere in the workbook (on a hidden sheet for example so that it will not get removed accidentally) or as a variable.
2. Store a text file with the latest version number on your share
3. Include a macro which automatically reads the latest-version-textfile and compares the version number to the one in the currently running workbook. If its too old just show a message-box (telling the user to update) and close the workbook.

I'm using a similar approach and it works fine.