VBA HTML Scraping - '.innertext' from complex table

Question

All,

I've created the following Module to scrape a single value (1m % change in London house prices) from the below address:

https://www.hometrack.com/uk/insight/uk-cities-house-price-index/

The specific value is nested within the following code:

The below VBA code is my attempt at scraping. I, perhaps wrongly, feel that I am very close to capturing the value - but the code will not work.

Does anyone know where I am going wrong here? It doesn't show an error message but also doesn't output any values.

 Sub HousePriceData()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim TxtRng As Range
        Dim ie As Object
        Dim V As Variant
        Dim myValue As Variant

        Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")
        ie.NAVIGATE "https://www.hometrack.com/uk/insight/uk-cities-house-price-index/"
        ie.Visible = False

        While ie.ReadyState <> 4
            DoEvents
        Wend

        Set wb = ActiveWorkbook 
        Set ws = wb.Sheets("Input") 
        Set TxtRng = ws.Range("C15") 

        Set myValue = ie.document.getElementById("cities-index-table").getElementsByTagName("tr")(7).g‌etElementsByTagName("td")(5) 

        TxtRng = myValue.innerText 
        End Sub

"the code will not work" - can you elaborate on that? What does or doesn't happen? Any error messages? — Tim Williams
– Tim Williams, Commented Mar 3, 2016 at 20:22
Hello Tim, it returns 'Run-time error '13': Type mismatch'. Thanks. — Pineapple
– Pineapple, Commented Mar 3, 2016 at 20:24
getElementsByTagName("tbody")(12) - a table should only have one body element. When you have a long line of code like that with multiple places where it could error, it's best to start ebugging by breaking it down into individual lines each of which access only a single element or collection. Did you mean getElementsByTagName("tr")(12) ? — Tim Williams
– Tim Williams, Commented Mar 3, 2016 at 20:25
The below shows no errors but doesn't scrape the data. I reference code tbody code as there are tr tags in codethead code and I thought it appropriate to differentiate between the two? code Set wb = ActiveWorkbook Set ws = wb.Sheets("Input") Set TxtRng = ws.Range("C15") Set myValue = ie.document.getElementById("cities-index-table").getElementsByTagName("tr")(7).getElementsByTagName("td")(5) TxtRng = myValue.innerText End Sub code — Pineapple
– Pineapple, Commented Mar 3, 2016 at 20:52
update your question - no-one can read code in comments. — Tim Williams
– Tim Williams, Commented Mar 3, 2016 at 21:06

omegastripes · Accepted Answer · 2016-03-03 21:36:57Z

3

Try to use XHR and primitive parsing instead of awkward IE:

Sub Test()

    Dim strUrl As String
    Dim strTmp As String
    Dim arrTmp As Variant

    strUrl = "https://www.hometrack.com/uk/insight/uk-cities-house-price-index/"
    With CreateObject("MSXML2.XMLHttp")
        .Open "GET", strUrl, False
        .Send ""
        strTmp = .ResponseText
    End With
    arrTmp = Split(strTmp, ">London</a></td>", 2)
    strTmp = arrTmp(1)
    arrTmp = Split(strTmp, "<td>", 7)
    strTmp = arrTmp(6)
    arrTmp = Split(strTmp, "</td>", 2)
    strTmp = arrTmp(0)

    ThisWorkbook.Sheets("Input").Range("C15").Value = strTmp

End Sub

edited Mar 3, 2016 at 21:36

answered Mar 3, 2016 at 20:53

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

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

1 Comment

Pineapple Over a year ago

Thanks @omegastripes. This outputs the value 1.2% vs 0.6% (last month London data)?

eros · Accepted Answer · 2017-03-01 10:36:13Z

0

try use this

Dim Engmt As String

Engmt = "ERRORHERE"
On Error Resume Next
Engmt = Trim(ie.document.getElementById("cities-index-   table").getElementsByTagName("tr")(12).g‌etElementsByTagName("td")(4).innerText)
On Error GoTo 0
If Engmt = "ERRORHERE" Then
TxtRng.Value = "ERROR"
Else
TxtRng.Value = Engmt
End If

answered Mar 1, 2017 at 10:36

eros

292 silver badges6 bronze badges

Collectives™ on Stack Overflow

VBA HTML Scraping - '.innertext' from complex table

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related