Html - Extract values from webpage table using VBA

OsvaldoAssun231227o
April 22, 2024
236 views
0 votes
2 Answers

I need to extract some values from the table in the following website: https://www.anbima.com.br/informacoes/indicadores/.

I tried to use the solutions given in Extract table from webpage using VBA but neither worked for me.

I am rather new to VBA and I’m not familiar with java. Is there a way to extract only the values 6.869,14 and 0,35. Both are in the last column.

Here is one solution that I was able to find, however it does not seem to be the most efficient or elegant. But it works.

@W_O_L_F solution seems better, but I get an error on objHTTP.send.

Sub scrape_quotes()
   Dim browser As InternetExplorer
   Dim page As HTMLDocument
   
   Set browser = New InternetExplorer
   browser.Visible = False
   browser.navigate ("https://www.anbima.com.br/informacoes/indicadores/")
   Do While browser.Busy: Loop
   Set page = browser.document
'-----
    Dim spanElement As Object, buttonElement As Object
 
    Set spanElement = page.getElementsByClassName("linhadados")

    aux = spanElement.Length
    
    For i = 0 To spanElement.Length - 1
        If VBA.Left(spanElement.Item(i).innerText, 5) = "IPCA " Then aux_indice = CDbl(VBA.Right(spanElement.Item(i).innerText, 8))
        If VBA.Left(spanElement.Item(i).innerText, 5) = "IPCA1" Then aux_proj = CDbl(VBA.Right(spanElement.Item(i).innerText, 4))
        
    Next i
    browser.Quit
End Sub

Answers

Maybe this can get you started:

Sub ScrapeWebsite()
'Declare variables
Dim objHTTP As New WinHttp.WinHttpRequest
Dim htmlDoc As New HTMLDocument
Dim htmlElement As IHTMLElement

Dim url As String
Dim readNextCell As Boolean
Dim IPCA As Boolean
Dim rowSpan As Integer

readNextCell = False
IPCA = False
rowSpan = 0
'Set the URL to be scraped
url = "https://www.anbima.com.br/informacoes/indicadores/"
'Make a request to the URL
objHTTP.Open "GET", url, False
objHTTP.send
'Parse the HTML response
htmlDoc.body.innerHTML = objHTTP.responseText
'Loop through the HTML elements and extract data


For Each aRow In htmlDoc.getElementsByClassName("linhadados") '.getElementsByTagName("tr")
'Do something with the data, e.g. print it to the Immediate window
    For Each acell In aRow.getElementsByTagName("td")
    If readNextCell Then
        MsgBox acell.innerText
        readNextCell = False
    End If
    If IPCA And (acell.innerText = "Número Índice" Or acell.innerText = "Projeção (abr/24)") Then
        readNextCell = True
        IPCA = False
    End If
    If (acell.innerText = "IPCA (mar/24)6" Or acell.innerText = "IPCA1") Then
        IPCA = True
        rowSpan = acell.rowSpan
    End If
    Next acell
Next aRow
End Sub

This should do it:

Sub GrabInfo()
    Const Url$ = "https://www.anbima.com.br/informacoes/indicadores/"
    Dim Html As HTMLDocument, WS As Worksheet
    Dim oHttp As Object, R&, C&, I&, N&

    Set Html = New HTMLDocument
    Set WS = ThisWorkbook.Worksheets("Sheet1")
    Set oHttp = CreateObject("MSXML2.serverXMLHTTP")
    
    With oHttp
        .Open "GET", Url, True
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36"
        .send
        While .readyState < 4: DoEvents: Wend
        Html.body.innerHTML = .responseText
        R = 1
        With Html.querySelectorAll("table > tbody > tr.linhadados")
            For I = 1 To .Length - 1
                With .Item(I).querySelectorAll("td")
                    For N = 0 To .Length - 1
                        C = C + 1: Cells(R, C) = .Item(N).innerText
                    Next N
                End With
                R = R + 1: C = 0
            Next I
        End With
    End With
End Sub

Please signup or login to give your own answer.

Click here to cancel reply.

Html – Extract values from webpage table using VBA

Answers