VBA to scrape Web scroll-table into excel

[bezuchoff]

Hello,

I have been trying putting together a code to get scroll table data from web to excel from an internal url which does not open in IE. The code I have, however, only takes table (column) headings, not the values from table itself. The table on web updates every once a while on would be great get it into excel. I have table ID, table data (cells) contain links as well as dynamic values.

Any help would be highly appreciated. Thanks a lot.


Sub ScpareWebTable()

Dim xml As Object
Dim html As MSHTML.IHTMLDocument
Dim objTable As Object
Dim result As String
Dim trow As Object
Dim tcel As Object


Set xml = CreateObject("MSXML2.XMLHTTP.6.0")
With xml
.Open "GET", "https://company_xx.internal.co.uk/station/linehaul", False
.send
End With


result = xml.responseText
Set html = CreateObject("htmlfile")


html.body.innerHTML = result


Set objTable = html.getElementsByTagName("table")


Set trow = html.getElementsByTagName("tr")
Set tcel = html.getElementsByTagName("td")

For Each tcel In trow
Debug.Print tcel.innerText
Next tcel

End Sub

 

[bezuchoff]

One important comment - when I call the table by its ID Set objTable = html.getElementsById("linehaulDataTable") I am getting Run-time error 438 (Object doesn't support this property or method).