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.

Answer 1

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: