Accessing Excel.ComboBox from C#

I have a combobox (the drop down list control) in a pre-existing Excel template. I can reference this combobox in VBA with Sheet10.ComboBox1.

How can I reference this through Excel Interop in C#?

What I 开发者_开发知识库am trying to do is populate this combobox/dropdown with C# and have it positioned where I want with the template.

Thanks in advance.

The VBA code to do it is below. Basically you need to access the Worksheet.Shapes collection to find the item that corresponds to your ComboBox (either by index or more realistically by name). Then traverse the properties OLEFormat -> Object -> Object, casting as appropriate. The C# code is very similar.

Dim wks As Worksheet
Dim objShape As Shape
Dim objComboBox As ComboBox
Dim objOleObject As Excel.OleObject

Set wks = Sheet1
Set objShape = wks.Shapes(1)
' or Set objShape = wks.Shapes("ComboBox1")
Set objOleObject = objShape.OLEFormat.Object
Set objComboBox = objOleObject.Object

This stuff isn't documented in the API because it's meant for other .NET components to use rather than you. Nonetheless, here's how I set first item as selected on a Form ComboBox named "Box 1" on the first sheet of (an otherwise) blank workbook in Excel 2010. Note that I'm using ExcelDna and log4net - thus the log lines and where I get my excel reference from. I recommend both.

using Microsoft.Office.Interop.Excel;

Application Xl = (Application) ExcelDnaUtil.Application;
DropDown box;

try
{
    box = Xl.ActiveWorkBook.Sheets["Sheet1"].DropDowns("Box 1");
}
catch (Exception e)
{
    Log.Fatal("Failed to find drop down called 'Box 1' on sheet", e);
    throw;
}
try
{
    box.Selected = 1;
}
catch (Exception e)
{
    Log.Fatal("Failed to set value of drop down", e);               
    throw;
}