vba - Excel VBA 发送到 MSXML2.XMLHTTP 不起作用

Question

我在 excel VBA 中有以下代码，直到昨天它都运行良好。
现在尝试检查 URL 时失败了。但是 URL 确实存在!

它失败了: oXHTTP.send (它会出现错误)

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

可能是什么问题？

谢谢

Answer 1

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

请参阅此以获取 differences 的 SO 帖子.

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