XMLHTTP.send请求返回"Nothing"消息. [英] XMLHTTP.send request brings back "Nothing"

问题描述

我有一个电子表格，其中包含数百个链接，这些链接指向可以通过Web访问的服务器(具有身份验证).我一直在寻找电子表格中链接检查器的解决方案，该解决方案可以告诉我哪些链接断开，哪些可以.破损是指该网站根本没有被调用.

I have a spreadsheet that has hundreds of links that point to a server (with authentication) that can be accessed via the web. I've been searching for a solution to a Link Checker in a spreadsheet that would tell me which links are broken and which are ok. By broken I mean that the website does not get called up at all.

我在网络上找到了各种解决方案，但都不适合我.我对此感到困惑...

There are various solutions I have found around the web, none of which work for me. I'm boggled by this...

我尝试使用并弄清楚的一个示例在下面重新发布.

One example that I've tried to use and figure out is re-posted below.

单步执行代码后，我意识到oHTTP.send请求会带回"Nothing".对于电子表格中的所有链接，无论链接是否起作用，它都会这样做.

As I've stepped through the code, I have come to realize that the oHTTP.send request brings back "Nothing". It does so for all links in the spreadsheet, regardless of whether the link works, or not.

Public Function CheckHyperlink(ByVal strUrl As String) As Boolean

    Dim oHttp As New MSXML2.XMLHTTP30

    On Error GoTo ErrorHandler
    oHttp.Open "HEAD", strUrl, False
    oHttp.send

    If Not oHttp.Status = 200 Then CheckHyperlink = False Else CheckHyperlink = True

    Exit Function

ErrorHandler:
    CheckHyperlink = False
End Function

任何关于错误或对错的建议都将受到高度赞赏！

Any suggestions as to what might be wrong, or right, is highly appreciated!

推荐答案

几个可能的原因.

1. 您是说oHttp.Open "GET", strUrl, False而不是oHttp.Open "HEAD", strUrl, False吗?
2. 也许MSXML2.XMLHTTP30不可用?您可以将MSXML2.XMLHTTPX的实例声明为早绑定或后绑定，这可能会影响您要使用哪个版本与可用版本(例如

1. Do you mean oHttp.Open "GET", strUrl, False instead of oHttp.Open "HEAD", strUrl, False ?
2. Perhaps MSXML2.XMLHTTP30 is not available? You can declare an instance of MSXML2.XMLHTTPX as either early bound or late bound which may impact which version you want to use vs what is available (example http://word.mvps.org/FAQs/InterDev/EarlyvsLateBinding.htm)

例如

Option Explicit

'Dim oHTTPEB As New XMLHTTP30 'For early binding enable reference Microsoft XML, v3.0
Dim oHTTPEB As New XMLHTTP60 'For early binding enable reference Microsoft XML, v6.0

Sub Test()
Dim chk1 As Boolean
Dim chk2 As Boolean

 chk1 = CheckHyperlinkLB("http://stackoverflow.com/questions/11647297/xmlhttp-send-request-brings-back-nothing")

 chk2 = CheckHyperlinkEB("http://stackoverflow.com/questions/11647297/xmlhttp-send-request-brings-back-nothing")

End Sub

Public Function CheckHyperlinkLB(ByVal strUrl As String) As Boolean
Dim oHTTPLB As Object

'late bound declaration of MSXML2.XMLHTTP30
    Set oHTTPLB = CreateObject("Msxml2.XMLHTTP.3.0")

    On Error GoTo ErrorHandler
    oHTTPLB.Open "GET", strUrl, False
    oHTTPLB.send

    If Not oHTTPLB.Status = 200 Then CheckHyperlinkLB = False Else CheckHyperlinkLB = True

    Set oHTTPLB = Nothing
    Exit Function

ErrorHandler:
    Set oHTTPLB = Nothing
    CheckHyperlinkLB = False
End Function


Public Function CheckHyperlinkEB(ByVal strUrl As String) As Boolean
'early bound declaration of MSXML2.XMLHTTP60

    On Error GoTo ErrorHandler
    oHTTPEB.Open "GET", strUrl, False
    oHTTPEB.send

    If Not oHTTPEB.Status = 200 Then CheckHyperlinkEB = False Else CheckHyperlinkEB = True

    Set oHTTPEB = Nothing
    Exit Function

ErrorHandler:
    Set oHTTPEB = Nothing
    CheckHyperlinkEB = False
End Function

我通过在浏览器中打开测试了OP的链接，现在我发现它重定向到登录页面，因此它是我正在测试的另一个链接.可能由于未将oHttp对象设置为允许重定向而失败.我知道可以使用以下代码为WinHttp.WinHttpRequest.5.1设置重定向.我将需要调查这是否也适用于MSXML2.XMLHTTP30.

I tested the OP's link by opening in a browser which I've now discovered redirects to the login page instead so it's a different link I was testing. It's probably failing because the oHttp object has not been set to allow redirects. I know it's possible to set redirects for WinHttp.WinHttpRequest.5.1 using the code below. I would need to investigate if this also works for MSXML2.XMLHTTP30 though.

Option Explicit

Sub Test()
Dim chk1 As Boolean

 chk1 = CheckHyperlink("http://portal.emilfrey.ch/portal/page/portal/toyota/30_after_sales/20_ersatzteile%20und%20zubeh%C3%B6r/10_zubeh%C3%B6r/10_produktbezogene%20informationen/10_aussen/10_felgen/10_asa-pr%C3%BCfberichte/iq/tab1357333/iq%20016660.pdf")

End Sub


Public Function CheckHyperlink(ByVal strUrl As String) As Boolean
Dim GetHeader As String

    Const WinHttpRequestOption_EnableRedirects = 6
    Dim oHttp As Object

    Set oHttp = CreateObject("WinHttp.WinHttpRequest.5.1")

    On Error GoTo ErrorHandler
    oHttp.Option(WinHttpRequestOption_EnableRedirects) = True
    oHttp.Open "HEAD", strUrl, False
    oHttp.send

    If Not oHttp.Status = 200 Then
        CheckHyperlink = False
    Else
        GetHeader = oHttp.getAllResponseHeaders()
        CheckHyperlink = True

    End If

    Exit Function

ErrorHandler:
    CheckHyperlink = False
End Function

MSXML2.XMLHTTP确实允许重定向(尽管我相信MSXML2.ServerXMLHTTP不允许).根据重定向是否是跨域，跨端口等，允许/禁止重定向(请参见此处的详细信息

MSXML2.XMLHTTP does allow redirects (although I believe MSXML2.ServerXMLHTTP doesn't). The redirects are allowed/disallowed depending upon whether the redirect is cross-domain, cross-port etc (see details here http://msdn.microsoft.com/en-us/library/ms537505(v=vs.85).aspx)

由于重定向到登录页面是跨域的，因此将实现IE区域策略.打开IE/工具/Internet选项/安全性/自定义级别，然后将跨域访问数据源"更改为已启用

Since the redirect to the login page is cross-domain then IE zone policy is implemented. Open IE/Tools/Internet Options/Security/Custom Level and change 'Access data sources across domains' to ENABLED

原始OP的代码现在将正确重定向.

The original OP's code will now redirect properly.

这篇关于XMLHTTP.send请求返回"Nothing"消息.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！