Problem to parse HTML in VBA from this url

Question

I would like to parse HTML and get the data from this website:

http://www2.bmf.com.br/pages/portal/bmfbovespa/boletim1/Ajustes1.asp?txtData22/12/2020

using VBA.

A already have this code, but is not working as expected:

site = "http://www2.bmf.com.br/pages/portal/bmfbovespa/boletim1/Ajustes1.asp?txtData22/12/2020"
Dim html As New MSHTML.HTMLDocument, http As Object

Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", site, False
http.send
  
html.body.innerHTML = http.responseText

Set lines = html.getElementsByTagName("tr")

Because both 'lines' and html.body.innerHTML are empty.

score 1 · Accepted Answer · answered Dec 25 '20 at 18:10

This simple recorded macro will read in the full dataset to a table for you to search and filter as you like:

Sub MacroBr()
    
    ActiveWorkbook.Queries.Add Name:="Ajustes do Pregão", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Kilde = Web.Page(Web.Contents(""http://www2.bmf.com.br/pages/portal/bmfbovespa/boletim1/Ajustes1.asp?txtData22/12/2020""))," & Chr(13) & "" & Chr(10) & "    Data0 = Kilde{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Hævede overskrifter"" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Ændret type"" = Table.TransformColumnTypes(#""Hævede overskrifter"",{{""Mercadoria"", type text}, {""Vct"", typ" & _
        "e text}, {""Preço de Ajuste Anterior"", type number}, {""Preço de Ajuste Atual"", type number}, {""Variação"", type number}, {""Valor do Ajuste por Contrato (R$)"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Ændret type"""
    ActiveWorkbook.Worksheets.Add
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Ajustes do Pregão"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Ajustes do Pregão]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Ajustes_do_Pregão"
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

Example:

How did you record the Macro for this operation ? Is there a website or tutorial that to point me to ? — Juliano Negri, Jan 05 '21 at 13:48
Settings, Adjust ribbon, Mark (at right) Developer, OK. Then menu Developer, Record macro. Then run steps from menu Data, From Internet to attach the HTML page. — Gustav, Jan 05 '21 at 18:08

score 0 · Answer 2 · answered Dec 25 '20 at 17:08

I was able to retrieve data from the page provided using Internet Explorer object:

Sub GetDataUsingIE()
    
    Dim URI As String
    URI = "http://www2.bmf.com.br/pages/portal/bmfbovespa/boletim1/Ajustes1.asp?txtData22/12/2020"
    
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    
    With IE
        .Navigate URI
        .Visible = True
    End With
    
    Do While IE.Busy And IE.readyState <> 4: DoEvents: Loop
    
    Dim Lines As Object
    Set Lines = IE.Document.GetElementsByTagName("tr")
    
    'actions with Lines Object
    
    IE.Quit
    Set IE = Nothing

End Sub

P.S. More information on how to use Internet Explorer for web scraping under the following link: Scraping data from website using vba

Thank you for the solution. I usually have compatibility problems using the InternetExplorer module in excel, given that I use it across a wide range of computers — Juliano Negri, Jan 05 '21 at 13:49

Problem to parse HTML in VBA from this url

2 Answers2