How to parse JSON with VBA without external libraries

Question

I have a json like below     sentences     trans   something ru   orig   english word   translit   Angliyskoye slovo   src translit         src   en   server time  69    and parse it   Function jsonDecode jsonString As Variant      Set sc   CreateObject  ScriptControl    sc Language    JScript      Set jsonDecode   sc Eval       jsonString        End Function  Set arr   jsonDecode txt    In result arr contains values like below  checked at Watches    arr  - sentences  type  Variant Object JScriptTypeInfo    - 0  type  Variant Object JScriptTypeInfo      - orig  type  Variant String      - trans  type  Variant String            - Item 1  type  Variant Object JScriptTypeInfo      - orig  type  Variant String      - trans  type  Variant String           - server time  - src   arr src works well  but how can I get arr sentences 0  trans  Firstly  VBA replaces sentences with Sentences  secondly  when I ve tried to change the json manually  it still doesn t allow to use sentenses 0

User · Answer

Call me simple but I just declared a Variant and split the responsetext from my REST GET on the quote comma quote between each item  then got the value I wanted by looking for the last quote with InStrRev   I m sure that s not as elegant as some of the other suggestions but it works for me            varLines   Split  responsetext                   strType   Mid varLines 8   InStrRev varLines 8           1

User · Answer

There are two issues here  The first is to access fields in the array returned by your JSON parse  the second is to rename collections fields  like sentences  away from VBA reserved names  Let s address the second concern first  You were on the right track  First  replace all instances of sentences with jsentences If text within your JSON also contains the word sentences  then figure out a way to make the replacement unique  such as using  quot sentences quot    as the search string  You can use the VBA Replace method to do this  Once that s done  so VBA will stop renaming sentences to Sentences  it s just a matter of accessing the array like so   first  declare the variables you need  Dim jsent as Variant   Get arr all setup  then For Each jsent in arr jsentences   MsgBox jsent orig  Next

User · Answer

I ve found this script example useful  from http   www mrexcel com forum excel-questions 898899-json-api-excel html post4332075     Sub getData        Dim Movie As Object     Dim scriptControl As Object      Set scriptControl   CreateObject  MSScriptControl ScriptControl       scriptControl Language    JScript       With CreateObject  MSXML2 XMLHTTP            Open  GET    http   www omdbapi com  t frozen amp y  amp plot short amp r json   False          send         Set Movie   scriptControl Eval        responsetext                 abort         With Sheets 2               Cells 1  1  Value   Movie Title              Cells 1  2  Value   Movie Year              Cells 1  3  Value   Movie Rated              Cells 1  4  Value   Movie Released              Cells 1  5  Value   Movie Runtime              Cells 1  6  Value   Movie Director              Cells 1  7  Value   Movie Writer              Cells 1  8  Value   Movie Actors              Cells 1  9  Value   Movie Plot              Cells 1  10  Value   Movie Language              Cells 1  11  Value   Movie Country              Cells 1  12  Value   Movie imdbRating         End With     End With  End Sub

[json] How to parse JSON with VBA without external libraries?

Examples related to json

Examples related to vba

Examples related to object