Excel HTTP Get Timeout

Question

We have this little sub which simply pumps data into a remote server. All is working as expected.

The other day, there was an incident on the web server which lasted about an hour. I could still PING the server, but IIS was non-responsive. As a result, the macro just hanged waiting for a response.

Any thoughts on a quick pass/fail test or timeout?

Sub WebLog(getUser As String, getEvent As String, getValue As String)
    Dim URL As String
    URL = "http://myurl.com/dp.aspx?Task=Log&v1=" + getUser + "&v2=" + getEvent + "&v3=" + getValue
    Dim xml As Object
    Set xml = CreateObject("MSXML2.XMLHTTP")
    xml.Open "GET", URL, False
    xml.Send
End Sub

Answer 1

You may want to take a look at the WinHttp.WinHttpRequest.5.1 library, which lets you set timeouts for your requests.

WinHttpRequest object

You should set .SetTimeouts before .Open and the four parameters you set for .SetTimeouts are (in milliseconds):

Resolve, Connect, Send and Receive

Your sample code would be (for 10 second timeout):

Sub WebLog(getUser As String, getEvent As String, getValue As String)
    Dim URL As String
    URL = "http://myurl.com/dp.aspx?Task=Log&v1=" + getUser + "&v2=" + getEvent + "&v3=" + getValue
    Dim xml As Object
    Set xml = CreateObject("WinHttp.WinHttpRequest.5.1")
    xml.SetTimeouts 10000, 10000, 10000, 10000
    xml.Open "GET", URL, False
    xml.Send
End Sub