Trying to do Office Automation with Excel 2007, but keeps using Excel 2003

Question

Environment:

Windows XP machine
Both Excel 2007 and Excel 2003 installed (in that order, not chronologically).
C# 3.5

Problem:

When I use the PIAs to do some Office automation, I use the following line of code:

var excel = new ApplicationClass();

The PIA's version specifically refers to it as Excel 12.
C:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll
But:

 excel.Version;//this is 11.0 instead of 12.0

Thus, when I try to open a file with extention .xlsx, it warns me about lost functionality in the file conversion, and opens it with excel 2003. I am pretty sure it has to do with the install order being 2007 -> 2003, but I can't uninstall 2003 on my machine b/c we have some office automation on our webserver for an unrelated project that uses excel 2003.

I've looked at the Policy.11.0.Microsoft.Office.Interop.Excel.config stuff, but it says

<bindingRedirect oldVersion="11.0.0.0" newVersion="12.0.0.0"></bindingRedirect>

So, I am at a loss. Why can't I tell the COM Interop which version of Excel to use?

Answer 1

You cannot programmatically command which version of Excel to use. The PIAs only dictate which interface, or object model, you are developing against. But which version of Excel is actually running is controlled by the registry.

When it comes to running the PIAs, however, you will actually run against the highest-level PIA installed on the system. So if you develop against the Excel 2003 PIA, but the client has Excel 2007 with the Excel 2007 PIA, your code will run against the Excel 2007 PIA -- and it should run fine, because the Excel 2007 PIA is backward compatible. That is, each higher-numbered PIA version (and Excel object model) is backward compatible to commands compiled against an older PIA and older Excel object model. Note that if the client had both the Excel 2007 and Excel 2003 PIAs on the machine, then the higher versioned PIA would load, regardless of which version of Excel is running - so the Excel 2007 PIA would run, if both PIAs were available.

[Edit: One caveat is that the Excel 2007 PIAs should be 100% backward compatible when using VB.NET or C# 4.0. If using C# 3.0 or below, the fact that optional parameters are actually required when called from C# 3.0 or below will create a break in some code when running against the higher-version PIA or object model. It's relatively rare though, and with C# 4.0 around the corner, this issue should go away, in theory.]

Ok, so you don't have a lot of control over the PIAs because the PIA that you developed against does not actually control which PIA will actually be running on the client machine.

You don't have a lot of control over which version of Excel is launched either. For example, when you create a new Excel instance via:

Excel.Application excelApp = new Application();

The Excel application loaded is set according to the current version set in the registry. The current version is saved at:

HKEY_CLASSES_ROOT\Excel.Application\CurVer

It looks like the 'CurVer' key in your case will have a default value of 'Excel.Application.11', instead of 'Excel.Application.12'. Changing this alone might do the trick, but I would prefer to do a repair instead to make sure that all the registry settings are corrected properly. (And I couldn't possibly know what all the settings should be.) Ok, I just found another one: you would also need to change:

[HKEY_CLASSES_ROOT\CLSID\{00024500-0000-0000-C000-000000000046}\ProgID]

to hold a value of "Excel.Application.12". But I would strongly recommend running a repair instead. I don't know what other settings might need to be changed, so changing them by hand is a bit risky.

In addition, you should find the following keys as well:

HKEY_CLASSES_ROOT\Excel.Application.11
HKEY_CLASSES_ROOT\Excel.Application.12

Because these are the versions of Excel that you have installed.

(See here for a further discussion.)

I am pretty sure it has to do with the install order being 2007 -> 2003

Yes, this is 100% correct. You could try running a repair on Excel 2007, this would be the easiest thing to do. If this does not work, then I would uninstall both and then re-install them both. I would uninstall Excel 2003 and then uninstall 2007 (reversing the order in which you installed them) and then install Excel 2003 and then install Excel 2007 so that you are installing both versions in the correct order.

But keep in mind that by doing this, Excel 2007 will be run by default when you call Excel.Application excelApp = new Application().

The actual recommended practice is not to have both versions of Excel running on the developer's machine. For more on this, see:

• Why is VS development not supported with multiple versions of Office?
• Can you build one add-in for multiple versions of Office?
• Writing Automation clients for multiple Office versions

I used to have multiple versions of Excel on my same development machine, and I personally felt that the downsides were not as complicated as these articles make it sound. In general, the Excel 2007 PIA is backward-compatible to the Excel 2003 PIA and everything works fine. But I once got into a registry mess similar to yours and decided to "do the right thing". I uninstalled both and then only re-installed Excel 2007.

From there I installed Virtual PC, which is free (VM ware is actually a little better, but it's not free) and then installed my lower versions of Excel for 2003, 2002, 2000, and '97 on separate VMs. It is definitely some work to set up, but once you do this, everything is 100% clean.

That said, I probably would not want to actually develop against lower-versions of Excel on a VM, it would be too hard to use Visual Studio hosted within a VM. So these VMs are only good for testing deployment to make sure that your system can work against various client configurations. Make sense?

Hope this helps!

Mike