CODEWITHSUNDEEP
excelvbaweb-scrapingdropdownqueryselector
ab1225
ab1225

Reputation: 35

Dropdown Menus with VBA

I need to select a specific option from a dropdown menu using VBA. How can I do this?

https://clinicaltrials.gov/ct2/results?cond=&term=Medpace&cntry=&state=&city=&dist= link to webpage we are trying to pull from

IE.document.getElementsByName("down_count").click code that I tried

Full Module:
Private Sub Workbook_Open()
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.application")
    With IE
        .Visible = True
        .Navigate ("https://clinicaltrials.gov/ct2/results?cond=&term=Medpace&cntry=&state=&city=&dist=")
        While .Busy Or .readyState <> 4: DoEvents: Wend

    End With

    With IE.document
    IE.Refresh
        Set div = IE.document.getElementById("save-list-link")
        div.FireEvent "onclick"
    ' Application.SendKeys "{TAB}", True
    ' Application.SendKeys "{TAB}", True
    ' Application.SendKeys "{SPACE}", True
    ' Application.SendKeys "{DOWN}", True
    ' Application.SendKeys "{ENTER}", True
    ' Application.SendKeys "{TAB}", True
    ' Application.SendKeys "{TAB}", True
    ' Application.SendKeys "{SPACE}", True
    ' Application.SendKeys "{DOWN}", True
    ' Application.SendKeys "{DOWN}", True
    ' Application.SendKeys "{DOWN}", True
    ' Application.SendKeys "{ENTER}", True
    IE.document.getElementsByName("down_count").click
    ' For Each elt In IE.document.getElementById("number-of-studies")
        ' If InStr(elt.innerText, "Found") > 0 Then elt.click: Exit For
    ' Next elt

    Set div4 = IE.document.getElementById("submit-download-list")
    div4.click
    End With
End Sub

Would like to have Number of Studies go to All found (this number changes) and have select file format be CSV

Upvotes: 2

Views: 179

Answers (1)

QHarr
QHarr

Reputation: 84465

The following shows you how to download

Option Explicit

'VBE > Tools > References: Microsoft Internet Controls
Public Sub GetData()
    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .Navigate2 "https://clinicaltrials.gov/ct2/results?cond=&term=Medpace&cntry=&state=&city=&dist="

        While .Busy Or .readyState < 4: DoEvents: Wend

        With .document
            .querySelector("#save-list-link").Click
            .querySelector("#number-of-studies option:last-child").Selected = True 'select last option from that dropdown
            '.querySelector("#number-of-studies").selectedIndex = 1  ''selects 2nd option
            .querySelector("[value=csv]").Selected = True 'select csv with attribute = value css selector

            .querySelector("#submit-download-list").Click
        End With
        Application.Wait Now + TimeSerial(0, 0, 10)
        Application.SendKeys "%+O", True
        Application.Wait Now + TimeSerial(0, 0, 10)
        .Quit
    End With
End Sub

Upvotes: 1

Related Questions