How to Select Item From Dropdown Inside Excel File?

Help
, ,
1

What to Achieve:
Select data from a dropdown inside an Excel file.
20180801Select%20Inside%20Excel

Note:
Sure I can just use Write Cell to write despite the available values, but the Excel is for input of another robot, so I need to make sure only available values in entried (what I mean, if “Pie” is not on the selection, I should not enter it).

Question:
Any suggestion on how to do that?

2

How do you want to do this - without opening the file in excel application or with opening ?

3

Hello @skini76, thank you for your reply. If there is any way to do this, I don’t mind whether it is with opening or without opening Excel Application Scope. If you’re feeling generous I’d like to know both ways so I can compare which one is more suitable in my case.

4

Opening the file in excel application - not very difficult, when you type a value outside the list, it throws an error (provided show error is enabled), you can capture the error using Image exists, will this work for you.

5

No, I tried that using Write Cell of Excel Application Scope, even though the value is not on the drop down, it throws no error. @skini76

6

You can try this :
Use Excel Application Scope to open the excel sheet.
Select Range - point to which ever cell that has the options
Type into “[k(alt)]avv” Shortcut to go to Data Validations that has the options listed
Get Text from the source field to get all options. Check if your option is available in the list.
If yes, then get the position of the option (1st / 2nd / 3rd / …)
Click OK or cancel to dismiss the window.

You can either write the option using Write Cell , which ideally overwrites the cell content.
(OR)
With the range still selected Type into “[k(alt)][k(down)]” to open the ‘in cell dropdown’ list.
Type into “[k(alt)][k(down)]” as many times as the position of the option (or one less than that) to move to the option.
Type into “[k(enter)]” to choose the option.

Hope this helps!

4 Likes
7

Thank you @kaderms, never thought I could get the list from that window!
I hope there is easier way to do this, but for now it it enough! Thank you so much!

1 Like
8

You can try screen scraping after pressing alt + down on the cell. But it has is own pros n cons!

9

Yes, I thought of that too but decided it was more prone to mistakes.
Your suggestion to get the list from the Data Tab seems to be more stable so I’d try that one! @kaderms

1 Like
10

image
working for me

11

Strange, did you use Write Cell @skini76 ?
It threw me no error.
But I found another workaround, thank you!