VBA HTTP request to API with JSON body returns empty array (MSXML2.XMLHTTP in Excel)

Question

Update: SOLVED --> Look below for answer.

Update: At the Microsoft docs i can see that when open method is called with Async=false, the response might not return if "the protocol stack times out", however I have not been able to read up on the timeout timers. https://learn.microsoft.com/en-us/previous-versions/windows/embedded/ms931177%28v%3dmsdn.10%29 Not really sure if it's related or not.

I'm trying to retrieve a JSON response object through VersionOne query API. When i try to read the responseText in VBA I receive an empty array, however the exact same request returns correct data from PostMan. I get an HTTP 200 code response, but no data in the response body.

The reason for doing it in VBA and Excel is that the data needs to be analalyzed in a pre-existing Excel model. I've tried different auth possibilities, both OAUTH and Basic.

This is the VBA code

Option Explicit
Sub Test_LateBinding()

    Dim objRequest As Object
    Dim strUrl As String
    Dim strResponse As String
    Dim body As String
    Dim strResponseHeaders As String
    Dim allResponseHeader As String

    Set objRequest = CreateObject("MSXML2.XMLHTTP")
    strUrl = "https://endpoint"
    body = " { ""from"": ""Epic"",""select"": []}"    
    'with basic'
    With objRequest
        .Open "GET", strUrl, False, "XXXX", "XXXX"
        .SetRequestHeader "Content-Type", "application/json"
        .Send body
        strResponseHeaders = .StatusText
        strResponse = .ResponseText
        allResponseHeader = .GetAllResponseHeaders
    End With
    Debug.Print body
    Debug.Print allResponseHeader
    Debug.Print strResponse

End Sub

This is my console output:

OK
Content-Type: application/json; charset=utf-8
Content-Length: 2
X-Content-Type-Options: nosniff
V1-MemberID: skatteministeriet/120267
Strict-Transport-Security: max-age=31536000; includeSubdomains
X-Robots-Tag: noindex
VersionOne: Ultimate/19.0.3.29; 0
X-Instart-Request-ID: 3912039762705832388:SEN01-NPPRY25:1553377406:0

[]

This is the PostMan response headers: PostMan response headers

This is the URL and request JSON body: URL and request body

Can you point to which example in the guidance aligns with your use case? The closest I found so far is a POST request here: community.versionone.com/VersionOne_Connect/Developer_Library/… — QHarr
– QHarr, Commented Mar 24, 2019 at 11:52
Endpoint introduction community.versionone.com/VersionOne_Connect/Developer_Library/… Endpoint tour community.versionone.com/VersionOne_Connect/Developer_Library/… Tbh the documentation is horribly organized, it's structured so wierd... — Hassassin
– Hassassin, Commented Mar 24, 2019 at 12:33
Could you please provide a valid URL? Also, add a screenshot of the working OK PostMan request with all headers and parameters to the question (or the link). — omegastripes
– omegastripes, Commented Mar 24, 2019 at 23:45
The valid URL for the endpoint will be troublesome to post, since the endpoint is organisation independant and therefore "non-disclosable". However, screenshots of the PostMan request i s possible. I will update this post with it. — Hassassin
– Hassassin, Commented Mar 25, 2019 at 8:57

Hassassin · Accepted Answer · 2019-03-28 14:20:14Z

SOLVED Finally...

So i found the answer. After re-analyzing the Postman response i found that the JSON response was actually handed as a gzip encoded response. Which is simply not compatible with the MSXML2.XMLHTTP lib.

So to solve the problem all I did was to use the WinHttp.WinHttpRequest.5.1 lib instead, which is basicly newer. No other changes to the code was needed.

So to others out there using MSXML2.XMLHTTP or the WinHTTPserver.6.0 lib, change to the newer library :)


Option Explicit
Sub Test_LateBinding()

    Dim objRequest As Object
    Dim strUrl As String
    Dim strResponse As String
    Dim body As String
    Dim strResponseHeaders As String
    Dim allResponseHeader As String

    Set objRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    strUrl = "https://endpoint"
    body = " { ""from"": ""Epic"",""select"": []}"    
    'with basic'
    With objRequest
        .Open "GET", strUrl, False, "XXXX", "XXXX"
        .SetRequestHeader "Content-Type", "application/json"
        .Send body
        strResponseHeaders = .StatusText
        strResponse = .ResponseText
        allResponseHeader = .GetAllResponseHeaders
    End With
    Debug.Print body
    Debug.Print allResponseHeader
    Debug.Print strResponse

End Sub

Collectives™ on Stack Overflow

VBA HTTP request to API with JSON body returns empty array (MSXML2.XMLHTTP in Excel)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related