Excel VBA works versus Access VBA doesn't

Question

This code works perfectly in Excel. It just finds the current price of a coin. However in Access this line

 Set container1 = html.querySelector("#productPrice-product-template span:last-child")

gives error -2147024809 Invalid argument.

Function fncScrapeGOLDvalue() As Integer
    Dim html As HTMLDocument
    Set html = New HTMLDocument
    Dim returnValue As Integer

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.allgoldcoins.co.uk/products/2013-queen-elizabeth-ii-60th-anniversary-of-the-coronation-5-gold-proof-coin?variant=41603767205941", False
        .send
        html.body.innerHTML = .responseText
    End With

    Dim container1 As Object
    Set container1 = html.querySelector("#productPrice-product-template span:last-child")
    
    fncScrapeGOLDvalue = CInt(container1.innerText)
End Function

if I modify that line to

Set container1 = html.querySelector("#productPrice-product-template ")

It works!! but it's not exactly what I want

Answer 1

Try this:

dim spans as ihtmldomchildrencollection
set spans= html.queryselectorall("#productprice-product-template span")
fncScrapeGOLDvalue = spans(spans.length - 1).innertext

Update:

I checked the above code as following and it works:

Const url = "https://www.allgoldcoins.co.uk/products/2013-queen-elizabeth-ii-60th-anniversary-of-the-coronation-5-gold-proof-coin?variant=41603767205941"

Dim html As MSHTML.HTMLDocument
Set html = New MSHTML.HTMLDocument
   
Dim XMLdoc As New MSXML2.XMLHTTP60
XMLdoc.Open "GET", url, False
XMLdoc.send
html.body.innerHTML = XMLdoc.responseText

Dim spans As IHTMLDOMChildrenCollection
Set spans = html.querySelectorAll("#productPrice-product-template span")
Debug.Print (spans(spans.length - 1).innerText)

Set html = Nothing

But there is something to be considered: There are actually 2 spans; first has a aria-hidden=true which the QuerySelectorAll cannot detect (don't know why). The second span has a class="visually-hidden" and this is the one in collection.

I suggest be more specific with CSS selector; Use this to select the second span:

Dim span As HTMLSpanElement
Set span = html.querySelector("#productPrice-product-template .visually-hidden")
Debug.Print (span.innerText)