Excel VBA和IE 11-在下拉列表中选择值后无法刷新页面 [英] Excel VBA & IE 11 - Unable to refresh page after selecting value in a dropdown

问题描述

我正在尝试获取WorldRemit提供的两种货币的汇率.我想更改网页左上角发送自"下拉列表中的值. ( https://www.worldremit.com/en/South-Africa )

我无法使用.Selected = True或.Click选择下拉选项，因此使用了.SelectedIndex.选择该值后，我将无法触发刷新页面的更改事件.如果有人可以帮助我解决这个问题，那就太好了.

导航到页面的代码:

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

使用.SelectedIndex属性选择选项的代码:

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

编辑#1:包含相关元素的HTML代码段为

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

解决方案

尝试一下，它对我有帮助.有时使用jQuery会容易一些，尤其是当页面也像此处一样使用jQuery时.

您可以通过使用IE的execScript函数来使用jQuery.见下文:

 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

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>

解决方案

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

这篇关于Excel VBA和IE 11-在下拉列表中选择值后无法刷新页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！