(excel Vba): Accessing Json File - Operation Timed Out

I'm attempting to pull data from a JSON file on the web. I'm using a dummy JSON file for the time being to get things working. My code is below, but it times out every time and doe

Solution 1:

Is there a special reason why using WinHttpRequest instead of XMLHTTPRequest?

While using WinHttpRequest the operating system defaults for HTTP requests - proxy settings for example - are not used and must be set explicitly:

Sub Test()
    Dim strResult AsStringDim objHTTP AsObjectDim URL AsStringSet objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    objHTTP.SetProxy 2, "proxyIP:proxyPort"
    URL = "https://jsonplaceholder.typicode.com/posts/2"
    objHTTP.Open "GET", URL, False
    objHTTP.setCredentials "username", "password", 1
    objHTTP.Send
    strResult = objHTTP.ResponseText
    MsgBox strResult
EndSub

The 2 in IWinHttpRequest::SetProxy method is HTTPREQUEST_PROXYSETTING_PROXY. The 1 in IWinHttpRequest::SetCredentials method is HTTPREQUEST_SETCREDENTIALS_FOR_PROXY.

While using XMLHTTPRequest the operating system defaults for HTTP requests are used as set in Internet Options in control panel. So the following should run if you are able accessing the URL via browser:

Sub Test()
    Dim strResult AsStringDim objHTTP AsObjectDim URL AsStringSet objHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
    URL = "https://jsonplaceholder.typicode.com/posts/2"
    objHTTP.Open "GET", URL, False
    objHTTP.Send
    strResult = objHTTP.ResponseText
    MsgBox strResult
EndSub

Solution 2:

Your code works OK here, but perhaps you should .WaitForResponse if things are timing out:

Sub Test()
    Dim strResult AsStringDim objHTTP AsObjectDim URL AsStringSet objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    URL = "https://jsonplaceholder.typicode.com/posts/2"
    objHTTP.Open "GET", URL, False
    objHTTP.Send

    objHTTP.waitforresponse

    strResult = objHTTP.ResponseText
    MsgBox strResult
EndSub