Excel VBA send to MSXML2.XMLHTTP does not work

Question

I have the following code in excel VBA, and it worked well until yesterday. Now it fails when trying to check the URL.. but the URL does exist!

It fails in line: oXHTTP.send (it goes to haveError)

Function HttpExists(sURL As String) As Boolean
Dim oXHTTP As Object
Set oXHTTP = CreateObject("MSXML2.XMLHTTP")

If Not UCase(sURL) Like "HTTP:*" Then
    sURL = "http://" & sURL
End If

On Error GoTo haveError
oXHTTP.Open "HEAD", sURL, False
oXHTTP.send
HttpExists = IIf(oXHTTP.status = 200, True, False)
Exit Function

haveError:
    HttpExists = False
End Function

sURL is the following, which does exist:

http://www.google.com/finance/historical?q=COF&startdate=Jan+1%2C+2017&enddate=Dec+31%2C+2017&num=30&ei=WLQtWaAfiMOxAbeYutgE&output=csv

What can be the problem?

Thanks

Answer 1

Consider adding a message in your error handle block which gives a more informative issue which can be resolved by using MSXML2.ServerXMLHTTP instead of MSXML2.XMLHTTP.

See this for SO post for the differences.

Function HttpExists(sURL As String) As Boolean

    Dim oXHTTP As Object
    Set oXHTTP = CreateObject("MSXML2.ServerXMLHTTP")

    If Not UCase(sURL) Like "HTTP:*" Then
        sURL = "http://" & sURL
    End If

    On Error GoTo haveError
    oXHTTP.Open "HEAD", sURL, False
    oXHTTP.send
    HttpExists = IIf(oXHTTP.Status = 200, True, False)
    Exit Function

haveError:
    MsgBox Err.Number & " - " & Err.Description, vbCritical
    HttpExists = False
End Function


Sub RunFct()
    Dim output As Boolean

    output = HttpExists("http://www.google.com/finance/historical?q=COF&startdate=Jan+1%2C+2017&enddate=Dec+31%2C+2017&num=30&ei=WLQtWaAfiMOxAbeYutgE&output=csv")
    Debug.Print output
End Sub

Outputs in immediate window

TRUE