how to select a specific dropdown element in webpage by excel vba

Question

Currently, I am working on a webpage to extract some data related to my work. Here I am facing a problem while selecting the drop-down menu in the production list. I want to select the "All" option in the drop-down list. By default, it takes only 25 values in the production table. Here is my code

Sub Production_table()

    Dim objIE As InternetExplorer
    Dim ele As Object
    Dim ele2 As Object
    Dim ele3 As Object
    Dim s As String
    Dim i As Integer

    i = 2

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Do While Range("A" & i).Value <> ""

    Set objIE = New InternetExplorer
    objIE.Visible = True
    source = "https://secure.conservation.ca.gov/WellSearch/Details?api=01120716&District=&County=&Field=&Operator=&Lease=&APINum=&address=&ActiveWell=false&ActiveOp=false&Location=&sec=&twn=&rge=&bm=&PgStart=0&PgLength=10&SortCol=6&SortDir=asc&Command=Search
    objIE.navigate source"
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
    Sheets.Add
    ActiveSheet.Name = "Data"
    On Error Resume Next

    Set ele2 = objIE.document.getElementById("productionTable_length"). _
      getElementsByTagName("label")

    For Each ele3 In ele2
    If ele3.Value = -1 Then
    ele3.Focus
    ele3.Selected = True
    ele3.Click
    Exit For
    End If
    Next

webpage link-- https://secure.conservation.ca.gov/WellSearch/Details?api=01120716&District=&County=&Field=&Operator=&Lease=&APINum=&address=&ActiveWell=false&ActiveOp=false&Location=&sec=&twn=&rge=&bm=&PgStart=0&PgLength=10&SortCol=6&SortDir=asc&Command=Search

Please help me on the same.

I do not see you interacting with tblWellRecords_length anywhere? — Siddharth Rout
– Siddharth Rout, Commented Sep 24, 2018 at 11:04
When you see the page, down to the "Production Data", you find the drop-down menu to select all data at once. I want that menu to select all option. — Partha
– Partha, Commented Sep 24, 2018 at 11:08
I think the element I am looking for is tagged in "tblWellRecords_length" id. In the source code of the webpage, "-1" is the value for all. — Partha
– Partha, Commented Sep 24, 2018 at 11:10

Siddharth Rout · Accepted Answer · 2018-09-25 08:18:42Z

1

I always recommend using Chrome Browser for such cases as it let's you inspect the element in a userfriendly way.

Try this

Sub Sample()
    Dim objIE As Object, ele As Object, opt As Object

    Set objIE = CreateObject("InternetExplorer.Application")

    objIE.Visible = True
    Source = "https://secure.conservation.ca.gov/WellSearch/Details?api=01120716&District=&County=&Field=&Operator=&Lease=&APINum=&address=&ActiveWell=false&ActiveOp=false&Location=&sec=&twn=&rge=&bm=&PgStart=0&PgLength=10&SortCol=6&SortDir=asc&Command=Search"
    objIE.navigate Source

    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    Set ele = objIE.document.getElementsByTagName("select")

    For Each opt In ele
        If opt.getAttribute("name") = "tblWellRecords_length" Then
            opt.Focus
            opt.Value = -1
            Exit For
        End If
    Next opt
End Sub

I was looking for the "All" element to click on the production data. it's changing the value of the drop-down if I fix the attribute name as "productionTable_length" but it's not clicking the element. If it is clicked then the total number of shows will be changed from 1-25 to 1-166. Please help me on that.... – Partha 2 hours ago

You did not say about that :) Your question was how to select a specific dropdown element in webpage by excel vba For which I gave you the answer. For what you want, there is a different approach.

Try this

Sub Sample()
    Dim objIE As Object, source As String, script As String

    Set objIE = CreateObject("InternetExplorer.Application")

    objIE.Visible = True
    source = "https://secure.conservation.ca.gov/WellSearch/Details?api=01120716&District=&County=&Field=&Operator=&Lease=&APINum=&address=&ActiveWell=false&ActiveOp=false&Location=&sec=&twn=&rge=&bm=&PgStart=0&PgLength=10&SortCol=6&SortDir=asc&Command=Search"
    objIE.navigate source

    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    script = "$('select[name=productionTable_length]').val('-1'); $('select[name=productionTable_length]').trigger('change');"
    objIE.document.parentWindow.execScript script, "jscript"
End Sub

edited Sep 25, 2018 at 8:18

answered Sep 24, 2018 at 12:45

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Partha Over a year ago

I was looking for the "All" element to click on the production data. it's changing the value of the drop-down if I fix the attribute name as "productionTable_length" but it's not clicking the element. If it is clicked then the total number of shows will be changed from 1-25 to 1-166. Please help me on that....

Siddharth Rout Over a year ago

I have edited the above post. You may have to refresh the page to see it

Partha Over a year ago

Now it's perfectly working :). Thanks a lot. My ultimate objective was to capture the whole table. It was just a part of the process. That's why I was asking for that. Ultimately I have to click on that. Now I can able to extract the whole table. Thanks once again. I want to know more on this script line. where I can find that?

Collectives™ on Stack Overflow

how to select a specific dropdown element in webpage by excel vba

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related