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").

[waimea]

In this link https://medium.com/swlh/excel-vba-pa...y-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?