C# app that uses Excel Interop, easy switch between 2003 2007?

Question

I have an app written that just does really basic I/O on a spreadsheet. Read in a bunch of cells, do some sorting, and dump the output back into another spreadsheet. Works great on my machine. I have Office 2003 installed.

When it runs on someone elses machine with 2007, it bombs, presumably due to the different versions of the Interop assemblies.

I was hoping someone knew a way to dynamically change which/where the assembly is loaded from depending on the office version (getting the office version is easy from the registry).

Sifting thru the rest of the internet, i didnt see anything to indicate that was possible :( . I tried installing the 2007 PIAs, but when i try to add a reference, they dont show up and i have no idea where to browse for them (they arent in the c:\windows\assemblies\; maybe the install didnt work). Am I going to have to get a machine with 2007 installed to build on every time i need to make updates?

Answer 1

Look at my project MS Office for .NET.

It's set of managed assemblies with wrapper types writen in VB.NET using late-binding and dynamicaly loaded COM objects. It works with versions 97-2007. There is no PIA required.

I preserved object model, so the most of VSTO samples should work with only little changes.

I hope, it helps.

Answer 2

It's actually more likely that the run-time version that represents the compatibility issue, not the PIAs.

When it runs on someone elses machine with 2007, it bombs, presumably due to the different versions of the Interop assemblies.

I know that it might "feel" this way, but this scenario is not likely. The Excel 2007 PIAs are broader than the 2003 PIAs, not narrower. Types, members, and optional parameters might be added, but the older 2003 signatures will be unchanged. Therefore, all your old 2003 calls will work the same way whether operating via the 2003 PIAs or the 2007 PIAs. This is not 100.00% guaranteed, of course, but it's probably in the 99.8% or so, is my guess.

I was hoping someone knew a way to dynamically change which/where the assembly is loaded from depending on the office version (getting the office version is easy from the registry).

You would need to use reflection, e.g., the Assembly.LoadFrom method. But I really don't think that you need to go this route, honest. In fact, to prove it, you should change the reference in your assembly to the 2007 version, compile it, and then run. I'm willing to bet that you'll get the same exact error.

But why would this be? And how would you fix it?

The compatibility problems between the Excel 2003 and Excel 2007 object model are few, but there are some. One example is the Range.Cells.Count property, which returns an Int32 that has a maximum value of +4.3 bil. (approx). However, with the much larger worksheets introduced in Excel 2007, the number of cells on the spreadsheet is actually around 17.2 bil. Therefore Range.Cells.Count throws an exception if the Range is too large (e.g., comprises an entire worksheet). Instead, you have to call Range.Cells.CountLarge which, because it returns an Int64, can handle the larger result.

So it is actually most likely the run-time version that represents the compatibility issue, not the PIAs. Therefore, you should isolate the line that is throwing the exception and let us know what the error message is. Then maybe we can help...

-- Mike