0

I want to programatically choose one of the options available in a Excel Dropdownlist. To manipulate the worksheet I'm using win32ole on ruby. It works well until I try to change the value of the dropdownlist.

I simply don't know how and google has been of no help.

setting a value to a a cell is as simple as worksheet.Cells(x,y).Value=new_value. This is not choosing one of the alternatives available on the dropdownlist and it's not even possible since the cell in question is protected.

The protection doesn't stop me from changin the value manualy via excelso I figure there must be a method or functon somewhere that allows me to simulate this action aswell.

Help in advance for any suggestions.

EDIT: I'm trying to choose one of the values available in the dropdown list.

Kenneth Ivan Rodriguez
  • 55
  • 8
  • Are you trying to change the value of the dropdown list or are you trying to choose one of the values available in the dropdown list? – Charles Caldwell Apr 24 '13 at 11:56
  • trying to choose one of the values available in the dropdown list. – Kenneth Ivan Rodriguez Apr 24 '13 at 12:01
  • I've spent a whole lot of time searching for a understandble(for me) documentation for WIN32OLE. It's pretty straight fordward to find a given cell and read or write a value to it. But i cannot for the life of me find what to do with a cell that it's a dropdown. Getting the current chosen option is as easy as getting the value of a Cell. But I've been searching for a way to get the array that must be the dropdownlist. – Kenneth Ivan Rodriguez Apr 25 '13 at 06:54
  • I think I've been going about it the wrong way. I've thinking the dropdown options would be somehow stored in the cell. I now think this is incorrect. There are a group of hidden cells somewhere in the document containing the options of the dropdown. I'm certain of it now. I just seem unable to unhidden it. – Kenneth Ivan Rodriguez Apr 25 '13 at 09:19

1 Answers1

0

I didn't find an api specific for dropdows, but I manage a work around. The data of the dropdown is somewhere in the workbook. Just choose from them and set it to the dropdown cell with "Cells.Value=new_value".

It was a little different for me since my workbook I was working on had some kind of protected custom dropdown but esencially the solution is realizing that the data of the dropdown can be found somwhere else in the workbook.

Kenneth Ivan Rodriguez
  • 55
  • 8