Search code examples
javascriptexcelvbainternet-explorer

VBA Select Item from List in Internet Explorer

I'm trying to create a macro that will perform certain tasks on a webpage. What I'm struggling with is to select an option from a dropdown list.

This is (part of) my VBA Code:

Sub stackoverflow()

' ***** Dims for VBA *****
Dim objIE As SHDocVw.InternetExplorer
Dim ieDoc As MSHTML.HTMLDocument
Dim testURL As String
' ***** END ***** 

' ***** Dim Buttons*****
Dim StatusFld As MSHTML.HTMLInputElement
' ***** END *****

testURL = "https://test.com"


'Create Internet Explorer Object
Set objIE = New SHDocVw.InternetExplorer

'Navigate to URL
objIE.navigate testURL

'Wait until page has loaded up
Do Until objIE.readyState = READYSTATE_COMPLETE: Loop

'Get document object
Set ieDoc = objIE.document
objIE.Visible = True

'***** Button IDs *****
Set statusFld = ieDoc.all.Item("apprverStatusCode")
'***** END *****

'THIS IS THE ROW WITH THE ERROR:
objIE.document.getElementById(statusFld).Value = "Rejected"

So with the above code, I get "Run time error '424' - Object required".

I've also tried this, which was also not successful:

statusFld.Value = "Rejected"

This is the source code of the website:

<select id="apprverStatusCode" data-code="GROUP_STATUS" data-servicecode="{"preview":"20501","download":"20502"}"
>
<option value="ALL">All</option>
<option value="Rejected">Rejected</option>
<option value="Approved">Approved</option>
</select>

Any ideas why this isn't working?

Solution

  • objIE.document.getElementById("apprverStatusCode").Value = "Rejected"

    However that isn't how elements are selected. You need to iterate through the options and add a "selected" attribute to the one you want to select, and remove it from any you do not.

    Dim oOptions As MSHTML.IHTMLElementCollection
Dim oOption As MSHTML.IHTMLOptionElement

Set oOptions = objIE.document.getElementById("apprverStatusCode").getElementsByTagName("option")

For Each oOption In oOptions
    If oOption.Value = "Rejected" Then
        oOption.Selected = True
    Else
        oOption.Selected = False
    End If
Next oOption

    I haven't tested this code, so not sure if the datatypes are correct/will work in the for each.

    See https://stackoverflow.com/a/38658524/212869 it looks a bit cleaner than mine.