none
C# Interop Shape.FormControlType or Excel.OleObject.Object caused System.Runtime.InterOpServices.COMException

    Question

  • My programming Environment:
    Visual Studio 2008 Professional Edition German
    Microsoft Office 2007 - Office 12 German
    Programming Language C#

    Im trying to enumerate the Controls in an excel worksheet like buttons, drop down list and so on. I can open the excel workbox, i can iterate through the worksheets, but i cant enumerate the Form Controls.

    Here same Codesnippets:

    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    using MSForms = Microsoft.Vbe.Interop.Forms;

    private Excel.Workbook mb_wb;
    private Excel.ApplicationClass app;
    private Office.MsoAutomationSecurity mb_secAutomation;

    mb_secAutomation = app.AutomationSecurity;
    app.AutomationSecurity = Office.MsoAutomationSecurity.msoAutomationSecurityForceDisable;
    mb_wb = app.Workbooks.Open(ExcelItem, Type.Missing, Type.Missing, Type.Missing, Type.Missing,       Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,
    Type.Missing,Type.Missing,Type.Missing); 

    Excel.Worksheet ws = (Excel.Worksheet)mb_wb.Sheets[1];

    Excel.OLEObjects objects = (Excel.OLEObjects)ws.OLEObjects(missingValue);
    --> the property objects.Count has the value 2, but a lot of other properties have the exception System.Runtime.InterOpServices.COMException
    foreach (Excel.OLEObject oleObj in objects)
     {
      if (oleObj.Object is MSForms.CommandButton)
       { --> oleObj.Object this variable has no Attributes and here it throws the exception

    Message: "Die Object-Eigenschaft des OLEObject-Objektes kann nicht zugeordnet werden." English (my translation) -> "Object Property could not be assigned / found or not an instance of" or something like that

       on System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       on Microsoft.Office.Interop.Excel._OLEObject.get_Object()

    I tried it also with shapes, here a codesnippet

    foreach (Microsoft.Office.Interop.Excel.Shape sh in ws.Shapes)
    {
      switch (sh.FormControlType)
      {
        case Microsoft.Office.Interop.Excel.XlFormControl.xlButtonControl:

    sh.FormControlType caused the Exception  System.Runtime.InterOpServices.COMException, but the property sh.Name shows the correct name of a pushbutton. So something seems to work.

    On System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
    on Microsoft.Office.Interop.Excel.Shape.get_FormControlType()

    Any advice or hint is very appreciated. I searched the whole day on the web and have read a lot of threads but i couldnt find a solution.
    Regards Paolo

    Thursday, January 13, 2011 3:25 PM

Answers

  • Finally, now it works
    In my defense i have to say that im not the developer of this excel automation project. The Developer who wrote it, left our company.
    I went through the whole code and found this line:

    Excel.ApplicationClass app = Office.MsoAutomationSecurity.msoAutomationSecurityForceDisable;
    I removed the line and tested this code snippet again:

    foreach (Microsoft.Office.Interop.Excel.Shape sh in ws.Shapes)
    {
    if (sh.Type == Microsoft.Office.Core.MsoShapeType.msoOLEControlObject)
    {
    //sh.type -> has the value: msoOLEControlObject
    //sh.Name -> has the value: CommandButton1
    //sh.OLEFormat.Application -> has the value: {Microsoft.Office.Interop.Excel.ApplicationClass}
    //sh.OLEFormat.Creator -> has the value: xlCreatorCode
    //sh.OLEFormat.Object -> has the value: {System.__ComObject}
    //sh.OLEFormat.progID -> has the value: Forms.CommandButton.1

    With the setting "SecurityForceDisable" i got the error
    //sh.OLEFormat.progID -> has the value: "sh.OLEFormat.progID" caused the exception "System.Runtime.InteropServices.COMException"

    The property sh.OLEFormat.progID gets now the corresponding Type and this way im able to match the ComObject.

    Thanks to all of you for the help.
    Regards Paolo

     

    • Marked as answer by Paolo Taverna Wednesday, January 19, 2011 9:31 AM
    Wednesday, January 19, 2011 9:29 AM

All replies

  • Hi Paolo

    Could you please specify which set of menu commands creates these controls? Excel has a couple of different sets and how you address them depends on which kind.

    Also, what do you want to do with them, once you can "grab" them?


    Cindy Meister, VSTO/Word MVP
    Thursday, January 13, 2011 5:11 PM
  • Hi Cindy

    I use only Excel Standard OLE Objects. Example: the Pushbutton is typeof "Forms.CommandButton"

    Once i grab them i want to translate the Caption of the Formcontrol

    Regards Paolo

    Thursday, January 13, 2011 5:32 PM
  • I think you should strip your code from anything that is unnecessary and build from there. I say this because the following piece of code works just fine:

          Excel.Application app = new Excel.Application();
          Excel.Workbook wb = app.Workbooks.Open("FileWithAButton.xlsx",
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing
            );
          Excel.Worksheet ws = wb.Sheets[1];
          foreach (Excel.Shape shape in ws.Shapes)
          {
            if (shape.FormControlType == Excel.XlFormControl.xlButtonControl)
              Console.WriteLine("I found a button called: " + shape.Name);
          }
    


    ClosedXML - Create Excel files in .Net
    Thursday, January 13, 2011 5:40 PM
  • There are a number of problems with this statement

    From :

    foreach (Excel.OLEObject oleObj in objects)

     

    To :

    foreach (Excel.OLEObject oleObj in ws.OLEObjects)

    1. Not all objects are OLEObject. So a non OLEObject would give an error
    2. You don't have the worksheet in the For loop
    3. Hierarchial order of object is as follows :

    Shape : OLEObject : Object

    Shapes include pictures and controls.  Not all Shapes have OLEObject

    Th eOLEObject is the controlbut doesn't hav ethe properties in a text box to write the text.  the text is in the object as a child of the OLEObject.

     

     


    jdweng
    Thursday, January 13, 2011 5:51 PM
  • "Not all objects are OLEObject. So a non OLEObject would give an error"

    I'll give you that, so first do a check for  "shape.Type == Office.MsoShapeType.msoOLEControlObject" and/or "shape.Type == Office.MsoShapeType.msoFormControl"

    "You don't have the worksheet in the For loop"

    I don't understand what you mean or what your intention is.

    "Shapes include pictures and controls.  Not all Shapes have OLEObject"

    That's why you have to check for the type first.

     


    ClosedXML - Create Excel files in .Net
    • Edited by Atarax Thursday, January 13, 2011 6:28 PM Typo
    Thursday, January 13, 2011 6:20 PM
  • In my Code i have this statement before the "foreach-loop"

    Excel.OLEObjects objects = (Excel.OLEObjects)ws.OLEObjects(missingValue);
    foreach (Excel.OLEObject oleObj in objects)
     {

    my collection "objects" should hold only instances of OLEObjects, or am i wrong?

    Regards Paolo

    Friday, January 14, 2011 7:39 AM
  • You're right, and that's why you're getting some errors. Keep in mind that Excel allows for two types of objects on a worksheet: Form and ActiveX/Ole. The problem is that they both have the same interface but the properties you can access (without getting an error) varies from one to the other.

    The property "FormControlType" only applies to "form" objects, not ActiveX/Ole. So when you query for an Ole object and then try to access properties meant for "form" objects, then you get an error.

    Hope this helps,


    ClosedXML - Create Excel files in .Net
    • Proposed as answer by Bruce Song Tuesday, January 18, 2011 8:54 AM
    • Unproposed as answer by Paolo Taverna Tuesday, January 18, 2011 11:01 AM
    Friday, January 14, 2011 6:26 PM
  • Hi Atarax

    Thank you for the hint. This explains a lot. The Controls in my Excel Worksheets are all ActiveX/Ole Objects. Voted your answer as helpful.

    Regards Paolo

    Monday, January 17, 2011 7:40 AM
  • Hi Bruce

    I unproposed the post as answer. The post is helpful and explains that the property FormControlType is not available for Office.MsoShapeType.msoOLEControlObject but i have no solution.

    Regards Paolo

    Tuesday, January 18, 2011 11:06 AM
  • Hi Atarax

    I tried what you have proposed. I created a new Excel Workbook with only 1 worksheet, only 1 cell has a String-value and there is only 1 embedded ActiveX MSForms.CommandButton on the worksheet. The Commandbutton is named "CommandButton1". I tested it with following Code and debugged it:

    foreach (Microsoft.Office.Interop.Excel.Shape sh in ws.Shapes)
    {
      if (sh.Type == Microsoft.Office.Core.MsoShapeType.msoOLEControlObject)
      {
        //sh.type -> has the value: msoOLEControlObject
     //sh.Name -> has the value: CommandButton1
     //sh.OLEFormat.Application  -> has the value: {Microsoft.Office.Interop.Excel.ApplicationClass}
     //sh.OLEFormat.Creator -> has the value: xlCreatorCode
     //sh.OLEFormat.Object -> has the value: {System.__ComObject}
     //sh.OLEFormat.progID -> has the value: "sh.OLEFormat.progID" caused the exception "System.Runtime.InteropServices.COMException"


    Then i tested this Code to get the CommandButton object in this simple Workbook:

    Excel.OLEObjects objects = (Excel.OLEObjects)ws.OLEObjects(missingValue);
    foreach (Excel.OLEObject oleObj in objects)
    {
     // oleObj -> has the value: {System.__ComObject}
     
     if (oleObj.Object is MSForms.CommandButton)
     -> this line throws System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)

    So the shape object is the CommandButton1. Found a lot of web articles describing the issue with early and late binding with Office Interop Objects. VB.Net does the late binding behind the scene but with C# you have to do the late binding of the COM Interop objects manually.

    Does someone know where to find a codesample how to use late binding Excel.OLEObjects like MSForms.CommandButton and other MSForms Controls that are embedded in an excel worksheet?

    Regards Paolo

    Tuesday, January 18, 2011 11:11 AM
  • Hi Paolo

    I think there are some in the Visual Studio tools for Office forum, and maybe one or two in the Word for Developers forum but...

    What is it you want to do with this CommandButton in your project? One thing I've never seen is how to get the event (Click) using late-binding. And a CommandButton usually isn't much good without some kind of code linked to it.


    Cindy Meister, VSTO/Word MVP
    Tuesday, January 18, 2011 1:27 PM
  • Hi Cindy

    I opened the Worksheet and added a click-Event with some VBA Code. Saved the Excel file and tested if the click event on the button is working. I can click now on the commandbutton in the Excel sheet and it works.

    Tried again my c# code, got the same error, nothing changed.

    I want to iterate through all embedded Controls and translate their caption in an other language.

    Regards Paolo

    Tuesday, January 18, 2011 1:44 PM
  • Hi Paolo

    <<I want to iterate through all embedded Controls and translate their caption in an other language.>>

    OK, that should be possible. See the following sample code. The first part is just my getting the Excel Application object, then the workbook and worksheet. What you're interested in is at the end.

          Excel.Workbook wb = null;
          Excel.Worksheet ws = null;
          GetExcelApp();
          if (xlApp != null)
          {
            if (xlApp.Workbooks.Count <= 0)
            {
              wb = xlApp.Workbooks.Add(missing);
            }
            else
            {
              wb = xlApp.ActiveWorkbook;
            }
            ws = (Excel.Worksheet)wb.ActiveSheet;
            
            object ctl = ws.GetType().InvokeMember("CommandButton1", BindingFlags.GetProperty, null, ws, null);
            //Change the caption of the commandbar button
            object[] args = { "Label 1" };
            object ctlValue = ctl.GetType().InvokeMember("Caption", BindingFlags.SetProperty, null, ctl, args);
    
    

     


    Cindy Meister, VSTO/Word MVP
    Tuesday, January 18, 2011 3:15 PM
  • Hi Cindy

    Thank you very much for your response. Im really sorry for my late answer, couldnt test it yesterday. Today i tested your code and it throws an exception.

    This line throws the exception:
    object ctl = ws.GetType().InvokeMember(oleObjektName, BindingFlags.GetProperty, null, ws, null)

    ex.message is: "Unbekannter Name. (Ausnahme von HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME))"
    ex.Stacktrace is: "
    on System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)
    on System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    on System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args)"

    Now im pretty sure that there must be a problem with the PIA version that is loaded from GAC.
    I wrote some code to list out all assemblies that were loaded during exection, loop over: Thread.GetDomain().GetAssemblies();

    This two assemblies are in the list of the loaded assemblies:
    assembly.FullName: Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
    assembly.Location: C:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

    assembly.FullName: office, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
    assembly.Location: C:\Windows\assembly\GAC\office\12.0.0.0__71e9bce111e9429c\office.dll

    but im missing the Assemblies "Microsoft.Vbe.Interop" and "Microsoft.Vbe.Interop.Forms" in the List. Can this be the problem?
    Im doing now some research about this errormeassage: "HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME))"

    Thank very much you for the code Cindy

    Regards Paolo

    Wednesday, January 19, 2011 7:43 AM
  • Finally, now it works
    In my defense i have to say that im not the developer of this excel automation project. The Developer who wrote it, left our company.
    I went through the whole code and found this line:

    Excel.ApplicationClass app = Office.MsoAutomationSecurity.msoAutomationSecurityForceDisable;
    I removed the line and tested this code snippet again:

    foreach (Microsoft.Office.Interop.Excel.Shape sh in ws.Shapes)
    {
    if (sh.Type == Microsoft.Office.Core.MsoShapeType.msoOLEControlObject)
    {
    //sh.type -> has the value: msoOLEControlObject
    //sh.Name -> has the value: CommandButton1
    //sh.OLEFormat.Application -> has the value: {Microsoft.Office.Interop.Excel.ApplicationClass}
    //sh.OLEFormat.Creator -> has the value: xlCreatorCode
    //sh.OLEFormat.Object -> has the value: {System.__ComObject}
    //sh.OLEFormat.progID -> has the value: Forms.CommandButton.1

    With the setting "SecurityForceDisable" i got the error
    //sh.OLEFormat.progID -> has the value: "sh.OLEFormat.progID" caused the exception "System.Runtime.InteropServices.COMException"

    The property sh.OLEFormat.progID gets now the corresponding Type and this way im able to match the ComObject.

    Thanks to all of you for the help.
    Regards Paolo

     

    • Marked as answer by Paolo Taverna Wednesday, January 19, 2011 9:31 AM
    Wednesday, January 19, 2011 9:29 AM
  • Hi Paolo

    <<Office.MsoAutomationSecurity.msoAutomationSecurityForceDisable>>

    Yep, that would do it :-) Glad you were able to track it down, and thanks for taking a moment to let us know what the problem was!


    Cindy Meister, VSTO/Word MVP
    Wednesday, January 19, 2011 3:54 PM
  • Hello,

    I am getting a similar issue while trying to set a Optionbutton using C# Code (on an Excel 2010 Sheet).

    My Code works perfectly on a 32 bit server, It also works fine on 64 bit server while debugging through visual studio.

    However It gives error on running the same through Website hosted on IIS.
    aShape = workSheet.Shapes.Item("OptionButton2");
    
    Object o = aShape.OLEFormat.Object;
                    MSForm.OptionButton newBox = (MSForm.OptionButton)(((Excel.OLEObject)o).Object);
                    
                    Object flag = true;
                    newBox.set_Value(ref flag);
                    String t = newBox.get_Value().ToString();          
    

    This gives the Error on 64 bit server as :

    Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Vbe.Interop.Forms.OptionButton'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BD21D53-EC42-11CE-9E0D-00AA006002F3}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155).

    On Changing the Code snippet to use Late Binding:
    aShape = workSheet.Shapes.Item("OptionButton2");
    
                        object oleFormatObject = aShape.OLEFormat.Object;
                        object[] oParam = new object[1];
                        oParam[0] = true;
                       
                        oleFormatObject.GetType().InvokeMember("Value", System.Reflection.BindingFlags.SetProperty, null, oleFormatObject, oParam);

                     

    We get the error as: Unknown name. (Exception from HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME))

    I assume "Value" is not a valid parameter for Optionbutton.

    The Following values were return for progid and  Type : Forms.OptionButton.1 and System.__ComObject respectively.

    Please suggest what can be done to fix this issue

    Swapnil Kocheta [http://portfolio.swapnilKocheta.com]


    • Edited by Skocheta Friday, November 02, 2012 1:33 PM updated formating
    Friday, November 02, 2012 1:32 PM