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?

[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