Excel VBA发送到MSXML2.XMLHTTP不起作用 [英] Excel VBA send to MSXML2.XMLHTTP does not work

问题描述

我在excel VBA中具有以下代码，并且一直有效到昨天. 现在，当尝试检查URL时它失败了.但是该URL确实存在！

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!

它在以下行中失败: oXHTTP.send (它出现在hasError中)

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是以下存在的:

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

可能是什么问题?

谢谢

推荐答案

请考虑在错误句柄块中添加一条消息，该消息会提供更多信息，可以通过使用MSXML2.ServerXMLHTTP而不是MSXML2.XMLHTTP来解决.

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.

有关SO帖子，请参见差异.

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

即时窗口中的输出

TRUE

这篇关于Excel VBA发送到MSXML2.XMLHTTP不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！