Dropdown box in Excel

[Saintsfan]

I'm new to Autoit but learning fast.

I writing a script to extract cells from a number of Excel spreadsheets and write them to a "summary" spreadsheet.

It all works fine except that one of the items I need is the contents of a dropdown box. I realise that this is an object, not a cell, but I'm not too clear how to read the contents.

Any help would be appreciated.

[Juvigy]

If it is a basic dropdown box - most likely "Data Validation - List" it should be easily read as a normal Cell:

msgbox(0,"",$oExcel.Range("B5").Value)

[Saintsfan]

It doesn't appear to be a standard dropdown.

It seems to sit on top of existing cells and it's possible values are held in a hidden column.

[Juvigy]

Then it is a VBA or something like this. Have to check the source code for this.

Can you upload the workbook ?

[Saintsfan]

It won't let me attach an Excel file

[Juvigy]

It won't let me attach an Excel file

Upload it somewhere (http://www.4shared.com/for example ) and give us a link.

[Saintsfan]

http://www.4shared.com/document/j9twOLTs/SS1.html

It's the box for "Bids received by".

[Juvigy]

Try this:

$oExcel=_ExcelBookAttach("c:\SS1.xls")
ConsoleWrite($oExcel.Application.ActiveSheet.DropDowns("Drop Down 171").Value &@CRLF)

You will receive the index number of the selected dropdown value. - For Example if "Tender" is selected you will receive 2

as it is the second value from the list , and you will receive 4 if "Inverse Auction" is selected.

[JoHanatCent]

It's the box for "Bids received by".

These are Drop Down controls. It would have been a lot easier to convert all to Validation drop downs

and then read from the cell directly. However if you insist in using as is try the following:

#include <Excel.au3>
$oExcel = _ExcelBookOpen(@DesktopDir & "\test2.xls", 1)
With $oExcel.ActiveSheet.DropDowns("Drop Down 171")
    $Selected = .List(.ListIndex)
EndWith
MsgBox(0, '', $Selected)

[Saintsfan]

I have no control over the format of the spreadsheet.

Both solutions work fine.

Thanks for everyones help.

Edited February 15, 2011 by Saintsfan

[CHAFF]

If it is a basic dropdown box - most likely "Data Validation - List" it should be easily read as a normal Cell:

msgbox(0,"",$oExcel.Range("B5").Value)

Hi Juvigy, but how could you actually read all the values in this list? Your example would only give you the top(visible) entry. Edited March 21, 2011 by CHAFF

[Juvigy]

I am guessing you speak of a DataValidation List ?

It is a list to a range of cells.Here is an example:

With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$L$8:$L$12"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

As you can see , the values are located : - "$L$8:$L$12"

[CHAFF]

Ah yes of course....this gives you the location of the list

Thanks.

Edited March 24, 2011 by CHAFF