WinHttpRequest Object returns "â??" instead of "✓" (U+2713)

Question

I am using Excel 2007 (12.0.4518.1014)

I have been using the WinHttpRequest Object to perform API GET Requests on a web service that hosts data for me.

Everything else works properly and it grabs the JSON formatted data from the web service and puts it into a string with the .ResponseText property.

The issue I am having is that inside that string, all of the Unicode characters are turned into gibberish like â?? instead of ✓ (U+2713). Which means that when I do MyRange.Value = .ResponseText the cell value becomes â??.

If I set the GET request to ask for Xml format, I get �?? instead of ✓

I have confirmed by repeating the GET request in Chrome, the web service is outputting the correct Unicode symbol, and Chrome was able to show me ✓. So this is an issue with VBA or WinHttp.

Excel by itself is able to generate Unicode symbols and VBA is able to do it as well ChrW(10003).

How can I preserve Unicode symbols during a GET request? Is it possible with WinHttp or do I need to change methods?

Edit:

Here are the headers during a standard response:

{
  "access-control-allow-headers": "Content-Type",
  "access-control-allow-methods": "GET, POST, PUT, DELETE, OPTIONS",
  "access-control-allow-origin": "*",
  "cache-control": "private",
  "content-encoding": "gzip",
  "content-security-policy": "frame-ancestors 'self', default-src * 'unsafe-inline' 'unsafe-eval' data: blob:;",
  "content-type": "application/json",
  "date": "Wed, 23 Jun 2021 18:08:53 GMT",
  "expect-ct": "max-age=0;",
  "referrer-policy": "strict-origin-when-cross-origin",
  "strict-transport-security": "max-age=31536000; includeSubDomains; preload",
  "vary": "Accept-Encoding",
  "x-content-type-options": "nosniff",
  "x-frame-options": "SAMEORIGIN",
  "x-stackifyid": "V2|80002f92-0000-3100-b63f-84710c7967bb|C61313|CD10436"
}

Update: RESOLVED!

I have solved my problem with advice from @GSerg and wonderful insight from @JoelCoeHoorn. I will write how it was solved here since my question was closed.

WinHTTPRequest was swapped out for an XMLHTTP Object. This Object can be used in VBA with similar commands to WinHTTPRequest, as shown in the link about halfway down the page. But the XMLHTTP object was able to return Unicode characters with no issues.

To use it in VBA, you can create it with the line:

    Dim http As Object
    Set http = CreateObject("Microsoft.XMLHTTP")

And then you're ready to go with .open and .setRequestHeader and .Send similar to the WinHttpRequest object.

@JoelCoehoorn Where can I go to learn how to decode UTF-8 in VBA? Do you have any advice? — Toddleson, Jun 23 '21 at 18:01
@GSerg I am not sure where to put that header setting `"Accept-Encoding", "identity"`. I see my application uses `"content-encoding": "gzip"`, I tried changing that with `.setRequestHeader "content-encoding", "identity"`. Is this the correct header to put that setting into? — Toddleson, Jun 23 '21 at 18:41
No, this goes into the request, not the response, and the server must be prepared to honor that request (which it may not if you have hardcoded `"content-encoding": "gzip"`). So before you do that, try using XMLHTTPRequest instead of WinHttpRequest in VBA. — GSerg, Jun 23 '21 at 18:43
@GSerg THANK YOU!!!!! The XMLHTTP object works perfectly! It is able to GET at the same address and return a real check mark instead of gibberish. If you want to, submit your notes and advice as an answer and I'll mark this question as answered. — Toddleson, Jun 23 '21 at 20:51
If you've solved your own problem, don't edit the question - write an answer below and accept it yourself... — JustCarty, Jun 25 '21 at 08:11
@JustCarty It was marked as duplicate before I solved it. Now I cant submit an answer, — Toddleson, Jun 25 '21 at 13:14

Joel Coehoorn · Answer 1 · 2021-06-23T18:47:05.970

I know of five ways this can happen relative to an HTTP transaction:

The response has a header that includes what specific encoding is used. If the chosen encoding doesn't have the ability to display all the character points used in the text, this is what you get.

This is also what you get if text of the response is set directly and not mapped to the encoding specified in the header, so the encoding says the text should be different than what it is.

For historical reasons, there are some encodings that are system dependent, where the upper region of the encoding depends on the locally installed language packs/settings. So you can see this effect if the header chooses a system-specific encoding and the text is set on a system where this upper region is interpreted differently than the client, even though both ends used the same encoding.

The fourth way this can happen is with UTF-8 when a byte order mark is used incorrectly, ignored, or interpreted as text.

Finally, (and this is the most likely of these options to fit your situation) this can happen when an encoding is used in one place that is not supported in the other. VBA pre-dates the widespread adoption of unicode, and does not have good unicode support. Especially older versions of VBA, like you might encounter in, say, the long unsupported Excel 2007.

These problems all tend to manifest only for unicode characters and leave simple latin characters alone, because many encodings all handle the simple latin characters in the exact same way.

I am still very inexperienced with REST requests and headers, I will add an example of all of the headers with their default setting to my post, could you take a look and see if there is something I can do to make it compatible with Excel/VBA? — Toddleson, Jun 23 '21 at 18:13
I have Excel 2003 here and VBA in it is perfectly Unicode as usual. It's the WinHttpRequest that is supposed to decode its whatever encoding into UTF-16 that VBA uses. — GSerg, Jun 23 '21 at 18:13
Sixth option though, `WinHttpRequest` [has problems](https://stackoverflow.com/q/44193488/11683) with gzip... — GSerg, Jun 23 '21 at 18:38
@GSerg You should make that into a new answer for this question. Technically this would be a dupe question, but it's one of those cases where the asker would have a hard time finding the original because they don't know initially gzip is involved, and so a new answer is probably justified. — Joel Coehoorn, Jun 23 '21 at 18:51

WinHttpRequest Object returns "â??" instead of "✓" (U+2713)

1 Answers1