Styjun

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://docs.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

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