Version of Excel document in VSTO add-in

Question

I use VSTO Excel template based solutions frequently. One of the things I like about this project type is that I can use cached data sets in the Excel template or worksheet to handle application state that persists after the user saves the file. However, cached data sets create a problem: the schema of the document is bound to the application version.

This hasn't been a big problem, but I've realized it would be a good idea to create a mechanism that will read the version of the Excel document when it opens in order to detect and handle version incompatibilities. For example, if my 2.1 plugin opens a 1.5 document, the schema of the cached data will not match what is expected.

Is there a standard or recommended way to version stamp the Excel template or worksheet? If not, does anyone have suggestions on how to do this?

Answer 1

I don't know any standard, but you can consider:

1. The use of a custom document property accessible by Microsoft.Office.Core.DocumentProperties (How to: Read from and Write to Document Properties);
2. A protected cell in the worksheet itself;

With the first approach the user could manually change this property or delete it. However the second approach you would have protected cells in the worksheet which, if I recall correctly, will introduce some problems if you want to sort any data in that worksheet.

Answer 2

I had a similar issue (how to determine the Excel version from within VSTO code). I found your question while search for a solution. What worked for me was the Version property of the Excel Application object. From within a worksheet:

Me.Application.Version

or maybe, depending on where you're at

Globals.ThisWorkbook.Application.Version

Anyway, the value is "11.0" for Excel 2003 and "12.0" for 2007.

However, on re-reading your issue, you need to know the version that created a workbook. You might try Workbook.CalculationVersion, which "Gets a number that indicates the version of Excel that the workbook was last fully recalculated by. The rightmost four digits are the minor calculation engine version number, and the other digits (on the left) are the major version of Microsoft Office Excel." as per http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.calculationversion.aspx

Answer 3

I usually use the Worksheet.CustomProperties to persist information like this into my worksheet. Any information you put into the CustomProperties collection gets saved along with the worksheet and is loaded back along with the worksheet information when you reload the worksheet.

Workbooks also support custom properties, but I think in your case Worksheet.CustomProperties is the way to go.