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 HTMLDoc As New MSHTML.HTMLDocument

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

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-13 17:46 Foxfire And Burns And Burns

    Sub Get_Info()
    Dim Elems, e As Variant
    Dim ie As Object
    Set ie = Nothing
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .Navigate "http://www.morningstar.fr/fr/fundquickrank/default.aspx"
        While Not .readyState = READYSTATE_COMPLETE
    End With
    With ie.Document
        Set Elems = .getElementsByTagName("span")
        For Each e In Elems
            If e.getAttribute("onclick") = "__doPostBack('TabAction', '2')" Then
                '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-13 17:46 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
    End Sub

    Reference to add to the library:

    1. Microsoft HTML Object Library
    2. Microsoft Internet Controls