Extracting HTML elements values using their classes

Question

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).

The URL is: https://www.betfair.com/exchange/plus/football/competition/11997260
The HTML table class is: coupon-table
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

It would be really helpful if you posted the code you have tried and specify the exact problems that you're having with it. — Stavros Jon, Jun 07 '19 at 10:24
That's the code I'm trying to make work. Please check the update — Smith O., Jun 07 '19 at 10:58

score 0 · Answer 1 · answered Jun 07 '19 at 09:21

0

have you tried getElementByClassName("yourclassname")?

answered Jun 07 '19 at 09:21

Eloísa López

11
1

No I haven't. Can you please provide a working example? – Smith O. Jun 07 '19 at 09:33
sure. go to the next link to see how to use it and even try it online with examples. [link]https://www.w3schools.com/jsref/met_document_getelementsbyclassname.asp – Eloísa López Jun 07 '19 at 09:43
Couldn't get it to work. Can you edit my code here pls? – Smith O. Jun 07 '19 at 09:47

score 0 · Accepted Answer · answered Jun 07 '19 at 11:21

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:

Extracting HTML elements values using their classes

2 Answers2

Linked