How to parse json GET request with VBA?

[waimea]

I HAVE X-POSTED THIS AT: https://www.mrexcel.com/board/thread...h-vba.1154905/ WITHOUT ANY ANSWERS!


Hi,

I am trying to parse a JSON GET request without using an external library.

I have the following code at the moment:

Sub getJSON()
    Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String
    myurl = "http://localhost:5000/api/v1/battery?limit=1"
    xmlhttp.Open "GET", myurl, False
    xmlhttp.send
    Debug.Print (xmlhttp.responseText)
    With Sheets("JSON")
         .Cells(1, 1).Value = xmlhttp.responseText
         .Cells(2, 1).Value = Split(xmlhttp.responseText)
    End With
End Sub

The JSON string I get is the following:

{"success":true,"count":1,"pagination":{"next":{"page":2,"limit":1}},"data":[{"_id":"5ec3d0c12cf31b3dfc48d540","data":{"alarmType":"Low Battery","devideId":"TEST123","reset":false},"created":"2020-05-19T12:27:45.999Z","__v":0}]}

I want to parse the JSON string into something useful.

AlarmType	DeviceID	Reset
Low Battery	TEST123	False

I have googled and found that some people use a dictionary and/or scripting engine (I don't know how to use either of them").