CODEWITHSUNDEEP
excelvbadrop-down-menu
Partha
Partha

Reputation: 5

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

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.

Upvotes: 0

Views: 1125

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

enter image description here

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

Upvotes: 1

Related Questions