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?

[Annaat29]

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


Any suggestions?
As shown earlier, use JavaScript via ScriptControl. Works fine for quick-and-dirty parsing but can break on 64-bit Office without a workaround.

[jdelano]

Given this is just a string, you can pull the data you're interested with using JSON specifically. You know the format this allows you to chop it upas needed. It is crude of course.




' parse know string format
Dim jsonText As String
Dim fso As New FileSystemObject
Dim jFile As TextStream
Dim jsonData() As String
Dim alarmData() As String
Dim tempStr As String
Dim alarmType As String
Dim deviceID As String
Dim resetData As String
Dim whenData As String

' I have the json string in a text file for this
Set jFile = fso.OpenTextFile("f:\temp\testJSON.json")
jsonText = jFile.ReadLine
jFile.Close
Set jFile = Nothing

' the alarm data is the only interested info from the json string
jsonData = Split(jsonText, "data")

' truncate the data to what we're interested in
tempStr = jsonData(2)
tempStr = Replace(tempStr, "{", "")
tempStr = Replace(tempStr, "}", "")
tempStr = Mid(tempStr, 3) ' remove the leading ": from the string to split

alarmData = Split(tempStr, ",")

' each alarm type is split on the colon
' 0 is the title and 1 is the data, remove the extra double quot character
alarmType = Replace(Split(alarmData(0), ":")(1), Chr(34), "")
deviceID = Replace(Split(alarmData(1), ":")(1), Chr(34), "")
resetData = Replace(Split(alarmData(2), ":")(1), Chr(34), "")
whenData = Replace(Split(alarmData(3), ":")(1), Chr(34), "")

Sheet1.Cells(4, 5).Value = alarmType
Sheet1.Cells(4, 6).Value = deviceID
Sheet1.Cells(4, 7).Value = resetData
Sheet1.Cells(4, 8).Value = whenData