none
Reading DropDown Cell In Excel File Using Microsoft Interop Excel RRS feed

  • Question

  • Not able to fetch the excel dropdown values which is exported using  Microsoft Interop Excel

    The below Columns HighMarkCodeandDescription,ExperianCodeandDescription,EquifaxConsumercodeandDescription having dropdowns.But the value was unable to read using Microsoft Interop Excel.So please help regarding this

    Sno
    AccountNumber
    HighMarkCodeandDescription ExperianCodeandDescription EquifaxConsumercodeandDescription EquifaxCommercialcodeandDescription
    1 103300000001        
    2 103300000002        
    3 103300000003        
    4 103300000004        
    5 103300000005        
    6 103300000006        
    7 103300000007        
    8 103300000008        
    9 103300000009        

    • Moved by CoolDadTx Thursday, August 18, 2016 1:53 PM Office related
    Thursday, August 18, 2016 11:20 AM

Answers

  • >>The below Columns having dropdowns.But the value was unable to read using Microsoft Interop Excel.

     

    How do you add the dropdown list, by Data validation or inserting a control (Form control or ActiveX control)?

    What value do you want to get, all the value in the lists or the selected value in the cell?

    If it is a form control, please use  Shapes.Item method to get the object.

    E.g.

    Dim xl As Excel.Worksheet

            Dim list As Excel.Shape

            Dim Val As String

            list = xl.Shapes.Item("Drop Down 1")

            Val = list.ControlFormat.Value

    If it is an ActiveX control, please use OLEObjects Object (Excel) to get the object.

    E.g.

     Dim r As Excel.OLEObject

            Dim value As String

            r = xl.OLEObjects("ComboBox1")

            value = r.Object.Value

    Friday, August 19, 2016 6:05 AM
    Moderator
  • Here is C# solution to fetch values from dropdown cell.

    Workbook workbook = new Workbook();            
    workbook.LoadFromFile("test.xlsx");          
    Worksheet worksheet = workbook.Worksheets[0];
    CellRange CR = worksheet.Range["C2"];
    string[] s = CR.DataValidation.Values;
    Note that this solution is based on .NET Excel component.

    Monday, August 22, 2016 8:10 AM

All replies

  • Hello,

    For anyone whom wants to assist you need to show what has been attempted at this point.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, August 18, 2016 12:28 PM
  • >>The below Columns having dropdowns.But the value was unable to read using Microsoft Interop Excel.

     

    How do you add the dropdown list, by Data validation or inserting a control (Form control or ActiveX control)?

    What value do you want to get, all the value in the lists or the selected value in the cell?

    If it is a form control, please use  Shapes.Item method to get the object.

    E.g.

    Dim xl As Excel.Worksheet

            Dim list As Excel.Shape

            Dim Val As String

            list = xl.Shapes.Item("Drop Down 1")

            Val = list.ControlFormat.Value

    If it is an ActiveX control, please use OLEObjects Object (Excel) to get the object.

    E.g.

     Dim r As Excel.OLEObject

            Dim value As String

            r = xl.OLEObjects("ComboBox1")

            value = r.Object.Value

    Friday, August 19, 2016 6:05 AM
    Moderator
  • Here is C# solution to fetch values from dropdown cell.

    Workbook workbook = new Workbook();            
    workbook.LoadFromFile("test.xlsx");          
    Worksheet worksheet = workbook.Worksheets[0];
    CellRange CR = worksheet.Range["C2"];
    string[] s = CR.DataValidation.Values;
    Note that this solution is based on .NET Excel component.

    Monday, August 22, 2016 8:10 AM