none
How to get an OLEControl contained within a Range RRS feed

  • Question

  • I know I can get a ContentControl contained within a Range easily from the ContentControls property of the Range object but I need to get an OLEControl instead so does anybody have an idea of how I could do this?
    • Edited by VAlain Monday, October 5, 2015 11:24 PM
    Monday, October 5, 2015 11:20 PM

Answers

  • I managed to get it work by maintaining a Dictionary matching the Wrapper Name with the Control Name.

    It look likes the dynamically created Controls are not saved with the Document but since the Wrapper is saved I should be able to recreated them in the same Cell by inspecting the empty Wrappers.

    The only problem left is that I don't know how I can determine in which Table a Cell is since the Parent Property returns a reference to the Document instead of the Table.

    So I will mark the question as answered thanks all for the effort to answer me.

    • Marked as answer by VAlain Saturday, October 10, 2015 9:44 PM
    Saturday, October 10, 2015 9:44 PM

All replies

  • Hi VAlain,

    If you want to get the property of OLEControl, you need to use OLEFormat.Object Property, it returns an Object that represents the specified OLE object’s top-level interfance. Here is a simple demo:

    Sub test()
        Dim item As InlineShape
        Dim obj As Object
        For Each item In ActiveDocument.InlineShapes
          If Not item.OLEFormat Is Nothing Then
           Set obj = item.OLEFormat.Object
           Debug.Print obj.Caption
          End If
        Next
    End Sub

    For more information, you could refer the link below:

    #OLEFormat.Object Property (Word)
    https://msdn.microsoft.com/en-us/library/office/ff198170(v=office.15).aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, October 6, 2015 2:25 AM
  • How you access an OLEcontrol partly depends on what kind of object it is. For example, if it's a named Shape object, you might activate it with code like:

    ActiveDocument.Shapes("MyShape").OLEFormat.Activate

    Similarly, if you know its Index # in the document, you might activate it with code like:

    ActiveDocument.Shapes(1).OLEFormat.Activate

    You can also use the latter approach with InlineShape objects:

    ActiveDocument.InlineShapes(1).OLEFormat.Activate

    However, if you don't know the index # or Shape name, you could use code like the following to iterate through all the InlineShape and/or Shape objects till you find the one you want:

    Sub Demo()
    Dim iShp As InlineShape, oShp As Shape
    Dim objOLE As Word.OLEFormat, objXL As Object
    With ActiveDocument
      For Each iShp In .InlineShapes
        With iShp
          If Not .OLEFormat Is Nothing Then
            If Split(.OLEFormat.ClassType, ".")(0) = "Excel" Then
              .OLEFormat.Activate
              'Do stuff with the activated object
              Set objOLE = .OLEFormat
              objOLE.Activate
              Set objXL = objOLE.Object
              objXL.ActiveSheet.Cells(R, C).Value = X
              On Error Resume Next
              objOLE.ActivateAs ClassType:=""
            End If
          End If
        End With
      Next
      For Each oShp In .Shapes
        With oShp
          If Not .OLEFormat Is Nothing Then
            If Split(.OLEFormat.ClassType, ".")(0) = "Excel" Then
              .OLEFormat.Activate
              'Do stuff with the activated object
              Set objOLE = .OLEFormat
              objOLE.Activate
              Set objXL = objOLE.Object
              objXL.ActiveSheet.Cells(R, C).Value = X
              On Error Resume Next
              objOLE.ActivateAs ClassType:=""
            End If
        End With
      Next
    End Sub

    Whichever approach you take, though, you need to bear in mind that Microsoft has never provided anything akin to a DeActivate method, so kludgy workarounds are needed to achieve that, such as:
              On Error Resume Next
              objOLE.ActivateAs ClassType:=""
    in the above code, which causes problems with the Ribbon display in Word 2007 & later.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Tuesday, October 6, 2015 3:29 AM
  • Hi Edward,

    OLEFormat.Object returns the ActiveX Wrapper of the Control but I need to access the properties of the Control itself, my control is a ListBox and I need to access it's Name and Tag properties.

    Tuesday, October 6, 2015 2:27 PM
  • I am not trying to access a Shape so it is not in the Shapes collection, it's a ListBox so it's in the Controls collection but I need to access it from a Range. I can access it's wrapper via InLineShape but I had not found the way to access the ListBox inside that Wrapper.
    Tuesday, October 6, 2015 2:35 PM
  • I am not trying to access a Shape so it is not in the Shapes collection, it's a ListBox so it's in the Controls collection but I need to access it from a Range. I can access it's wrapper via InLineShape but I had not found the way to access the ListBox inside that Wrapper.

    HI VAlain

    Is this a VSTO document and you're adding a WinForms control to it? Can you explain why you need to access it via the Range object (give us some context)?


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, October 6, 2015 5:24 PM
    Moderator
  • Hi Cindy,

    Yes it is a VSTO Document with WinForms Controls, I have ListBoxes in a Word Table populated from Excel ListObjects and the Tag Property of the ListBox determine from which ListObject.

    It work perfectly but now I want to be able to programmatically add a Row to the Table, copy all the ListBoxes from the previous Row and then set the Tag Property of each ListBox of the new Row to the same value as of the previous Row.

    So I need to access the controls from the Range Object since I need to get the all the controls of the new Row and also refer to the ones from the same Column in the previous Row.

    Tuesday, October 6, 2015 6:48 PM
  • Hi Alain I'm afraid the propsed approach cannot work. You cannot simply copy/paste WinForm controls in the Word document: they won't hook up automatically to zhe VSTO functionality. You need to create each one individually using the special functionality. see https://msdn.microsoft.com/en-us/library/x97a5x3s.aspx. https://msdn.microsoft.com/en-us/library/cc442765.aspx.

    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, October 6, 2015 7:39 PM
    Moderator
  • I know they won't hookup automatically by magic I do it programmatically, I already do it at runtime for the first Row when the Document is created from the Template. It loads the data from an Excel Worksheet to populate the ListBoxes and then I hookup some events of the ListBoxes to keep track of the Selected Items. I use the Cached Attribute to save all the data in the Data Island of the Document and it work perfectly well so if I can find a way to access the ListBoxes from a Range I know it will work as expected.

    Tuesday, October 6, 2015 8:22 PM
  • <<so if I can find a way to access the ListBoxes from a Range I know it will work as expected.>>

    In that case, why not copy/paste the complete Table row?


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, October 7, 2015 2:57 PM
    Moderator
  • I need to be able to update the Tag Property since it's my Key in the Dictionary used to track the Selected Items of the ListBox.
    Wednesday, October 7, 2015 4:58 PM
  • Is this simply additional information or is there a problem with my proposal to copy/paste the entire table row then edit the result?

    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, October 7, 2015 5:00 PM
    Moderator
  • I have no problem doing it in Visual Studio but I want to be able to add rows at runtime on the Click of a Button. I can Copy/Paste programmatically but the Tag value would be the same so I need to be able to update it programmatically.

    Wednesday, October 7, 2015 5:15 PM
  • Hi Alain

    I think we're still not communicating on the same wave-length :-)

    I'm proposing you do the copy/pasting at run-time. Instead of creating a new row from scratch then copy/pasting each and every individual control, just copy the entire row, then paste it...


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, October 7, 2015 5:23 PM
    Moderator
  • Hi Cindy,

    Yes I think we are still not on the same wave-length ;-)

    First keep in mind that I need to be able to update the Tag Property of the ListBox, it can be done easily at design-time but I have not found the way to access the Properties of the ListBox at run-time yet.

    I am actually copying/pasting the entire Row at run-time, but by copying it I should have the exact same Tag Property value for each ListBox in the same column and since the Tag is used as a Key I need to be able to update that value so that's why I need to be able to access the Tag Property of the ListBox.

    Wednesday, October 7, 2015 5:55 PM
  • Hi VAlain,

    >> First keep in mind that I need to be able to update the Tag Property of the ListBox, it can be done easily at design-time but I have not found the way to access the Properties of the ListBox at run-time yet.

    Did you want to get the Tag Property of the Listbox and set it? If so, you could get specific control from ControlCollection with "Globals.ThisDocument.Controls". Here is a simple code:

               ListBox a = (ListBox)Globals.ThisDocument.Controls[0];
               a.Tag = "Hello Word";
               foreach (Control item in Globals.ThisDocument.Controls)
               {
                   if (item.GetType().Name == "ListBox")
                   {
                       ListBox lb = (ListBox)item;
                       MessageBox.Show(lb.Tag.ToString());
                   }
               }

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, October 8, 2015 7:46 AM
  • Hi Edward,

    Yes this is what I want to do and I know about the Controls collection but I need to get the ListBox in a particular Range. The Controls collection is only available from the Document, there is no Controls property in the Range Object like there is for the ContentControls collection.

    Thursday, October 8, 2015 2:17 PM
  • Hi Alain

    Looking at the list of properties for the WinForm listBox control I see: InlineShape and the reference states:

    "Gets the underlying InlineShape of the ListBox."

    I think this is what you need?


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, October 8, 2015 5:33 PM
    Moderator
  • Hi Cindy,

    Yes I know, I use it to get the Range of my Button to know in which Table it is and then get the last Row of that Table but now I need the get the Controls contained in that Row.

    Thursday, October 8, 2015 5:46 PM
  • Could you provide the link to the article you use to re-create the controls when the document is opened? Getting to the VSTO documentation on MSDN is a real PITA at the moment... Somehow, that code must know how to "talk" to "unconnected" controls in the document, but I can't remember how it looks...

    The thought that comes to mind is to loop a collection and compare location/tag names. The later table row index  would be what you need to process?

    But you might end up being faster by creating each control "from scratch"...


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, October 8, 2015 5:59 PM
    Moderator
  • I actually have only static controls in the first Row of the Table and I connect to them at Startup by looping through the Controls collection. My code is not based on any actual article, I populate the ListBoxes from Excel based on the Tag value of the ListBox and save its state in a Cached Dictionary using it's Tag as the Key.

    What I want to achieve is dynamically adding a Row that would be a duplicate of the first Row with updated Tags. I first tried to Copy/Paste the Row but I can't find the way to access the Control inside the Wrapper (IActiveXWrapper) I could not find any documentation on that Interface.

    I know I can create the controls dynamically with AddControl and then give them any Tag I want but then the problem is to determine the Tag value as I can't access the Tags from the previous Row. If the Tag of the Control in the first Row is "Region[1]" it need to set it to "Region[2]" for the new Row.

    Thursday, October 8, 2015 6:55 PM
  • Hi VAlain,

    >> Yes this is what I want to do and I know about the Controls collection but I need to get the ListBox in a particular Range

    I am a little confused with your requirement. What is your requirement? Based on your description, you want to get and set the Tag property of ListBox, I think my code has meet your requirement. But now, it seems you want to get the Range of ListBox, if so, I think the suggestion from Cindy would meet your requirement.

    It would be helpful if you could share us what you want.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, October 9, 2015 3:39 AM
  • Hi Edward,

    I don't want to get the Range of a ListBox (I know to do that) but I want to get the ListBox contained in a particular Range. I can't get it by the Controls collection because I don't know the name of the ListBox beforehand.

    What I want to do is to dynamically duplicate a Table Row containing ListBoxes and then update the Tags of those ListBoxes. To do so I'm looping through the Table Row and I try to get the ListBox contained in each Cell of that Row.

    Friday, October 9, 2015 4:14 AM
  • Maybe I'm missing something, but I don't see a Tag property for ActiveX controls of any kind. Do you mean the Name property? If so, you could use code like:

    Sub AddRow()
    Application.ScreenUpdating = False
    Dim i As Long, j As Long
    With Selection.Tables(1).Rows
      With .Last.Range
        .Next.InsertBefore vbCr
        .Next.FormattedText = .FormattedText
      End With
      i = .Count
      With .Last.Range.InlineShapes
        For j = 1 To .Count
          With .Item(j).OLEFormat.Object
            If InStr(.Name, "Checkbox") > 0 Then .Name = "Checkbox" & i
            If InStr(.Name, "ComboBox") > 0 Then .Name = "ComboBox" & i
            If InStr(.Name, "CommandButton") > 0 Then .Name = "CommandButton" & i
            If InStr(.Name, "Image") > 0 Then .Name = "Image" & i
            If InStr(.Name, "Label") > 0 Then .Name = "Label" & i
            If InStr(.Name, "Listbox") > 0 Then .Name = "ListBox" & i
            If InStr(.Name, "OptionButton") > 0 Then .Name = "OptionButton" & i
            If InStr(.Name, "ScrollBar") > 0 Then .Name = "ScrollBar" & i
            If InStr(.Name, "SpinButton") > 0 Then .Name = "SpinButton" & i
          End With
        Next
      End With
    End With
    Application.ScreenUpdating = True
    End Sub


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Friday, October 9, 2015 8:38 AM
  • Yes I think you are missing the fact that it's a VSTO Project so maybe there is no Tag property in VBA but there is one in VSTO with many more other features not available in VBA.
    Friday, October 9, 2015 2:41 PM
  • Hi Alain

    <<I know I can create the controls dynamically with AddControl and then give them any Tag I want but then the problem is to determine the Tag value as I can't access the Tags from the previous Row. If the Tag of the Control in the first Row is "Region[1]" it need to set it to "Region[2]" for the new Row>>

    Count the number of rows and use that for the region designation?

    Or maintain a list (or lists) of tag names so that you can "look up" the most recent and then increment?


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, October 9, 2015 7:23 PM
    Moderator
  • Hi Cindy,

    I have more than one Table with more than one ListBox in each Table, how do I know I am in the Region instead of the Subregion or any other Column to compute the right Tag?

    What I done instead is a Dictionary matching the Control Name with it's Wrapper Name and since I can get the Wrapper Name from InlineShape and it work well with static Controls but when I try to access the InlineShape Property of a dynamically created Control I get a Null Reference Exception.

    I also have another issue referring to the Table, I needed to hardcode it as Table(1) but I have more than one Table. I tried to reference the Table from the Parent Property of the Cell but it returns a reference to the Document instead of the Table as expected.


    • Edited by VAlain Saturday, October 10, 2015 9:16 PM
    Friday, October 9, 2015 7:57 PM
  • I managed to get it work by maintaining a Dictionary matching the Wrapper Name with the Control Name.

    It look likes the dynamically created Controls are not saved with the Document but since the Wrapper is saved I should be able to recreated them in the same Cell by inspecting the empty Wrappers.

    The only problem left is that I don't know how I can determine in which Table a Cell is since the Parent Property returns a reference to the Document instead of the Table.

    So I will mark the question as answered thanks all for the effort to answer me.

    • Marked as answer by VAlain Saturday, October 10, 2015 9:44 PM
    Saturday, October 10, 2015 9:44 PM
  • Something like this: cell.range.tables(1) (I'm writing on an ipad, so forgive the bad formatting...)

    Cindy Meister, VSTO/Word MVP, my blog


    Saturday, October 10, 2015 9:54 PM
    Moderator
  • This will returns the Tables inside that Cell but what I need is the Table containing that Cell, in VBA the Parent Property of the Cell object returns the Table as expected which is exactly what I need but in VB.NET it returns the Document instead.

    Sunday, October 11, 2015 3:52 AM
  • Hi Alain

    Do you have tables inside the cell? If not, that line of code will do as you wish - try it.

    If your cell does have a table in it, then you can use this:

    Cell.Range.Rows(1).Range.Tables(1).Select


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, October 11, 2015 4:04 PM
    Moderator
  • Hi Cindy,

    I tried and it works thanks, in fact Cell.Range.Tables(1) is enough as you suggested in your previous post and it works even with sub-tables since I would refer to the sub-table as Cell.Tables(1)

    Sunday, October 11, 2015 4:49 PM