none
Accesing a drop-down list in excel using PowerShell RRS feed

  • Question

  • Hi Guys, 

    I just wanted to find out if there is any way after reading an excel sheet using Powershell can i access a dropdown list in that sheet and loop through all the options in that drop down list?

    • Moved by Bill_Stewart Monday, September 22, 2014 7:23 PM Move to more appropriate forum
    Monday, September 22, 2014 7:19 PM

All replies

  • Hi,

    What's the meaning your "dropdown list"? Do you mean the Combo Box control or Range data validation as followed?

    If the dropdown list means Range data validation, we can resort to Validation.Formula1 Property of Range Object to get the value or expression associated with the conditional format or data validation.

    If the dropdown list means ActiveX Combo Box, we need to access to the control and its items. We can use OLEObject object to access to the control. Here is a VBA sample to print the value of first item of ComboBox1 for your reference.

    Sub test()
    Dim obj As OLEObject
    Set obj = Sheet3.OLEObjects("ComboBox1")
    Debug.Print obj.Object.List(0)
    End Sub


    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, September 23, 2014 9:56 AM
    Moderator
  • Thanks for replying. I was able to do this in VB script but i was trying to do it in powershell. I am just trying to loop through all the options in a Combo Box and it seems that there is no easy way to do that. 
    Tuesday, September 23, 2014 8:03 PM
  • Hi,

    What's the meaning your "dropdown list"? Do you mean the Combo Box control or Range data validation as followed?

    If the dropdown list means Range data validation, we can resort to Validation.Formula1 Property of Range Object to get the value or expression associated with the conditional format or data validation.

    If the dropdown list means ActiveX Combo Box, we need to access to the control and its items. We can use OLEObject object to access to the control. Here is a VBA sample to print the value of first item of ComboBox1 for your reference.

    Sub test()
    Dim obj As OLEObject
    Set obj = Sheet3.OLEObjects("ComboBox1")
    Debug.Print obj.Object.List(0)
    End Sub


    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.

    So, can someone help me with the powersehll equivalent of the code above ??

    Niranjan

    Friday, June 12, 2015 6:48 AM