Checking header responses

Sometimes you may need to check the header response when doing a web query. MSXML lets us do this fairly quickly.

Normally we use GET or POST requests to grab responses from a website. But there is another option (are there more?): HEAD requests. These requests return just the headers from whatever URL you call. This way, we can check if the request is well formed or if the website is available prior to making an actual request.

Here is some sample VBA code that makes a HEAD request and returns the headers as an array:

Function GetHeaderResponse(URL As String) As String()

  Dim strResponse As String
  Dim xml As Object ' MSXML2.XMLHTTP60
  Dim responseHeaders() As String

  Set xml = GetMSXML

  With xml
    .Open "HEAD", URL, False
    .send
  End With

  responseHeaders = Split(xml.getAllResponseHeaders, vbCrLf)

  GetHeaderResponse = responseHeaders

End Function

The GetMSXML function may be found on the MSXML Object Library page.

Sample Usage

Sub test()
  Dim results() As String
  results = GetHeaderResponse("http://www.google.com/")
  Debug.Print results(0)
End Sub
About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 2 Comment(s) on Checking header responses:

  1. so i am beginning to understand just how valuable this blog is to VBA bunglers such as myself… more and more i am finding a need for using the "hyperlink" formula in Excel and not knowing how to get the link to open the browser and return the web-page information iwhen the website requires tedious enty of additional parameters before getting you to the page with the data.

    anyway, i tried the above code thinking i could learn something new but i get stopped with "Compile Error: user defined type not defined" (whatever that means) at the "Dim xml As MSXML2.XMLHTTP60" line of code. what am i doing wrong?

    • Thanks Bill!

      I'm afraid I forgot to make my code late bound, just declare xml As Object instead. I'll fix the code in the post.

This article is closed to any future comments.
Excel School