You can access the underlying Interop object from a host control by using the control's InnerObject method. However, there is no way to do the opposite: access a host control from the underlying Interop object. VSTO does not provide a collection for each of the host controls; instead, there is a single Controls collection, which can contain host controls as well as Windows Forms controls that are on the worksheet.
Although VSTO does not have a collection of ListObjects, NamedRanges, XMLMappedRanges, and Charts, you can loop through a collection of the underlying Interop objects. Listing 7.16 shows how you can loop through a collection of list objects, just as you can when you use VBA. This code assumes that you have at least one list object on Sheet1 of your workbook.
Listing 7.16. The Interop ListObject collection
For Each list As Microsoft.Office.Interop.Excel.ListObject _ In Me.ListObjects
Because these are Interop list objects, you do not have access to the data binding and events that VSTO provides. You cannot use a For Each statement for a VSTO ListObject collection, because no such collection exists in VSTO. If you change the code in Listing 7.16 to declare the list variable as the type Microsoft.Office.Tools.Excel.ListObject, the code will compile fine, but you will receive an error when you run the solution code.
As an alternative, you can use the Controls collection to loop through each control in the collection and then determine whether the control is of the type you are interested in—in our case, a ListObject host control. Listing 7.17 loops through all the controls in the Controls collection and checks whether the control is of the type Microsoft.Office.Tools.Excel.ListObject. If the control is a ListObject control, its name is displayed in a message box. Because you declare myControl as an Object, you must cast the object back into a ListObject control before you can access its properties, methods, and events.
Listing 7.17. The Controls collection Dim myControl As Object
For Each myControl in Me.Controls
If TypeOf(myControl) Is Microsoft.Office.Tools.Excel. _ ListObject Then
Dim VSTOListObject As Microsoft.Office.Tools.Excel. _ ListObject = CType((myControl), _ Microsoft.Office.Tools.Excel.ListObject)
Instead of calling code like this each time you need to access the host control from the underlying object, you can create a helper function for each host control so that you can obtain the corresponding control whenever you have accessed the Interop object. For an example, see Listing 6.13 in Chapter 6.
Was this article helpful?