VBA HTML Scraping - 来自复杂表的“.innertext" [英] VBA HTML Scraping - '.innertext' from complex table

问题描述

全部，

我创建了以下模块来从以下地址中提取单个值(伦敦房价变化 100 万%):

下面的 VBA 代码是我的抓取尝试.我，也许是错误的，觉得我非常接近捕获价值——但代码不起作用.

有人知道我哪里出错了吗?它不显示错误消息，也不输出任何值.

 子 HousePriceData()将 wb 调暗为工作簿暗淡为工作表将文本调暗为范围Dim ie As Object将 V 变暗为变体将 myValue 调暗为变体设置 ie = CreateObject("INTERNETEXPLORER.APPLICATION")ie.NAVIGATE "https://www.hometrack.com/uk/insight/uk-cities-house-price-index/"ie.Visible = False而 ie.ReadyState <>4做事件文德设置 wb = ActiveWorkbook设置 ws = wb.Sheets("输入")设置 TxtRng = ws.Range("C15")设置 myValue = ie.document.getElementById("cities-index-table").getElementsByTagName("tr")(7).g‌ etElementsByTagName("td")(5)TxtRng = myValue.innerText结束子

解决方案

尝试使用 XHR 和原始解析来代替笨拙的 IE:

子测试()将 strUrl 变暗为字符串将 strTmp 调暗为字符串将 arrTmp 调暗为变体strUrl = "https://www.hometrack.com/uk/insight/uk-cities-house-price-index/"使用 CreateObject("MSXML2.XMLHttp").打开GET"，strUrl，假.发送 ""strTmp = .ResponseText结束于arrTmp = Split(strTmp, ">伦敦</a></td>", 2)strTmp = arrTmp(1)arrTmp = Split(strTmp, "<td>", 7)strTmp = arrTmp(6)arrTmp = 拆分(strTmp, "</td>", 2)strTmp = arrTmp(0)ThisWorkbook.Sheets("Input").Range("C15").Value = strTmp结束子

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

解决方案

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

这篇关于VBA HTML Scraping - 来自复杂表的“.innertext"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！