• 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...

  • 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

Kendall

New Member
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:
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:
        <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"
 

Marc L

Excel Ninja
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:
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

New Member
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:

Marc L

Excel Ninja

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 …​
 

Kendall

New Member
I can see that, i couldnt access the data before that apparently was there already. either way thank you for your help :D
 

Marc L

Excel Ninja

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