VBA to extract value from span class

user_10598570 0
Viewed 3593

I am using vba to extract the value from HTML using the .getElementsByClassName but not having much luck at the moment.

I'm trying to extract the "20+" from the following HTML 

<p class="delivery-stock">
    <span class="delivery-stock-value">20+</span> 
    <span class="delivery-available f-bold f4">available for delivery:</span>
</p>
<p>

The VBA I've used so far is 

sh01.Cells(r, 5) = HTML.getElementsByClassName("delivery-stock-value")(0).innertext
sh01.Cells(r, 5) = HTML.getElementsByClassName("delivery-stock-value")(0).value

and I've done the same using the delivery-stock class too.

delivery-stock-value & delivery-stock occurs only once in the HTML.

Thanks in advance for your help.

web-scraping vba excel html
2 Answers

Here's a small example that should return the text to Cell A1 in Sheet1. You want the outerText property. This is just for demo purposes, if you want this to be faster and more maintainable, use a web request.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub GetTheText()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    Dim text As String

    With CreateObject("internetexplorer.application")
        .Navigate "https://www.toolstation.com/2-column-radiator/p39711"
         Do While .Busy And .readyState <> 4:DoEvents:Loop
         Sleep 1000 ' wait a little bit too
         text = .document.getElementsByClassName("delivery-stock-value")(0).outerText
        .Quit
    End With

    ws.Cells(1, 1).Value = text
End Sub
user_48398270

Use an API if available.In this case there is an API you pass the stock code to. This is more efficient and reliable.

With JSONParser (recommended):

If you want the security of a JSON parser (e.g. jsonconverter.bas) then as follows. Once you add the .bas to your project go to vbe > tools > references and add a reference to Microsoft Scripting Runtime

Public Sub GetStock()
    Dim json As Object
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.toolstation.com/api/products/39711/delivery?site=WW", False
        .send
        Set json = JsonConverter.ParseJson(.responseText)
    End With
    Debug.Print json("data")("channels")("delivery")("stock")
End Sub

Without JSON parser (not recommended):

Public Sub GetStock()
    Dim sResponse As String
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.toolstation.com/api/products/39711/delivery?site=WW", False
        .send
        sResponse = .responseText
    End With
    Debug.Print Split(Split(sResponse, "stock" & Chr$(34) & ":")(1), ",")(0)
End Sub
user_62412350
Related