Automating Excel With C#

[Kenneth]

Hey all, first post.

i am automating Excel for a C# application, and i need to have some measure of backwards compatibility. Our old version supported back to 2000, but it was in C++ and doesn't come with the complexities that .NET Interop poses.

Couple of questions:
1. i could be crazy, but i'm fairly confident the version after 2000 was XP (2002).
2. What references do i need to add to my project to get things to work for both XP and 2003 (assuming XP is the 2002 version)? i've got:

Microsoft Excel 11.0 Library
Microsoft Excel 5.0 Library

3. What the heck is the 5.0 library?

Thanks for the help!

[Hot Forex Signals]

You should always develop for the minimum version supported. If it's XP then I believe you should be using the 10.0 library.

The alternative is to use late binding and declare your Excel object variables as "object". This way you avoid having to add a specific version of the reference to your project.

[Trade Forex Copier]

Office XP, aka Office 2002, is version 10, so you would use the Microsoft Excel 10.0 Library.

However- if you don't have Office/Excel XP installed on your development PC, and/or have a later version installed, you will not have the "Microsoft Excel 10.0 Library" entry available in the "Add References" dialog.

The standard way to develop apps that use Office automation is to do your development on a PC that has the earliest version you want to support installed on it. When you later go to install your finished app on a PC that has only a later version of Excel on it, your app will automatically use the later version. This does not work in reverse! You can't set a reference to Excel 11 and expect your app to run on a PC with Office 10 on it.

The "easy way out" is to use Late Binding; however, take it from someone who has quite a bit of experience writing VB apps which automate Office: this is not the way to go. You will experience a significant loss of execution speed (up to 50%), you will lose IntelliSense (which is particularly useful when automating an Office app you're not that familiar with), and there are other reasons not to go this route, such as loss of Type Safety/Type Checking. If, as you said, performance is an issue, Early Binding and direct automation of Excel is the way to go.

In my experience, Early Binding is just more robust; I've had fewer installation issues on my clients' PCs with it. And you get performance that can be up to twice as fast or even faster; you don't have the overhead of doing a run-time lookup; and you get Type Safety in VB. To quote from the above web pages: "Microsoft recommends early binding in almost all cases"; "The advantages given to early binding make it the best choice whenever possible."

If you have a situation where your development PC already has a later version of Office on it than the one you need to support, a great solution is to use a product like Microsoft's Virtual PC.

You can do all your development as usual, with the Reference set to the version you have on your development PC, then, when your testing is done, install the earlier version of Office on a virtual OS, along with Visual Studio and the project files for your app. Change the reference to the earlier version of Excel, recompile, do a bit more testing, and you're good to go!
The other advantage of this approach is that you can create endless variations of combinations of OSes and Office versions, for testing purposes.

One of the best features of these apps is their ability to clone, or copy, OSes you've already set up; you can then make small changes to the copy, do your testing, then toss the copy and start fresh with a new one. Otherwise, for every new OS you set up on a virtual PC, you must go through the full installation process of the OS itself, from the original CD, and any other software you need, such as Office.

Another thing - you probably already know this, but, when automating Office 2002/XP or later from VB.NET, be sure to download and use the Office PIAs, or Primary Interop Assemblies. You reference the PIA rather than the Office app; to check that you've referenced them properly, open the References in Solution Explorer and click on "Excel". You should see MICROSOFT.OFFICE.INTEROP.EXCEL in the Properties window.

When you use a COM object from .NET, Visual Studio automatically creates an Interop Assembly for you. These automatically created assemblies may not be optimal; when a PIA is available from the COM object's creator, always use it, unless there are known issues with it. The developer of the COM object knows it best, and can create the best Interop assembly.

I hope I have convinced you to take another look at Early Binding; it's the way to go if at all possible.

[RusefTrader]

You can use ZetExcel for Automating Excel With C#.