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
2 Comment(s) on Checking header responses:
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.