Dim Json As Object

Set Json = JsonConverter.ParseJson("{""a"":123,""b"":[1,2,3,4],""c"":{""d"":456}}")



' Json("a") -> 123

' Json("b")(2) -> 2

' Json("c")("d") -> 456

Json("c")("e") = 789



Debug.Print JsonConverter.ConvertToJson(Json)

' -> "{"a":123,"b":[1,2,3,4],"c":{"d":456,"e":789}}"



Debug.Print JsonConverter.ConvertToJson(Json, Whitespace:=2)

' -> "{

'       "a": 123,

'       "b": [

'         1,

'         2,

'         3,

'         4

'       ],

'       "c": {

'         "d": 456,

'         "e": 789  

'       }

'     }"

' Advanced example: Read .json file and load into sheet (Windows-only)

' (add reference to Microsoft Scripting Runtime)

' {"values":[{"a":1,"b":2,"c": 3},...]}



Dim FSO As New FileSystemObject

Dim JsonTS As TextStream

Dim JsonText As String

Dim Parsed As Dictionary



' Read .json file

Set JsonTS = FSO.OpenTextFile("example.json", ForReading)

JsonText = JsonTS.ReadAll

JsonTS.Close



' Parse json to Dictionary

' "values" is parsed as Collection

' each item in "values" is parsed as Dictionary

Set Parsed = JsonConverter.ParseJson(JsonText)



' Prepare and write values to sheet

Dim Values As Variant

ReDim Values(Parsed("values").Count, 3)



Dim Value As Dictionary

Dim i As Long



i = 0

For Each Value In Parsed("values")

  Values(i, 0) = Value("a")

  Values(i, 1) = Value("b")

  Values(i, 2) = Value("c")

  i = i + 1

Next Value



Sheets("example").Range(Cells(1, 1), Cells(Parsed("values").Count, 3)) = Values