CODEWITHSUNDEEP
excelvbarest
bgordon
bgordon

Reputation: 149

VBA-Web Request Timeout

I have an Excel add-in built using VBA and one of my users is experiencing Request Timeouts when attempting to Post to my cloud-based API. I'm using VBA-Web tools https://github.com/VBA-tools/VBA-Web to make HTTP requests.

Dim Request As New WebRequest
Request.Resource = Resource 'my api endpoint
Request.Method = WebMethod.HttpPost
Request.Format = WebFormat.Json
Request.ResponseFormat = WebFormat.Json
Request.AddHeader "Authorization", "Bearer " & SessionKey

Client.TimeoutMs = 15000

Set Request.Body = Body 'a dictionary with user data

Dim Response As WebResponse
Set Response = Client.Execute(Request)

If Response.StatusCode <> Ok Then
    Debug.Print Response.StatusCode
    Debug.Print Response.StatusDescription
End If

I've asked the user to send me their logs and they look like this:

Response.StatusCode
Response.StatusDescription

>> 408
>> Request Timeout: The operation timed out

I originally assumed this was a firewall issue because no one has reported this issue apart from one group of users (located within the same building), but I've asked them to navigate to the URL in their internet browser and they were able to get a response.

This is unusual since I've set the default timeout period to 15 seconds and yet this response appears almost immediately after making the request. I'd also expect to see logs from my API that a request was made but I can see none (I'm using AWS API Gateway).

I'm wondering whether Excel being blocked from making network requests, is this a default behaviour in some Windows machines? Is there a way to check for it within VBA? Also, it looks like VBA-Web generalises a range of status codes as 408 (https://github.com/VBA-tools/VBA-Web/blob/eb857f0dee739ff4a55cfae80b24b82418aee816/src/WebClient.cls#L345-L352), is anyone familiar with why these would be returned?

Upvotes: 2

Views: 3763

Answers (1)

Louis
Louis

Reputation: 3632

Try to change library.

I've dealt with many API services and, from my experience, if it works on the browser it has almost certainly to do with the library you use and how it handles the request, which in this case is WinHttpRequest 5.1.

I would suggest using an alternative library to send your data and see if that works, like MSXML2.XMLHTTP60. In this example, you send a GET request (useful to see if you can reach the server):

Sub tester()
Dim objCON As New MSXML2.XMLHTTP60
Dim URL As String

    URL = "http://www.myurl.com"

    objCON.Open "GET", URL, False
    objCON.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    objCON.send 

    MsgBox (objCON.responseText)
End Sub

Hope this helps.

Upvotes: 1

Related Questions