XML Http request into Excel

[jamescooper]

So for this website: https://www.tesco.com/groceries/en-GB/products/268279288
The following code pulls the Price/price per unit etc.

Private Function getprices(ByVal URL As String) As Variant
Dim source As Object
Dim http As New XMLHTTP60, html As New HTMLDocument
Dim ret(1 To 5) As String
Dim elem As Object


    With http
        .Open "GET", URL, False
        .send
        html.body.innerHTML = .responseText
    End With


    Set elem = html.querySelector(".price-details--wrapper .value")


    If Not elem Is Nothing Then
        ret(1) = elem.innerText
    Else
        ret(1) = "N/A"
    End If


    Set elem = html.querySelector(".price-per-quantity-weight .value")


    If Not elem Is Nothing Then
        ret(2) = elem.innerText
    Else
        ret(2) = "N/A"
    End If


    Set elem = html.querySelector(".price-per-quantity-weight .weight")


    If Not elem Is Nothing Then
        ret(3) = elem.innerText
    Else
        ret(3) = "N/A"
    End If
    
    Set elem = html.querySelector(".promotions-wrapper.hidden-small.hidden-medium-small-only .offer-text")
       
       If Not elem Is Nothing Then
        ret(4) = elem.innerText
    Else
        ret(4) = "No"
    End If
   
    Set elem = html.querySelector(".promotions-wrapper.hidden-small.hidden-medium-small-only .dates")


       If Not elem Is Nothing Then
        ret(5) = elem.innerText
    Else
        ret(5) = "None"
    End If


    getprices = ret
  
End Function

Now trying to adapt to pull the price (78p) on this website and cannot seem to adapt it to suit when inspecting the element. Any ideas? Any literature on this would be great too thanks.

https://www.sainsburys.co.uk/shop/gb/groceries/bananas-grapes/sainsburys-loose-fairtrade-bananas

Private Function getprices(ByVal URL As String) As Variant


    Dim source As Object
    Dim http As New XMLHTTP60, html As New HTMLDocument
    Dim ret(1 To 1) As String
    
    With http
        .Open "GET", URL, False
        .send
        html.body.innerHTML = .responseText
    End With
    
    ret(1) = html.querySelector(".pricing .priceperunit").innerText
 
End Function

 

[lrobbo314]

Trying to use the query selector won't work because that element has a different value for each page. I referenced a parent element. This works testing with grapes and bananas.

Sub Main()
Dim bananas As String: bananas = "https://www.sainsburys.co.uk/shop/gb/groceries/bananas-grapes/sainsburys-loose-fairtrade-bananas"
Dim grapes As String: grapes = "https://www.sainsburys.co.uk/shop/gb/groceries/bananas-grapes/sainsburys-red-seedless-grapes-500g"

Debug.Print getprices(bananas)
Debug.Print getprices(grapes)
End Sub

Private Function getprices(ByVal URL As String) As Variant

Dim source As Object
Dim http As New XMLHTTP60, html As New HTMLDocument
Dim ret(1 To 1) As String

With http
    .Open "GET", URL, False
    .send
    html.body.innerHTML = .responseText
End With

ret(1) = html.querySelector("#content > div.section.productContent > div.pdp").innerText
sp = Split(ret(1), Chr(32))
For i = 0 To UBound(sp) - 1
    If InStr(sp(i), "/") > 0 Then getprices = sp(i): Exit Function
Next i
 
End Function

 

[Norie]

This worked for me.

Private Function getprices(ByVal URL As String) As Variant


    Dim source As Object
    Dim http As New XMLHTTP60, html As New HTMLDocument
    Dim ret(1 To 1) As String
    
    With http
        .Open "GET", URL, False
        .send
        html.body.innerHTML = .responseText
    End With
    
    ret(1) = html.querySelector(".pricePerUnit").innerText
 
End Function

 

[jamescooper]

Thanks both, Norie - that is great and I am learning a lot from you.

Do you know of any literature on this?