Excel-VBA: buttons and drop-down lists before scraping data on .aspx web pages

I'd like to use VBA to get a table from a webpage, but I cannot access it directly because I need to switch from a tab to another. The problem relies on the .aspx end of the web page (URL doesn't evolve accordingly).

URL: http://www.morningstar.fr/fr/fundquickrank/default.aspx

Default page after clicking URL

Moving to "Long Terme" Tab - URL won't change

I've managed to build the code for exporting the table to an Excel worksheet, but lack the "navigation" part.

Here is the HTML code that regards the button clicking the "Long Terme Tab":

HTML Code Long Terme Tab

My starting point is here (ProcessHTMLPage does the table scraping):

Sub Browse_Morningstar()

Dim XMLPage As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument


XMLPage.Open "GET", "http://www.morningstar.fr/fr/fundquickrank/default.aspx", False
XMLPage.send

HTMLDoc.body.innerHTML = XMLPage.responseText


ProcessHTMLPage HTMLDoc

End Sub

I suppose the request must be updated somehow. Sorry if I am not being accurate enough, but I'm kind of new at all this.

Thanks a lot guys!

2 answers

  • answered 2018-01-14 03:33 Foxfire And Burns And Burns

    Sub Get_Info()
    
    Dim Elems, e As Variant
    
    Const READYSTATE_COMPLETE& = 4&
    Dim ie As Object
    Set ie = Nothing
    DoEvents
    
    Set ie = CreateObject("InternetExplorer.Application")
    DoEvents
    
    With ie
        .Visible = True
        .Navigate "http://www.morningstar.fr/fr/fundquickrank/default.aspx"
        While Not .readyState = READYSTATE_COMPLETE
            DoEvents
        Wend
    End With
    
    With ie.Document
        Set Elems = .getElementsByTagName("span")
        DoEvents
        For Each e In Elems
            If e.getAttribute("onclick") = "__doPostBack('TabAction', '2')" Then
                e.Click
                'try to insert your table export code here
                Exit For
            End If
        Next e
    
    End With
    
    Set Elems = Nothing
    Set e = Nothing
    
    'ie.Quit Quit Internet Explorer once the exporting is done
    Set ie = Nothing
    
    MsgBox "Done"
    End Sub
    

    The code above navigates directly to the tab you want. Try to combine it with your code to export the table and maybe it will work. ie.Visible=True just to make sure you navigate to the right URL, but make it false once you see it works. Hope it helps!

  • answered 2018-01-14 12:43 Shahin

    If you wish to get the tabular data from that target page using IE then this is one such way to achieve that.

    Sub Fetch_Data()
        Dim IE As New InternetExplorer, html As HTMLDocument
        Dim posts As Object, post As Object, elem As Object, trow As Object
    
        With IE
            .Visible = True
            .navigate "http://www.morningstar.fr/fr/fundquickrank/default.aspx"
            While .readyState < 4: DoEvents: Wend
            Set html = .document
        End With
    
        For Each post In html.getElementsByClassName("ms_tab_inactivetext")
            If InStr(post.innerText, "Long terme") > 0 Then post.ParentNode.Click: Exit For
        Next post
    
        Do While IE.Busy = True Or IE.readyState <> 4: DoEvents: Loop
    
        Set posts = html.getElementById("ctl00_ctl00_MainContent_Layout_1MainContent_gridResult")
    
        For Each elem In posts.Rows
            For Each trow In elem.Cells
                c = c + 1: Cells(r + 1, c) = trow.innerText
            Next trow
            c = 0: r = r + 1
        Next elem
        IE.Quit
    End Sub
    

    Reference to add to the library:

    1. Microsoft HTML Object Library
    2. Microsoft Internet Controls