Invalid data format from request response - VBA

Asked Sep 05 '17 at 20:07

Active Dec 18 '17 at 15:24

Viewed 100 times

Trying to pull data from private page, I am receiving a JSON response and would like to check whether everything is okay. The status of the request is fine (200), but if I use

Debug.Print (xmlhttp.responseText)

I see unknown ( for me ) format, it looks like:

? ?s???+]?f? ?n??????w????@??E????LJ??n???

Do you know how to convert it to standard JSON code ?

If I use the same headers in Javascript, then everything is okay, so I would like to know why excel converting the response to such strange form

Thanks in advance.

edited Dec 18 '17 at 15:24

BruceWayne

22,449
14
60
100

asked Sep 05 '17 at 20:07

Vba_Beg

Look at the encoding. `Debug.Print` and the VBE want to work with ANSI/ASCII. – Mathieu Guindon Sep 05 '17 at 20:22
so where can I change it? sorry, I am begginer :-) – Vba_Beg Sep 05 '17 at 20:30
Look into how to use `StrConv`, see if it helps. – Mathieu Guindon Sep 05 '17 at 20:31
1

It looks like `responseText` failed to decode the response from `utf-8` to `utf-16`. It might be due to a header in the response. You'll probably have to convert the response with `ADODB.Stream` or `MultiByteToWideChar`. There's too many "?" to be due to the ANSI limitation of the immediate window. – Florent B. Sep 05 '17 at 20:34
Hey Florent, I don't understand what you mean. How to use ADOBD.Stream ? – Vba_Beg Sep 05 '17 at 20:43
@Vba_Beg, have a look here: https://stackoverflow.com/questions/14252762/how-to-parse-line-by-line-winhttp-response-utf-8-encoded-csv – Florent B. Sep 05 '17 at 20:46
Thanks , but I see the following error: Arguments are of the wrong type, are out of acceptable range or are in conflict with one another – Vba_Beg Sep 05 '17 at 20:58
the part of code: FileStream.Type = 1 'Binary FileStream.Write xmlhttp.responseText – Vba_Beg Sep 05 '17 at 20:58
may it be related to this : FileStream.Type ? – Vba_Beg Sep 05 '17 at 20:58
should I change the type ? – Vba_Beg Sep 05 '17 at 20:58
@Vba_Beg, you need to keep the line `FileStream.Write HTTPReq.responseBody`. For the line `FileStream.Charset`, you need to set the one returned by the response. It should be `utf-8`. Check the returned header `content-type` with `xmlhttp.GetResponseHeader("content-type")`. – Florent B. Sep 05 '17 at 21:12
it returns application/json; charset=utf-8 – Vba_Beg Sep 05 '17 at 21:28
Changed the code to https://paste.ofcode.org/ahkgPQmWNcmqvvhPYpNbnP – Vba_Beg Sep 05 '17 at 21:29
But still see the safe text format :- – Vba_Beg Sep 05 '17 at 21:29
nothing change :-( – Vba_Beg Sep 05 '17 at 21:29
@Vba_Beg try `Cells(1, 1).Value = xmlhttp.responseTex` or `MsgBox xmlhttp.responseTex` instead of `Debug.Print (xmlhttp.responseText)` to output response. Then just copy the string and paste here. – omegastripes Sep 15 '17 at 20:45

Invalid data format from request response - VBA

0 Answers0