Why does this HTTP head request not work in Excel VBA?

Question

I need to detect whether a URL is served as is or redirects. A StackOverflow answer told me this can be done by making an HTTP head request and watching for the response code. If the URL is served as is, then the request should return the success code, 200. But if it redirects, then the request should return the redirect code, 303.

I'm not familiar with HTTP requests, and ChatGPT has told me before that it can write code for me, so I gave it a try, asking, "How do I make a HEAD HTTP request in Excel VBA?" It gave me a VBA subroutine, which I converted to the following function:

Public Function nHeadRequest(sURL As String) as Integer

On Error Resume Next

Dim oXmlHttp As Object, nRetVal As Integer
Set oXmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
oXmlHttp.followRedirects = True

oXmlHttp.Open "HEAD", sURL, False
oXmlHttp.setRequestHeader "Content-Type", "text/xml"
oXmlHttp.send ""
nRetVal = oXmlHttp.Status

Set oXmlHttp = Nothing

On Error GoTo 0

nHeadRequest = nRetVal

End Function        ' nHeadRequest()

I call this function in Excel with the formula =nHeadRequest(<URL>). I've tested this with the following three URLs:

• https://www.youtube.com/shorts/sIji90rlQK4
  • This URL is served as is and should return the success code, 200.
• https://www.youtube.com/shorts/w3-yM1IjuB0
  • This redirects to https://www.youtube.com/watch?v=w3-yM1IjuB0 and I understand that means it should return the redirect code, 303.
• https://www.youtub.com
  • This is a bad URL and should return an error code.

Instead of those anticipated results, I get 200 for both of the first two URLs. For the third URL, I get 0, which I presume is not a status code, but is the result of the request encountering an error when accessing the bad URL and following the on error resume next command.

So I suspect there's a problem in the code ChatGPT gave me. I've heard that ChatGPT can also help with debugging code, so I told it about the incorrect results. It suggested some changes, but nothing that fixed the code.

So, can someone say what's wrong with the above code?

• Why am I getting 200 for a URL that redirects?
• Why am I not getting a proper error response on a bad URL?

Answer 1

Track redirection with WinHttp.WinHttpRequest.5.1

Public Function nHeadRequest(sURL) As Integer
    Const WHR_EnableRedirects = 6
    Dim oXmlHttp As Object, nRetVal As Integer
    Set oXmlHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
    oXmlHttp.Option(WHR_EnableRedirects) = False
    oXmlHttp.Open "HEAD", sURL, False
    oXmlHttp.setRequestHeader "Content-Type", "text/xml"
    On Error Resume Next
    oXmlHttp.send ""
    If Err.Number = 0 Then
        nRetVal = oXmlHttp.Status
    Else
        nRetVal = -9999
    End If
    On Error GoTo 0
    Set oXmlHttp = Nothing
    nHeadRequest = nRetVal
End Function

Sub demo()
    Dim sU As String
    sU = "https://www.youtube.com/shorts/w3-yM1IjuB0"
    Debug.Print (nHeadRequest(sU))
    sU = "https://www.youtub.com"
    Debug.Print (nHeadRequest(sU))
End Sub

Answer 2

Expanding on the accepted answer by @taller_ExcelHome:

That answer told me that the problem with my code was that it set

Option(nEnableRedirects) = true

The answer was correct because when I changed that to false, the code did return status 303 when the URL was redirected. But this didn't seem to make sense because setting it to false would seem to mean that redirects are disabled, so I should never get a status 303.

I think I've figured out the apparent logical inconsistency.

The MS documentation for the WinHttpRequest object links to documentation for the option property, which links to documentation for the option values. There we see:

WinHttpRequestOption_EnableRedirects

Sets or retrieves a VARIANT that indicates whether requests are automatically redirected when the server specifies a new location for the resource. The default value of this option is VARIANT_TRUE to indicate that requests are automatically redirected.

I think what's going on is that "automatically redirected" implies "redirected without notice", so that if redirection takes place, the request returns success status 200, just as if the redirection had not happened. Turning this off doesn't prevent redirection, but it causes redirection to result in a non-success status, specifically 303, which was the behavior intended for my function.