Help with CreateObject("msxml2.xmlhttp") macro

[surfersparadise]

Hello,

I'm trying to extract a table Additional Info from the website that is in the code, but failing so far. There is mistake with wrong class name or other variable. Could anyone suggest how to make it work?

Sub AdditionalInfo()    
    Dim oDom As Object: Set oDom = CreateObject("htmlFile")
    Dim x As Long, y As Long
    Dim oRow As Object, oCell As Object
    Dim data, aTable As Object
    
    y = 1: x = 1
    
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "http://uk.soccerway.com/matches/2014/02/23/england/premier-league/liverpool-fc/swansea-city-afc/1483721/?ICID=HP_MS_01_01", False
        .send
        oDom.body.innerhtml = .responsetext
    End With
    
    For Each aTable In oDom.getelementsbytagname("table")
        If Trim(LCase(aTable.classname)) = "Additional info" Then
            With aTable
                ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length)
                For Each oRow In .Rows
                    For Each oCell In oRow.Cells
                        data(x, y) = oCell.innertext
                        y = y + 1
                    Next oCell
                    y = 1
                    x = x + 1
                Next oRow
            End With
            Exit For
        End If
    Next aTable
    


    With Sheets(2).Cells(1, 1)
        .CurrentRegion.ClearContents
        If IsArray(data) Then
            .Resize(UBound(data), UBound(data, 2)).Value = data
        Else: MsgBox "Table not found"
        End If
    End With


End Sub

 

[VBA Geek]

Hi, try

Sub AAA()
Dim mXML As MSXML2.XMLHTTP: Set mXML = New MSXML2.XMLHTTP
Dim O As MSHTML.HTMLDocument: Set O = New HTMLDocument
Dim Coll As HTMLDivElement
Dim p
Dim k As Integer
With mXML
    .Open "GET", "http://uk.soccerway.com/matches/2014/02/23/england/premier-league/liverpool-fc/swansea-city-afc/1483721/?ICID=HP_MS_01_01", False
    .send
    O.body.innerHTML = .responseText
End With
Set Coll = O.getElementById("page_match_1_block_match_additional_info_6")
For k = 0 To Coll.getElementsByTagName("DT").Length - 1
    Range("A" & k + 1) = Coll.getElementsByTagName("DT")(k).innerText
Next
For k = 0 To Coll.getElementsByTagName("DD").Length - 1
    p = Split(Coll.getElementsByTagName("DD")(k).innerText, vbLf)
    Range("B" & k + 1).Resize(1, UBound(p) + 1).Value = p
Next
End Sub

 

[surfersparadise]

Hi, thank you for your reply.

It returns error at:

Dim mXML As MSXML2.XMLHTTP:

User-defined type not defined

 

[VBA Geek]

hi, because you need to create early binding, in your VBE editor go to TOOLS then REFERENCES and tick Microsoft XML, v6.0 and also tick Microsoft HTML Object Library then press ok, if you dont have version 6.0 for Microsoft XML any previous version should do

 

[surfersparadise]

Works superbly. Thank you very much!

Just one more question... If I wanted to extract any other table, info from that site, do I just change elementid or is there something else to change?

Thank you

 

[surfersparadise]

Also, what do I need to modify to change destination of the table? E.g. to put in the sheet 3, D4.

Thank you again

 

[VBA Geek]

It depends from the table as it would have a different name and maybe different tags..


You can easily change the destination by changing this part

<code><code>For k = 0 To Coll.getElementsByTagName("DT").Length - 1
Range("A" & k + 1) = Coll.getElementsByTagName("DT")(k).innerText
Next
For k = 0 To Coll.getElementsByTagName("DD").Length - 1
p = Split(Coll.getElementsByTagName("DD")(k).innerText, vbLf)
Range("B" & k + 1).Resize(1, UBound(p) + 1).Value = p
Next</code></code>