2

I can make a simple API call in JavaScript using XMLHttpRequest:

    <html>
    
    <script>
        function reqListener () {
            out.textContent=this.responseText;
        }
    
    var oReq = new XMLHttpRequest();
    oReq.addEventListener("load", reqListener);
    oReq.open("GET", "https://api.stackexchange.com/2.3/tags?order=desc&sort=popular&site=stackoverflow");
    oReq.send();
        </script>
        
        <body><span id='out'></span></body>
    </html>

using the code from:

https://developer.mozilla.org/en-US/docs/Web/API/XMLHttpRequest/Using_XMLHttpRequest

The same code in Excel:

Dim xh As Object
Set xh = CreateObject("MSXML2.serverXMLHTTP")

seurl = "https://api.stackexchange.com/2.3/tags?order=desc&sort=popular&site=stackoverflow"
          
xh.Open "GET", seurl, False
xh.Send

MsgBox xh.responseText

doesn't work, I get a return of '?[]'.

I can't see any difference. Why does the first one work and not the second?

I have read:

https://api.stackexchange.com/docs/compression

on compression, but the GET request seems to return a text file, at least in the JavaScript version.

Improve this question
1
  • 1
    The response from the API is definitely compressed, but your browser is smart enough to detect that out of the box, hence it 'just works' in JavaScript. I'm not sure about VBA but it's been 15 years since I touched that ...
    – Glorfindel ♦
    Commented Nov 2, 2021 at 6:47

1 Answer 1

Reset to default
3

The response from Stack Exchange is always compressed, as indicated by Glorfindel.

Note that browsers offer the decompression to you by default but the MSXML2.serverXMLHTTP COM/OLE component pre-dates the modern compression habits. It simply lacks the option to cope with that kind of responses.

If you inspect the responseHeader "Content-Encoding" after you called send like so:

MsgBox xh.getResponseHeader("Content-Encoding")

You will see gzip as an answer.

You can indicate to the server with a RequestHeader Accept-Encoding what encoding/compression you support. Add this line before the send method:

xh.setRequestHeader "Accept-Encoding", "deflate;q=1.0"

and you'll get deflate as Content-Encoding response.

According to the HTTP specification identity is a valid Accept-Encoding and should instruct the server to not use compression. This is where our luck runs out as the Stack Exchange server is non-compliant here. It simply falls back to its preferred default compression: gzip. I think SE should return a 406 Not Acceptable in this case, but meh.

Given that we can't tell the server to send a plain response, we have to somehow decompress the received response body. After some searching I found How to decompress http responses in vba excel? by user Mavin which has a complete native VBA module with an Inflate method.

I'm not sure how recent the Excel is you have but when testing this on Excel 2010 I ran into a problem with the use of Application.WorksheetFunction.Bitlshift in the checkgzip function which doesn't exist in older Excel versions. I have replaced that line with

    decompressedsize = size(3) * 2 ^ 32 + size(2) * 2 ^ 16 + size(1) * 2 ^ 8 + size(0)

to achieve the same result.

My whole main program now looks like:

Dim xh As Object
Set xh = CreateObject("MSXML2.serverXMLHTTP")

seurl = "https://api.stackexchange.com/2.3/tags?order=desc&sort=popular&site=stackoverflow"
          
xh.Open "GET", seurl, False
xh.setRequestHeader "Accept-Encoding", "gzip;q=1.0"
xh.Send

MsgBox xh.getResponseHeader("Content-Encoding")

Dim httpresponse() As Byte

httpresponse = xh.responseBody

Mod_Inflate64.Inflate httpresponse

MsgBox StrConv(httpresponse, vbUnicode)

and when run this is the result:

SE json result

Improve this answer
1
  • I got it to work! First save as a class module (so change the name to Mod_Inflate64, and in MS365, the class can't be used like in yours, so Dim x As Mod_Inflate64 Set x = New Mod_Inflate64 x.Inflate httpresponse instead. Output ...(partial) quired":false,"count":2293185,"name":"javascript"},{"h...
    – JMP
    Commented Nov 5, 2021 at 19:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .