CODEWITHSUNDEEP
javaexcelvbaweb-scraping
Ian Schneider
Ian Schneider

Reputation: 83

Pick value in dropdown menu using VBA web scrape

I've got an issue trying to select items from a dropdown menu on a javascript webpage. My end goal is to fill in the menu value via a userform but I'm not having any success creating the VB to select the drop down. Web code is below

<select name="dateSelector0" class="clsInputArea selectBox valid" style="display: none; "onchange="setDateRange(this, 'rtf[0].val1', 'rtf[0].val2')">
   <option value="-1"></option>
   <option value="1">Last Month</option>
   <option value="2">Current Month</option>
   <option value="3">Next Month</option>
   <option value="4">Last Year</option>
   <option value="5">Current Year</option>
   <option value="6">Next Year</option>
   <option value="7">First Quarter</option>
   <option value="8">Second Quarter</option>
   <option value="9">Third Quarter</option>
   <option value="10">Fourth Quarter </option></select>

<a tabindex="NaN" title="" class="selectBox clsInputArea selectBox-dropdown" style="width: 147px; display: inline-block;" href="javascript:void(0);"><span class="selectBox-label" style="width: 127px;">&nbsp;</span><span class="selectBox-arrow"></span></a>

I've tried various GetElementsBy, with both the above name, trying the below with the name and with ID rtf[0].val1 but to no avail, example below. I believe going by the dateSelector0 name will be best, but I would greatly appreciate input from those of you better at this than me.

ie.Document.Body.GetElementsByname("dateSelector0").Value = "1"
ie.Document.Body.GetElementsByname("dateSelector0").Item(0).FireEvent ("onchange")

Upvotes: 1

Views: 5736

Answers (2)

QHarr
QHarr

Reputation: 84465

If you observe the following CSS selector, where "." means class and " option" means select all "option" tags inside elements of that class (clsInputArea selectBox valid), you will see it makes the correct selections.

Note: You aren't allowed compound names in CSS selectors which is why the spaces are replaced with "." in the class name.

Sample CSS query results:

Sample results

VBA:

Now, we can translate that into the following syntax:

ie.Document.getElementsByClassName("clsInputArea selectBox valid")(0).getElementsByTagName("option")

This assumes that index 0 is the correct one to use for elements of the class "select_list". You can easily inspect the collection to find the right index if you set it to a variable e.g.

Dim x As Object
Set x = ie.Document.getElementsByClassName("clsInputArea selectBox valid")(0).getElementsByTagName("option")

Assuming the object is not nothing then we can either select an option by its index (i.e. position) or by looping the collection and selecting when a certain condition is met e.g. its innerText matches a wanted phrase.

Selecting by Index:

This means for you you might say

x.SelectedIndex = 1 to select Last Month

Selecting by looping:

Dim currentOption As Object
For Each currentOption In x
    If InStr(currentOption.innerText, "Last Month") > 0 Then
        currentOption.Selected = True
        Exit For
    End If
Next currentOption

Full example of selecting drop down:

As you don't supply an URL, here is an example using a different website. The webpage target drop down, Código de Ativo, is shown here:

Target dropdown

The target option to select is AALR21, which is number 3, or item 2 on 0 based index.

Looping and matching on the item innertext AAL21:

Option Explicit
Public Sub MakeSelectiong()
    Dim IE As New InternetExplorer
    Const URL = "http://www.debentures.com.br/exploreosnd/consultaadados/sndemumclique/"
    Const optionText As String = "AALR21"        'Number2
    Application.ScreenUpdating = False           '   a.selectedIndex = 2

    With IE
        .Visible = True
        .navigate URL
        While .Busy Or .readyState < 4: DoEvents: Wend

        Dim a As Object, currentOption As Object
        Set a = .document.getElementById("ctl00_ddlAti")

        For Each currentOption In a.getElementsByTagName("Option")
            If InStr(currentOption.innerText, optionText) > 0 Then
                currentOption.Selected = True
                Exit For
            End If
        Next currentOption
        Stop
        .Quit
    End With
End Sub

Selecting by index:

I could remove the entire loop and simply say:

a.selectedIndex = 2

Upvotes: 1

Hubvill
Hubvill

Reputation: 504

You almost had it. I just tried:

Sub IE_Navigate()

'Declare
Dim IE As Object

'Use IE
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

IE.navigate ("Website URL")

'Wait for Load to finish
While IE.readyState <> 4
DoEvents
Wend
Application.Wait (Now + TimeValue("0:00:01"))

Both of these approaches worked for me.
Select data from dropmenu by ClassName:

IE.document.getElementsByClassName("clsInputArea")(0).Value = 1

Or
Select data from dropmenu by Name

IE.document.getElementsByName("dateSelector0")(0).Value = 1

Which returned the first item from the drop down menu.

EDITED

Upvotes: 1

Related Questions