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

drec4s · Accepted Answer · 2018-04-13 23:27:12Z

15

+50

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

answered Apr 13, 2018 at 23:27

drec4s

8,1178 gold badges35 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Cappelletti Over a year ago

FABULOUS - Knew there had to be a timeout. Expect a bounty as soon as the system lets me

John Cappelletti Over a year ago

Never undstood why we can't create a bounty from day 1.

Collectives™ on Stack Overflow

Excel HTTP Get Timeout

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related