Scraping dynamic web page with VBA based on XMLHTTP object

Question

I have problem with scraping table data from this page [http://www.eex.com/en/market-data/power/derivatives-market/phelix-futures]. I use this code, but do not scrape any data:

Public Sub ScrapTableData()
    Dim sURL As String
    Dim XMLHttpRequest As XMLHTTP
    Dim HTMLDoc As New HTMLDocument
    Dim elc As HTMLHtmlElement
    Dim i As Integer

    sURL = "http://www.eex.com/en/market-data/power/derivatives-market/phelix-futures"

    Set XMLHttpRequest = New MSXML2.XMLHTTP
    XMLHttpRequest.Open "GET", sURL, False
    XMLHttpRequest.responseXML.async = False
    XMLHttpRequest.send

    Do While XMLHttpRequest.Status <> 200
        DoEvents
    Loop
    While XMLHttpRequest.ReadyState <> 4
        DoEvents
    Wend

    HTMLDoc.body.innerHTML = XMLHttpRequest.responseText

    ' Tables
    Dim tbl    As HTMLTable, row    As HTMLTableRow, cell   As HTMLTableCell
    i = 1
    For Each tbl In HTMLDoc.getElementsByTagName("table")
       For Each row In tbl.Rows
           For Each cell In row.Cells
               ActiveSheet.Cells(i, 5) = cell.innerText
               i = i + 1
           Next
       Next
    Next
End Sub

My code does not find HTML table tags.

Also, if I use this part of code, do not list all HTML tags (for example HTML DIV tag) and HTML that describes 6 buttons:

i = 0
Dim elc As HTMLHtmlElement
For Each elc In HTMLDoc.all
    Worksheets("Tables").Range("A1").Offset(i, 0) = elc.tagName
    i = i + 1
Next

6 buttons: Year, Quarter, Month,..., Day

I need to simulate click on them to display (scrape) different tables' data.

Data is added dynamically to the page after load - you cannot use that approach to scrape a page like this, since XMLHTTP only fetches the initial page source: it won't perform any dynamic updates. Try automating IE instead. — Tim Williams
– Tim Williams, Commented Feb 12, 2014 at 17:05
Current link is eex.com/en/market-data/power/futures/phelix-de-futures#!/2018/… — QHarr
– QHarr, Commented Jun 21, 2018 at 17:49

ron · Accepted Answer · 2014-02-12 19:17:08Z

1

I don't think the XMLHTTP approach will work in this case, you need to open IE. The following code will do this. You may need to modify the loop to put data in your worksheet, I didn't tinker with this. At the end, I've also placed some code that will change the tabs. Hope this helps

Sub test()
' open IE, navigate to the website of interest and loop until fully loaded
    Set IE = CreateObject("InternetExplorer.Application")
    my_url = "http://www.eex.com/en/market-data/power/derivatives-market/phelix-futures"

    With IE
        .Visible = True
        .navigate my_url
        .Top = 50
        .Left = 530
        .Height = 400
        .Width = 400

    Do Until Not IE.Busy And IE.readyState = 4
        DoEvents
    Loop

    End With

' Collect data from tables
    Set tbl = IE.document.getElementsByTagName("table")
    For Each itm In tbl
        i = 1
        For Each itm2 In itm.Rows
            For Each cell In itm2.Cells
                ActiveSheet.Cells(i, 5) = cell.innertext
                i = i + 1
            Next
        Next
    Next

' Click on the 6 buttons, substitute "week", "year", etc. for the button you want to click
    Set Results = IE.document.getElementsByTagName("a")
    For Each itm In Results
        If InStr(1, itm.innertext, "month", vbTextCompare) > 0 Then
            itm.Click

            Do Until Not IE.Busy And IE.readyState = 4
                DoEvents
            Loop
            Exit For
        End If
    Next

' Do whatever is next

End Sub

answered Feb 12, 2014 at 19:17

ron

1,3783 gold badges18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Figlio Over a year ago

Thank you. Yes, I know that. I made also with IE and work, but I prefer XMLHTTP regard to speed ... Many people prefer also JSON etc, but i'm not familiar with JS etc. Thanks again.

Collectives™ on Stack Overflow

Scraping dynamic web page with VBA based on XMLHTTP object

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related