CODEWITHSUNDEEP
excelvbainternet-explorerweb-scraping
cattlesnake
cattlesnake

Reputation: 11

Excel VBA & IE 11 - Unable to refresh page after selecting value in a dropdown

I'm trying to get the currency exchange rate offered by WorldRemit for a pair of currencies. I want to change the value in the 'Send From' dropdown list on the top left corner of the webpage. (https://www.worldremit.com/en/South-Africa)

I wasn't able to select the dropdown option using .Selected = True or .Click so have used .SelectedIndex. After selecting the value, I'm not able to trigger the change event that refreshes the page. Would be great if someone can help me figure this out.

Code for navigating to the page:

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate "https://www.worldremit.com/en/South-Africa"
While ie.busy
  DoEvents
Wend
Set HTMLdoc = ie.document

Code for selecting option using .SelectedIndex property:

Dim fromSelect As HTMLSelectElement
Set fromSelect = HTMLdoc.getElementById("selectFrom")
optionIndex = Find_Select_Option(fromSelect, "Germany")
If optionIndex >= 0 Then
    fromSelect.selectedIndex = optionIndex
    fromSelect.FireEvent ("onchange") ' this doesn't work
End If

Function for selecting option (used in the code above):

Function Find_Select_Option(selectElement As HTMLSelectElement, optionText As String) As Integer

Dim i As Integer 
Find_Select_Option = -1
i = 0
While i < selectElement.Options.Length And Find_Select_Option = -1
    DoEvents
    If LCase(Trim(selectElement.Item(i).Text)) = LCase(Trim(optionText)) Then Find_Select_Option = i
    i = i + 1
Wend    
End Function

Edit #1 : The HTML snippet containing the element in question is

<select id="selectFrom" data-track-field-name="from country" data-track-event="change">...</select>

Upvotes: 1

Views: 2251

Answers (2)

cattlesnake
cattlesnake

Reputation: 11

Apparently FireEvent doesn't work all that well with IE 11 so need to use CreatEvent + initEvent + dispatchEvent

Working code snippet below:

Dim fromSelect As HTMLSelectElement
Dim evt As Object

Set evt = HTMLdoc.createEvent("HTMLEvents")
evt.initEvent "change", True, False
Set fromSelect = HTMLdoc.getElementById("selectFrom")
optionIndex = Find_Select_Option(fromSelect, "Germany")
If optionIndex >= 0 Then
    fromSelect.selectedIndex = optionIndex
    fromSelect.dispatchEvent evt
End If

Upvotes: 0

Ryan Wildry
Ryan Wildry

Reputation: 5677

Give this a try, it's working on my end. Sometimes using jQuery is a bit easier, especially when the page also uses jQuery as it does here.

You can use jQuery by using the execScript function of IE. See below:

 Public Sub test()
    Dim IE As Object: Set IE = CreateObject("InternetExplorer.Application")

    With IE
        .Visible = True
        .navigate "https://www.worldremit.com/en/South-Africa"
        'Wait for the page to load
        While .busy Or .readyState <> 4
            Application.Wait (Now() + TimeValue("00:00:01"))
            DoEvents
        Wend
        'Use JQuery to find the element based on ID, then make the Selected property true
        'Once that is done, call the change event in jQuery
        .document.parentWindow.execScript "$('#selectFrom option:contains(Germany)').prop('selected','True')"
        .document.parentWindow.execScript "$('#selectFrom option:contains(Germany)').change()"
    End With
End Sub

Upvotes: 1

Related Questions