How to parse json GET request with VBA?

[waimea]

I HAVE X-POSTED THIS AT: https://www.mrexcel.com/board/threads/how-to-parse-json-get-request-with-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").

[waimea]

In this link https://medium.com/swlh/excel-vba-parse-json-easily-c2213f4d8e7a there is code for a parsing engine in VBA but I don't undertand the code provided.

1. Parse JSON data
2. Stringify JSON data

[waimea]

{
"userId": 1,
"id": 1,
"title": "delectus aut autem",
"completed": false
}




{
"id": 1,
"title": "hello"
}


This is a simpler json, I would like to be able to parse the string, I have looked at the code in the above post but I do not fully understand it.

I am playing around with select cases but I am not getting anything that seems right or even close to right.

[waimea]

I would really like to be able to parse simple json!

Any suggestions?