Sign in to follow this  
Followers 0
Saintsfan

Dropdown box in Excel

13 posts in this topic

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.

Share this post


Link to post
Share on other sites



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)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Can you upload the workbook ?

Share this post


Link to post
Share on other sites

It won't let me attach an Excel file

Share this post


Link to post
Share on other sites

It won't let me attach an Excel file

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

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

I have no control over the format of the spreadsheet.

Both solutions work fine.

Thanks for everyones help.

Edited by Saintsfan

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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 by CHAFF

Share this post


Link to post
Share on other sites

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"

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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

Posted Image

Thanks.

Edited by CHAFF

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0