Accessing Excel's methods/properties via Visual Studio C#

I tried this, but it fails on the following statement:

OLEObjects objs = (OLEObjects)worksheet.OLEObjects();

It doesn't like having no arguments - comes up with an error of "No overload for method 'OLEObjects' takes '0' arguments'"

Any further help on this would be good.

Thanks

That's interesting.  The optional nature of the parameter is certainly defined in http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.oleobjects(VS.80).aspx

Oh, hang on.  I think I've got it.  It looks like you have to jump through an extra hoop with C# when doing COM interop, as C# does not support optional parameters.  I expect the COM classes were designed for C/C++/VB, which do support these.

Try passing Type.Missing to the OLEObjects function.  Apparently, the interop layer knows to treat this as the default value for an OptionalAttribute-decorated parameter:

OLEObjects objs = (OLEObjects)worksheet.OLEObjects(Type.Missing);

                                              

1:

I've given that a go but I still get another error which is :

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.OLEObject'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208A2-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

Any further help on this would be good.

Thanks

I am assuming that the error is on the foreach... statement, rather than on the same line as the last error -- you hve not indicated the actual position of the error like before, but the cast to OLEObject occurs implicitly in the foreach.

As such, I guess you've got to change the foreach to look a bit more like the below code.  You might want to set a breakpoint on the foreach and find out what is being yielded by objs because, as far as I can see, it should only be instances of OLEObject.

foreach (object obj in objs)
{
  if (obj is OLEObject)
  {
    OLEObject ole = (OLEObject)obj;
    if (ole.Object is OptionButton) 
    {
      OptionButton opt = ole.Object as OptionButton;
      optionButtons.Add(opt.Caption, opt.get_Value());
    }
  }
}
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

Actually, the error was occuring before it reached the foreach statement.  It happends when it gets to the following line:

OLEObjects objs = (OLEObjects)worksheet.OLEObjects(Type.Missing);
 
The error is related to the Type.Missing:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.OLEObject'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208A2-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).


Thanks
Geoff