Problem with Microsoft Interop assemblies for Excel integration

Also, if you want to make use of modern functionality if you do happen to have the 10.0 interface available you should do an explicit call to QueryInterface in such a way that you first check for the 10.0 interface and if that fails you check for the 9.0 interface.

Then when you want to call a function you check if you have 10.0 or 9.0 available, you have three possible values:

1. a null value - neither 10.0 nor 9.0 nor any other more modern interface available.

If this is the case, you haven't tested yet, run the QueryInterface testing and get the proper interface. Then test again, it shouldn't be the null value in this case.

9.0

use the 9.0 interface, if the function require 10.0 interface have the function fail with the reason "The excel version is too old, upgrade excel if you want to use this function". You can for example let the function throw an exception with such an error message in this case. If the function is available in 9.0 interface you call that function.

10.0

Use the 10.0 interface to do the job - you already said that works so you don't have to do any extra in this case.

One way to achieve this - and also at the same time prepare for excel 11.0 when it comes etc is to do the following:

step 1. Define a class ExcelInterface with all virtual functions for all your operations. If you need more operations later you add new functions. This is a baseclass and all the operations should therefore simply fail and never attempt to actually do anything. They could throw an exception or whatever.

step 2. Define a delegate type inside the class:

    public delegate ExcelInterface ExcelInterfaceCreator(IntPtr ip);

step 3. Define a static list object holding delegates. Delegates can actually hold references to many functions (multicast) but unfortunately you can't make use of this functionality here. Just declare a list (use arraylist or your own class for this) of delegates. The intial value is empty list.

step 4. Define a public static function like this in the class:

public static ExcelInterface GetInterface(IntPtr ip)
{
   ExcelInterface xptr;

   foreach (object obj in DelegateList) {
       ExcelInterfaceCreator c = (ExcelInterfaceCreator)obj;
       if ((xptr = c(ip)) != null)
           return xptr;
   }
   // None of our interfaces were supported, throw exception?
   return null;
}

step 5. Create a protected static function to add delegates to the list.

protected static void AddInterface(ExcelInterfaceCreator c)
{
    // add the delegate to the list.
}

step 6. For each version you want to support define a subclass of ExcelInterface implementing the functions it can support. Those functions that cannot be supported by this particular interface is left as they are and will fail by throwing exception by the baseclass.

step 7. Define the following private static member in the subclass.

    private static readonly GUID guid = guid_for_this_particular_excel_interface;

For a specific subclass this is a specific interface and so the GUID value is known and you can initialize this static variable with that proper GUID value.

step 8. Define the following private non-static member in the subclass.
     private ExcelObject excel;

ExcelObject is here a type that is such that all objects which is such that QueryInterface(obj, guid) returns non-NULL with obj being the object and guid being the static member defined in step 6, then the obj in question can be cast to the type ExcelObject without any error. In worst case the ExcelObject would simply be the type "object" but that wouldn't give you much access to Excel functions, so hopefully you can narrow it down better than that. In the general case the ExcelObject is actually reference an unmanaged object using the IntPtr type. If you instead want ExcelObject to directly be that IntPtr type you can of course do that as well. If so you must implement the calls to the object using the PInvoke feature yourself instead of delegating it to some ExcelObject type. Note that this type is specific to the interface version so you have one type for 9.0 and another for 10.0 etc.

step 9. Define the following private constructor:

private ExcelInterfaceSubClass(ExcelObject ob) { excel = ob; }

step 10. Define the following private static funciton in the subclass.

   private static ExcelInterface make_obj(IntPtr ob)
   {
        // Use QueryInterface and ask the excel object if it support the interface we want identified by guid.
        // if the return is is a valid interface we can use that interface to create an ExcelObject value which is
        // given to constructor to create an ExcelInterfaceSubClass object which we return.
   }

step 11. Make the following public static function in the subclass.

    public static void AddInterface()
    {
          ExcelInterfaceCreator c = new ExcelInterfaceCreator(ExcelInterfaceSubClass.make_obj);
          // add it to the list.
          base.AddInterface(c);
    }


step 12. for each of the subclasses call the AddInterface function at some early point in the program. Call the interface for the more recent versions first.

public static int Main(....arguments here if you want...)
{
    ExcelInterface10_0.AddInterface();
    ExcelInterface9_0.AddInterface();

    // Get the IntPtr to the excel interface.
    IntPtr ip = GetExcelObject(....arguments here...);

    // Now you're ready to call ExcelInterface.GetInterface() to get an object.
    ExcelInterface x = ExcelInterface.GetInterface(ip);
    // x is actually a subclass of ExcelInterface, which subclass depends on the interface that the object ip supports.
    // This means that virtual calls to x will always use the version that is most available of a function or interface.
    // be sure to properly clean up afterwards. This is all the more important since the COM interfaces themselves
    // are not managed objects and so have to be handled with special care.
}

The idea is then that when a version 11.0 comes you simply make a new subclass either from ExcelInterface or from ExcelInterface10_0 and make sure you add a call to ExcelInterface11_0.AddInterface() before the call to 10.0 one above and then the GetInterface() function will first check to see if the object in question support interface 11.0 and if that fail it will check if it support 10.0 and if that fail it will check if it support 9.0 When it finds an interface that is supported it creates the corresponding object and return that object which contains virtual functions to handle all the operations you want to do.

Hope this is of help.

Alf