Reference both Excel 2003 and 2007 into Autocad-VBA project

I am writing a VBA (Visual Basic for Applications) script for AutoCAD. It uses Autocad as a graphical engine and Excel for displaying the results....

The problem is, that some of the users are using Excel 2003, and the other are using Excel 2007.

To use Excel 2003 from the VBA, I have to reference C:\Program Files\Microsoft office\Office12\excel.exe to the project. But to use Excel 2007, I have to reference ...\Office14\excel.exe.

For people that does not know, what reference is: it must be done permanently, within the project properties, so I can't开发者_如何转开发 programatically decide, which of that two files does exist in computer and reference them in runtime.... or maybe I just don't know, how?

I can't even reference both of the files at once, because they have the same filename.

You'll need to use late binding (IDispatch). Create your Excel app object by calling VBA's CreateObject method. This will use whichever version is installed.

With late binding you won't have the convenience of IntelliSense.

Different versions of Excel have different features, and in some cases even implement the same feature in different ways. So be sure to test against both versions. In some places you may have to have different code for the different versions.

You need to late bind, that is, reference neither, declare everything as "object" and use CreateObject to instantiate instances.

This isn't so hard as you can code with the reference in (early bound) and then just change the variable declarations afterwards and remove the reference. Its quite common when coding against excel due to the problem of different versions.

Note that you should test thoroughly against both incase there are changes in API or functional behaviour between the versions.

If you're writing new code for AutoCAD I'd seriously look at going to .NET. Autodesk are phasing out their support of VBA - it has not been automatically included in the last 2 (soon to be 3) versions of AutoCAD: http://withoutanet.typepad.com/without_a_net/2009/04/vba-in-autocad-2010.html

As users update to newer versions there will be even more friction for them to use VBA add-ons. LISP & ARX aren't going anywhere in the next few years since a lot of AutoCAD commands are written in those languages.

Even if you are maintaining existing code I think you should seriously work towards migrating to the technology that AutoCAD will support in the near future. I have quite a few tools I wrote in VBA which I have ceased to maintain (read - abandoned) & am now rewriting in .NET. The leap from VBA to VB isn't a quantum one. Personally, I now prefer C# but that is an entirely personal choice. The languages are equivalent in their functionality. There are a few links to migration guides on the link above and at http://through-the-interface.typepad.com/through_the_interface/2010/02/updated-devtv-autocad-vba-to-vbnet-migration-basics.html. As a bonus these skills will be a lot more valuable outside AutoCAD development than VBA in the future, and now.

.NET code is also supported by the ODA so it is (kinda) straightforward to port AutoCAD add-ons to a stand-alone app using their library.

Also, this could solve your problem - references can be managed programmatically in a Visual Studio .NET project.