CODEWITHSUNDEEP
excelvba
carolinetoynbee
carolinetoynbee

Reputation: 11

(XMLHTTP60) no longer returning required data

Help gratefully received on this one. I have some VBA running in Excel that inspects a series of webpages displaying betting odds for football matches and puts the odds into my spreadsheet. It has been working perfectly for months and has stopped working in the last few weeks. Here is a simplified version of the code I'm using:

Sub TestImport()

Dim http As New MSXML2.XMLHTTP60
Dim html As New MSHTML.HTMLDocument
Dim htmlEle1 As MSHTML.IHTMLElement
Dim columncounter As Integer
Dim rowccounter As Integer
Dim targetwebpage As String
Dim ColumnHeader As Variant

On Error GoTo ErrorStop

trowNum = 1

    targetwebpage = "https://www.oddschecker.com/football/english/premier-league"
        With http
            .Open "get", targetwebpage, False
            .send
        End With

Set table_data = html.getElementsByTagName("tr")

If table_data.Length = 0 Then GoTo SkipLeague

For Each trow In table_data

    For Each tcell In trow.Children

      If tcell.innerText <> "TIP" Then 'Ignore this
        tcellNum = tcellNum + 1
        Cells(trowNum, tcellNum) = tcell.innerText
      End If
    
    Next tcell
    
    Cells(trowNum, 1) = Worksheets("Leagues").Cells(j, 1)
    trowNum = trowNum + 1
    tcellNum = 1
    
Next trow

SkipLeague:

ErrorStop:

End Sub

No data gets returned because [table_data] is always null. It's always null because there are no tags in my variable, [html]. Instead, [html] seems to be simply this:

"<HEAD></HEAD>
<BODY>
<P>&nbsp;</P></BODY>"

Why would [html] return this value when the actual webpage (https://www.oddschecker.com/football/english/premier-league) is much more complex when displayed in my browser? And why has this problem only started in the last few weeks?

I'd be grateful for any help on this.

Upvotes: 0

Views: 1007

Answers (1)

Keith Swerling
Keith Swerling

Reputation: 165

I did a quick test and had no issue. Some page, like Google require the User-Agent to be sent, but not the oddschecker page.

Sub TestURL()
    Debug.Print GetResult("https://www.oddschecker.com/football/english/premier-league")
End Sub 

Function GetResult(url As String) As String
    Dim XMLHTTP As Object, ret As String
    Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.setRequestHeader "Cache-Control", "no-cache"
    XMLHTTP.setRequestHeader "Pragma", "no-cache"
    XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
    XMLHTTP.send
    ret = XMLHTTP.responseText
    GetResult = ret
End Function

Upvotes: 1

Related Questions