1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Selecting dropdown box on webpage with VBA

Discussion in 'VBA Macros' started by Kendall, Jul 11, 2018.

  1. Kendall

    Kendall New Member

    Messages:
    3
    Im trying to use VBA to populate a spreadsheet with information about exdividend dates from a website.
    The website has a default of 25 companies listed in the table, with which there is a drop down menu to select 15,25,50 or ALL. ALL is the option I am after. I cant get any .selected=4 to work.
    I have the section of code working to retrieve the table and populate the cells, however currently i am only getting the first 25 companies (not enough, I need ALL of them!). Ive played around and searched in the forums for a solutions but none are working for me.
    This is what i currently have

    Code (vb):
     
    Dim HTMLDoc As New HTMLDocument
    Dim elem As Object
    Dim objIE As InternetExplorer
    Set objIE = New InternetExplorer
    objIE.navigate "https://www.morningstar.com.au/Stocks/UpcomingDividends"
    Do Until objIE.readyState = 4 And Not objIE.Busy
        DoEvents
    Loop
    Application.Wait (Now + TimeValue("0:00:03")) 'wait for java script to load
    HTMLDoc.body.innerHTML = objIE.document.body.innerHTML
    HTMLDoc.getElementTagName("select").selectedIndex = 4 'here is where it goes sour
    HTMLDoc.getElementTagName("select").FireEvent ("onchange") 'error 438 ^
    objIE.Quit
     
    The structure for the html page is below
    Code (vb):

            <div id="pager" class="pager">
                <form>
                    <img src="/Content/scripts/tablesorter/addons/pager/icons/first.png" class="first"/>
                    <img src="/Content/scripts/tablesorter/addons/pager/icons/prev.png" class="prev"/>
                    <input type="text" class="pagedisplay"/>
                    <img src="/Content/scripts/tablesorter/addons/pager/icons/next.png" class="next"/>
                    <img src="/Content/scripts/tablesorter/addons/pager/icons/last.png" class="last"/>
                    <select class="pagesize">
                        <option value="15">15</option>
                        <option selected="selected" value="25">25</option>
                        <option value="50">50</option>
                        <option value="294">ALL</option>
                    </select>
                 </form>
            </div>
     
    The value for the ALL option changes so i have to select the 4th option i cant use .value = "294"
  2. Marc L

    Marc L Excel Ninja

    Messages:
    4,037
    No need any option as all the data is yet in the initial webpage code !
    Just reading it …

    From threads of this forum (like in some other forums !) as a starter :​
    Code (vb):
    Sub Demo1()
             Dim T$
        With CreateObject("WinHttp.WinHttpRequest.5.1")
            .Open "GET", "https://www.morningstar.com.au/Stocks/UpcomingDividends", False
            .setRequestHeader "DNT", "1"
             On Error Resume Next
            .send
         If .Status = 200 Then T = .responseText
             On Error GoTo 0
        End With
        If T > "" Then
            With CreateObject("htmlfile")
                   .body.innerHTML = T
                If .frames.clipboardData.setData("Text", .all("OverviewTable").outerHTML) Then
                    With ActiveSheet
                        .UsedRange.Clear
                        .Paste [A1]
                        .Hyperlinks.Delete
                        .UsedRange.WrapText = False
                        .UsedRange.Columns(2).AutoFit
                    End With
                        .frames.clipboardData.clearData "Text"
                End If
            End With
        End If
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
    Kendall likes this.
  3. Kendall

    Kendall New Member

    Messages:
    3
    wow i have no idea how it works with your code and not anything i tried but thank you so much :D legend!
    Last edited by a moderator: Jul 12, 2018 at 11:27 AM
  4. Marc L

    Marc L Excel Ninja

    Messages:
    4,037

    This demo just loads the webpage code to a Document
    then paste the table to the active sheet via the clipboard,
    the easy way when all data is yet in the original webpage code …​
  5. Kendall

    Kendall New Member

    Messages:
    3
    I can see that, i couldnt access the data before that apparently was there already. either way thank you for your help :D
  6. Marc L

    Marc L Excel Ninja

    Messages:
    4,037

    You can access the data the same way even piloting IE
    just reading the webpage code but as piloting IE is often the slowest …​

Share This Page