Automating Excel With C#
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!
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.
Microsoft MVP (Visual Basic)
i think what i'm going to end up doing is using the Excel XML formats and just write stuff out to XML. Then i'll only need to instantiate the Excel.Application object to launch the file.
The whole late binding thing will work for this, but trying to process sizeable data through all that boxing is going to be too slow i'm thinking.
Paul is correct, as usual. ;-)
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.
To read up on the issue, see this page on automating multiple versions of Office, and this page, which has a good discussion of Early vs. Late Binding. It states clearly that Early Binding is the preferred method, along with some of the reasons why.
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.
In fact, you really should be doing this regardless, unless you are blessed with a large and diversely set-up number of testing PCs. If you need to support multiple versions of Windows, if you may be dealing with different versions of the .NET runtime on your target PCs, or, as in this case, you need to support different versions of Office, a Virtual PC-type app is really essential unless you have an unlimited testing budget and lots of spare time(yeah, right!). Microsoft even makes a 45-day evaluation version available for free: http://www.microsoft.com/downloads/d...DisplayLang=en
If you don't like Virtual PC, there is a competing product called VMware, which you may also want to check out.
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.
Last edited by Andrew Cushen; 06-16-2006 at 11:44 AM.
Wow, thank you for the comprehensive response. You have confirmed quite a bit of what i had suspected, and it was extremely helpful.
Question: will the PIA's work for C#, too? You mention them in reference to VB.NET... would i be able to use those in lieu of finding a PC with v. 10 installed?
Glad to be of help; I had to gather a lot of this info on my own over the years, and I know how frustrating that can be. In fact, I'm going through that same frustration all over again with VB.NET 2005/ASP.NET 2.0...
The PIAs will work for C#, but AFAIK not in lieu of having the proper version of Excel/Office. The PIAs are really just a means of interfacing to Office via COM Interop.
When you automate Office, whether from VB6, VB.NET, C#, or C++, you are talking to a COM object.
When you use a .NET language to automate Office, you are using COM Interop, and you need a Runtime Callable Wrapper (RCW), also called a Primary Assembly, in order to talk to the COM object. It's sort of analogous to a proxy; in a way, it translates the .NET calls to COM calls. But you still need the underlying COM object; which means that you need the proper version of Office/Excel. The PIA acts like a translator; you still need the underlying COM object to talk to.
So AFAIK you can't use the PIAs for one version of Office (such as Office 2002/XP) to talk to another (later) version, such as Office 2003. Sorry. If you want to support Office 2002/XP, you'll need it installed on your dev PC, or the PC you compile the final .EXE on, or you'll have to use Late Binding.
As I said in my earlier post, if you have Office 2002/XP lying around, you could use the free 45-day eval copy of Virtual PC to install Excel 2002/XP on a virtual OS on your dev machine when you're ready to do your final compile and testing. Unless you can get your boss to spring for the full version of Virtual PC...Whatever you do, you still need to test your app using Office XP/2002, right? So you really need a copy of it up and running anyway... so you might as well do the right thing and compile your app on a machine with Excel 2002/XP, whether it's an actual machine, or just a virtual one... ;-)
I hope that clears things up.
Thanks again Andrew...
Question - i was able to dig up a copy of office XP - but i do already have 2003 installed. Does anyone know of any KB's for installing multiple versions of Excel? i've been googling, and i know i've seen one referenced somewhere...
Anyways, any final help here is appreciated
Hmm. You did notice all the warnings about doing this, right? And the part where you have to install the oldest version of Excel FIRST?
And this part: "Using Automation to Control Microsoft Excel -
If you install multiple versions of Microsoft Excel on a computer, you may experience difficulty when you attempt to use Automation to control a specific version of Microsoft Excel. "
Sounds ugly to me.
Since you have the copy of Office XP, why not download the free 45-day evaluation copy of Virtual PC?http://www.microsoft.com/downloads/d...DisplayLang=en
That will allow you to create a virtual OS into which you can install Visual Studio and Office XP. Finish your development and testing on your regular PC, then copy everything over to the Virtual PC, change the Excel reference to 10.0, re-compile, do a little testing, and Bob's your Uncle?
Not to mention the fact that, if you do eventually convince your boss to spring for the full version of Virtual PC (great testing tool!!), you already have a virtual PC file set up for dev work!
I would be leery of installing 2 versions of Office on my development PC. What if it gets trashed?
Of course, there's another option here. If you already have Office 2003 installed, according to the instructions on that MSKB webpage, you would need to remove it first anyway before installing Excel 2002/XP. Why not remove 2003, install Office XP, leave it that way until your app is all finished, then re-install Office 2003?? Much safer than running multiple versions of Excel...
Anyway, good luck with your mission... ;-)
Last edited by Andrew Cushen; 06-16-2006 at 11:57 AM.
Reason: add'l info
Yes, i read the warnings after that last post... we do have a copy of Virtual PC, and i have installed it, but since we don't have a bootable CD for Win XP Pro, i'm having a bear of a time getting to it on our MSDN collection (since the MSDN CD has an invalid path for older DOS file systems, i.e. through a 98 boot disk). i'm downloading the iso now from MSDN, but if you have any suggestions i'd appreciate it
It's been awhile since I did this, but if you've got a floppy drive, you can make a bootable diskette, which I think will work...
I have a program called WinISO, which will rip an .ISO file image off a CD. There are free programs that do the same. You can then use a program like D-Tools to mount the .ISO as a virtual CD-ROM drive...though I'm not sure if that would help you.
There are other ways to get a bootable CD, and you could then switch to the WinXP CD; try these links for info:
If you have Nero: http://www.tacktech.com/display.cfm?ttid=297
i got it working... had to download the iso from MSDN.
i'm going through the process of installing everything, including the 2002 PIAs, and then i should be in good shape. Thanks for the great recommendation!
Does anyone know the way to set values from an array? In C++ it is *much* faster to use a COleSafeArray and pass in the values to a spreadsheet all at once. Is there a way to do this in C#?
By Garner in forum VB Classic
Last Post: 05-14-2006, 12:59 AM
By OwenDavies in forum VB Classic
Last Post: 03-09-2006, 09:40 PM
By lew26 in forum Database
Last Post: 01-23-2006, 04:27 PM
By Alexander Frolov [Afalina Co., Ltd.] in forum vb.announcements
Last Post: 06-24-2002, 01:13 PM
By Afalina Co., Ltd. in forum vb.announcements
Last Post: 04-01-2002, 10:39 AM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL