使用其类提取HTML元素值 [英] Extracting HTML elements values using their classes

问题描述

我已经查看了此链接中提供的解决方案

如果能将每个单元格中的价格提取到表格中，我将不胜感激.

 公共子类GetInfo()Const URL As String ="https://www.betfair.com/exchange/plus/football/competition/11997260"昏暗的html作为HTMLDocument，hTable作为HTMLTable，ws作为工作表，headers()标头= Array(国家/地区"，价格")设置ws = ThisWorkbook.Worksheets("Sheet1")设置html =新的HTMLDocument使用CreateObject("MSXML2.XMLHTTP").打开"GET"，URL，False.发送html.body.innerHTML = .responseText结束于设置hTable = html.querySelector("table.coupon-table")将Dim Td作为对象，将Tr作为对象，将r作为对象，将c作为对象r = 1与ws.Cells(1，1).Resize(1，UBound(headers)+1)=标头对于hTable.getElementsByTagName("tr")中的每个Trr = r + 1:c = 1如果r>3然后对于Tr.getElementsByClassName("bet-button-price")中的每个Td.Cells(r-2，c)= IIf(c = 2，'"& Td.innerText，Td.innerText)c = c + 1下一个万一下一个结束于 

结束子

解决方案

我将使用页面用于更新返回json的那些值的同一端点.然后使用json解析器提取所需的值.

我使用

I have looked at the solution provided in this link Extract Table from Webpage in Excel using VBA and it was very helpful. But I need to extract the elements with certain classes in each HTML Table cells(td).

1. The URL is: https://www.betfair.com/exchange/plus/football/competition/11997260

2. The HTML table class is: coupon-table

3. The price to extract is nested inside cells. It is in the Span element using the "bet-button-price" class. That's the data I need extracted into each cell in the Excel sheet.

Here's a screenshot of the table structure:

I would be grateful to receive any help to extract those prices in each cell into the sheet.

Public Sub GetInfo()
Const URL As String = "https://www.betfair.com/exchange/plus/football/competition/11997260"
Dim html As HTMLDocument, hTable As HTMLTable, ws As Worksheet, headers()
headers = Array("Countries", "Prices")
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set html = New HTMLDocument
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", URL, False
    .send
    html.body.innerHTML = .responseText
End With
 Set hTable = html.querySelector("table.coupon-table")
Dim Td As Object, Tr As Object, r As Long, c As Long

r = 1
With ws
    .Cells(1, 1).Resize(1, UBound(headers) + 1) = headers
    For Each Tr In hTable.getElementsByTagName("tr")
        r = r + 1: c = 1
        If r > 3 Then
            For Each Td In Tr.getElementsByClassName("bet-button-price")
                .Cells(r - 2, c) = IIf(c = 2, "'" & Td.innerText, Td.innerText)
                c = c + 1
            Next
        End If
    Next
End With

End Sub

解决方案

I would use the same endpoint the page uses to update those values which returns json. Then use a json parser to extract the values you want.

I use jsonconverter.bas - you add the code from that link to a module called JsonConverter then go VBE > Tools > References > Add a reference to Microsoft Scripting Runtime.

In VBA for json the [] denotes a collection accessed by For Each or index, the {} represents a dictionary you can access by key or For Each over.

I store results in an array and write out in one go to sheet for efficiency. You could write out to Activesheet.Cells(2,1) and then add headers, if wanted, in row 1.

You can view an example response json here.

Option Explicit
Public Sub GetPrices()
    Dim s As String, json As Object, p As String

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.betfair.com/www/sports/exchange/readonly/v1/bymarket?_ak=nzIFcwyWhrlwYMrh&alt=json&currencyCode=GBP&locale=en_GB&marketIds=1.157348157,1.157348529,1.157347785,1.157347909,1.157348405,1.157348653,1.157348281,1.157349025,1.159492425,1.157348777,1.157348033,1.157348901,1.157350197,1.157350445,1.157351280,1.157349949&rollupLimit=10&rollupModel=STAKE&types=MARKET_STATE,MARKET_RATES,MARKET_DESCRIPTION,EVENT,RUNNER_DESCRIPTION,RUNNER_STATE,RUNNER_EXCHANGE_PRICES_BEST,RUNNER_METADATA,MARKET_LICENCE,MARKET_LINE_RANGE_INFO", False
        .send
        s = .responseText
        Set json = JsonConverter.ParseJson(s)
    End With

    Dim runners As Object, runner As Object, results(), r As Long
    Set runners = json("eventTypes")(1)("eventNodes")

    ReDim results(1 To runners.Count, 1 To 7)
    For Each runner In runners
        r = r + 1
        results(r, 1) = runner("event")("eventName")
        results(r, 2) = runner("marketNodes")(1)("runners")(1)("exchange")("availableToBack")(1)("price")
        results(r, 3) = runner("marketNodes")(1)("runners")(1)("exchange")("availableToLay")(1)("price")
        results(r, 4) = runner("marketNodes")(1)("runners")(3)("exchange")("availableToBack")(1)("price")
        results(r, 5) = runner("marketNodes")(1)("runners")(3)("exchange")("availableToLay")(1)("price")
        results(r, 6) = runner("marketNodes")(1)("runners")(2)("exchange")("availableToBack")(1)("price")
        results(r, 7) = runner("marketNodes")(1)("runners")(2)("exchange")("availableToLay")(1)("price")
    Next
    ActiveSheet.Cells(1, 1).Resize(UBound(results, 1), UBound(results, 2)) = results
End Sub

---

Results:

这篇关于使用其类提取HTML元素值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！