How to get the value in an Excel dropdown using C#

Question

I am looking for code to open and read an Excel file, any version of Excel, including 2010. One of my columns has a dropdown in it. I need to get the value of the selected item in the dropdown. I would eventually want to populate these values into a business object.

If anyone has some code to share please let me know.

I am using C# and Visual Studio 2010.

Thanks.

I wasn't aware that there were both? Have you got code for both? I assume forms dropdown? — Brendan Vogt, Nov 09 '10 at 12:05
A forms dropdown is much easier, because it can have a cell link that holds the value, so all you need to do is refer to the cell. — Fionnuala, Nov 09 '10 at 13:53
I've got a solution that also allows setting the value over @ http://stackoverflow.com/a/8521399/41153 — Michael Paulukonis, Dec 15 '11 at 20:20

Fionnuala · Accepted Answer · 2010-11-21T10:18:01.120

3

I know the VBA for both the ActiveX combo and the forms dropdown, and based on that, I can give you some very inexpert notes for c# for the forms dropdown, the combo eludes me as yet.

Working with notes from: http://support.microsoft.com/kb/302084

//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Open("C:\\Docs\\Book1.xls"));
//3rd Sheet
oSheet = (Excel._Worksheet)oWB.Sheets.get_Item(3);

//This will return an index number
var i = oSheet.Shapes.Item("Drop Down 1").ControlFormat.Value;
//This will return the fill range
var r = oSheet.Shapes.Item("Drop Down 1").ControlFormat.ListFillRange;
oRng = oSheet.get_Range(r);
//This will return the value of the dropdown, based on the index
//and fillrange
var a =oRng.get_Item(i).Value;

//Just to check
textBox1.Text = a;

This may help with an ActiveX combo, but I have only half got it to work:

using MSForm = Microsoft.Vbe.Interop.Forms;

<...>
Excel.OLEObject cbOLEObj = (Excel.OLEObject)workSheet.OLEObjects("ComboBox1");
MSForm.ComboBox ComboBox1 = (MsForm.ComboBox) cbOLEObj.Object; 
Console.WriteLine(ComboBox1.Text);

From: http://www.eggheadcafe.com/community/aspnet/66/10117559/excel-get-value-from-a-combobox.aspx

edited Nov 21 '10 at 10:18

answered Nov 09 '10 at 15:50

Fionnuala

90,370
7
114
152

Does it advance Stackoverflow to vote down the sole answer to a question without adding some comments? – Fionnuala Nov 18 '10 at 11:52
Edit your answer so that I vote it up again. Sorry for this. – Brendan Vogt Nov 21 '10 at 07:43
No problem. I can see it could deserve a down vote, in which case a note would be useful. It is also quite easy to down vote an answer when trying to do something else. – Fionnuala Nov 21 '10 at 10:16
Two hints for this answer: a) To find out the shape name, right-click the dropdown, the name will appear in the top left (I couldn't get it another way) and b) this: oRng = oSheet.get_Range(r); will only work if the lookup area is on the same sheet. – puls200 Jul 08 '22 at 05:57

How to get the value in an Excel dropdown using C#

1 Answers1

Linked