Selecting a dropdown in navigating site via vba

[Nelson78]

Hello everybody.

I'm struggling with some problems in selecting a dropdown navigating in Explorer via vba.

Above the code, until it works:

sub enter_site ()

 
Dim myId As String, myCode As String

myId = Range("E9")                   'user'
myCode = Range("F9")               'pw'
myURL = "https://aaa/bbb/Authentication/Login.aspx"

Set IE = CreateObject("InternetExplorer.Application")

With IE
    .navigate myURL
    .Visible = True
    Do While .Busy: DoEvents: Loop    'Attesa not busy
    Do While .readyState <> 4: DoEvents: Loop 'Attesa documento
End With

myStart = Timer

Do  
    DoEvents
    If Timer > myStart + 1 Or Timer < myStart Then Exit Do
Loop


IE.document.getElementById("UserName").Value = myId
IE.document.getElementById("Password").Value = myCode

Set mycoll = IE.document.getElementsByTagName("Input")
For Each myinp In mycoll
   
    If myinp.ID = "LoginButton" Then
        myinp.CLICK
        Exit For
    End If

Next myinp


    Do While IE.Busy: DoEvents: Loop    'Attesa not busy'
    Do While IE.readyState <> 4: DoEvents: Loop 'Attesa documento'

IE.document.getElementById("menu").CLICK
IE.document.getElementById("report").getElementsByTagName("a")(4).CLICK

Now, i've a dropdown to select.

Above the html code:

<select id="site" title="listsites" onchange="javascript:setTimeout('__doPostBack(\'site\',\'\')', 0)" name="site">
<option value="0" selected="selected">Select</option>
<option value="1">London1</option>
<option value="2">London2</option>
<option value="3">Brighton</option>
<option value="4">London3</option>

Maybe for the intrinsic caracteristic of the page (aspx), I can't proceed. I've tried selecting by id and also with Sendkeys, with no positive results.


Could you get me some hints, please?

 

[John_w]

The select element has a Javascript onchange event, so you might need to call dispatchEvent on it. Add a reference to MS HTML Object Library (Tools - References in VBA editor) and try this code:

    Dim HTMLdoc As HTMLDocument
    Dim selectElement As HTMLSelectElement
    Dim evtChange As Object
    
    Set HTMLdoc = IE.document
    Set evtChange = HTMLdoc.createEvent("HTMLEvents")
    evtChange.initEvent "change", True, False
    Set selectElement = HTMLdoc.getElementById("site")
    selectElement.Value = "1" 'London1
    selectElement.dispatchEvent evtChange

 

[Nelson78]

Building the code with your suggestions, I've error 91 on the row

selectElement.Value = "1" 'London1

Probably i do mistakes in building the code.
The MS HTML Object Library was already set (anyway I've checked to be sure).
Important (maybe): I work with IE9

sub enter_site ()

 
Dim myId As String, myCode As String

    Dim HTMLdoc As HTMLDocument
    Dim selectElement As HTMLSelectElement
    Dim evtChange As Object
    
myId = Range("E9")                   'user'
myCode = Range("F9")               'pw'
myURL = "https://aaa/bbb/Authentication/Login.aspx"

Set IE = CreateObject("InternetExplorer.Application")

With IE
    .navigate myURL
    .Visible = True
    Do While .Busy: DoEvents: Loop    'Attesa not busy
    Do While .readyState <> 4: DoEvents: Loop 'Attesa documento
End With

myStart = Timer

Do  
    DoEvents
    If Timer > myStart + 1 Or Timer < myStart Then Exit Do
Loop


IE.document.getElementById("UserName").Value = myId
IE.document.getElementById("Password").Value = myCode

Set mycoll = IE.document.getElementsByTagName("Input")
For Each myinp In mycoll
   
    If myinp.ID = "LoginButton" Then
        myinp.CLICK
        Exit For
    End If

Next myinp


    Do While IE.Busy: DoEvents: Loop    'Attesa not busy'
    Do While IE.readyState <> 4: DoEvents: Loop 'Attesa documento'

IE.document.getElementById("menu").CLICK
IE.document.getElementById("report").getElementsByTagName("a")(4).CLICK

    Set HTMLdoc = IE.document
    Set evtChange = HTMLdoc.createEvent("HTMLEvents")
    evtChange.initEvent "change", True, False
    Set selectElement = HTMLdoc.getElementById("site")
    selectElement.Value = "1" 'London1
    selectElement.dispatchEvent evtChange

 

[John_w]

I tend not deal with magic numbers like 91. What is the error text?

Is the select element ready or available at the time the code references it? Try changing the Set selectElement line to:

    Do
        Set selectElement = HTMLdoc.getElementById("site")
        DoEvents
    Loop While selectElement Is Nothing

 

[Nelson78]

Object variable or With block variable not set (Error 91)

also after the last suggestion, at the following line:

Set selectElement = HTMLdoc.getElementById("site")

 

[John_w]

That suggests HTMLdoc is Nothing. Is HTMLdoc Nothing? Find out by looking in the Locals window.

If not Nothing, is HTMLdoc referencing the expected page? To check:

MsgBox HTMLdoc.body.innerText

Does it display the expected text?

 

[Nelson78]

Actually, after this instruction

IE.document.getElementById("report").getElementsByTagName("a")(4).CLICK

the site charges for about one second, something like a subpage, in despite of no changes in the address bar (the url is always myURL).

Anyway, using the hints I managed to select correctly two dropdowns.

Now, I'm in trouble with "submit".

The following way doesn't work anymore (error 438 Object doesn't support this property or method), probably because the method has been changed (using htmldoc).

IE.document.getElementById("ok").submit

How could I click on this "submit"?


input id="ok" type="submit" style="margin: 0 5px 10px; height: 25px; background: #AF2A2A ; color: #fff ; font-size: 1em; font-weight: bold; Width:150px" value="Select" name="Sel"/

 

[John_w]

Try Click instead of submit.

 

[Nelson78]

Try Click instead of submit.

Yes: using "click" it works.

 

[hksar5]

selectElement.dispatchEvent evtChange

Hi, I got error 328 at above line. Much appreciated if you could help!