Follow up to Extract values from webpage table using VBA

Earlier today I asked the following question Extract values from webpage table using VBA. All answers gave me a way to access the data inside the HTML Object.

I want to do the same thing for another website, however none of the answers seem to work. The website is https://www.bcb.gov.br/. The first box

#home > div > div:nth-child(1) > div.componente.cotacao > div

Contains the information I want:

#home > div > div:nth-child(1) > div.componente.cotacao > div > cotacao > table:nth-child(1) > tbody > tr:nth-child(1) > td:nth-child(2) > span

Outer HTML <span _ngcontent-epj-c113="">5,2037</span>

Full Xpath:

/html/body/app-root/app-root/div/div/main/dynamic-comp/div/div/div/div[1]/div[1]/div/cotacao/table[1]/tbody/tr[1]/td[2]/span

I also tried the solution given in Extracting div class information from website with VBA looping through class name or tag name.

Here is a sample code extracting the website and trying to loop through tag name

Sub WebData()
    Dim http As New XMLHTTP60, html As New HTMLDocument
    Dim source As Object

    With http
        .Open "GET", "https://www.bcb.gov.br", False
        .send
        html.body.innerHTML = .responseText
    End With
    
    
    For i = 1 To 1000
        Cells(i, 1) = html.getElementsByTagName("td")(i).innerText
    Next i
End Sub

Solution

The script is unable to see the data when you make an HTTP request using the url in your post because the desired data is loaded through an external API. Try this way instead to fetch the value.

Sub GrabCertainValue()
    Const Url$ = "https://www.bcb.gov.br/api/servico/sitebcb/indicadorCambio"
    Dim oHttp As Object, WS As Worksheet
    Dim Rxp As Object, sResp$, jVal As Object
    Dim valorCompra$

    Set WS = ThisWorkbook.Worksheets("Sheet1")
    Set oHttp = CreateObject("MSXML2.XMLHTTP")
    Set Rxp = CreateObject("VBScript.RegExp")
    
    With oHttp
        .Open "GET", Url, False
        .setRequestHeader "Accept", "application/json, text/plain, */*"
        .send
        sResp = .responseText
    End With
    
    valorCompra = vbNullString
    
    With Rxp
        .Global = True
        .MultiLine = True
        .Pattern = "valorCompra"":(.*?),"
        
        Set jVal = .Execute(sResp)
        If jVal.Count > 0 Then
            valorCompra = jVal(0).submatches(0)
        End If
    End With
    
    WS.Cells(1, 1) = valorCompra
End Sub