Adding to existing JSON from excel

Question

I want to add to this JSON file in excel using vba.

So I have this JSON file

{
    "root": [{
        "STATUS_RESPONSE": {
            "STATUS": {
                "STATUS": {
                    "OWNER": "root"
                }
            },
            "REQ_ID": "00000",
            "RESULT": [{
                "USER": {
                    "BUSINESS_ID": "A",
                    "USER_NUMBER": "45",
                    "LANGUAGE": "F"
                }
            },
            {
                "USER_SESSION": {
                    "USER_ID": "0000001009",
                    "HELP_URL": "http://google.com"
                }
            },
            {
                "USER_ACCESS": {
                    "SERVICES_ROLE": "true",
                    "JOURNALLING": "true"

                }
            }]
        }
    }]
}

I want to add another "USER" right below it so it looks like

{
    "root": [{
        "STATUS_RESPONSE": {
            "STATUS": {
                "STATUS": {
                    "OWNER": "root"
                }
            },
            "REQ_ID": "00000",
            "RESULT": [{
                "USER": {
                    "BUSINESS_ID": "A",
                    "USER_NUMBER": "45",
                    "LANGUAGE": "F"
                }
            },
            {     
                 "USER": {
                    "BUSINESS_ID": "B",
                    "USER_NUMBER": "55",
                    "LANGUAGE": "E"
                }
            },
            {
                "USER_SESSION": {
                    "USER_ID": "0000001009",
                    "HELP_URL": "http://google.com"
                }
            },
            {
                "USER_ACCESS": {
                    "SERVICES_ROLE": "true",
                    "JOURNALLING": "true"

                }
            }]
        }
    }]
}

This is what I have currently

Private Sub CommandButton3_Click()
Dim z As Integer, items As New Collection, myitem As New Dictionary
Dim rng As Range
Dim cell As Variant
Dim FSO As New FileSystemObject
Dim JsonTS As TextStream
Set JsonTS = FSO.OpenTextFile("test.json", ForReading)
JsonText = JsonTS.ReadAll
JsonTS.Close
Set JSON = ParseJson(JsonText)

Set rng = Range("A5")

z = 0
For Each cell In rng
    myitem("BUSINESS_ID") = cell.Offset(0, 1).Value
    myitem("USER_NUMBER") = cell.Offset(0, 2).Value
    myitem("LANGUAGE") = cell.Offset(0, 3).Value
    items.Add myitem
    Set myitem = Nothing
z = z + 1
Next

myfile = Application.ActiveWorkbook.Path & "\test.json"
Open myfile For Output As #1
Print #1, ConvertToJson(myitem, Whitespace:=2)
MsgBox ("Exported to JSON file")
Close #1

End Sub

All this does is add it below the existing JSON and is not connected to it all.

How would I go about add another "USER" right below the current one with the information from excel

You create the parsed JSON object (which is what you need to modify), but then you do nothing with it. Within that object "RESULT" is already an existing collection - so add each `myItem` to that. And you can't set `myItem` to nothing if you loop over more than one row, or how will you add the next item? — Tim Williams, Jun 08 '18 at 16:40
A similar thing I did was just JSON("root")(1)("STATUS_RESPONSE")("RESULT").Add myItem which added it to the result object at the bottom — Kevin Jones, Jun 08 '18 at 18:19
Also, any idea if there was like more keys and values inside that "USER" that I don't modify in the excel, how can I add them as well? — Kevin Jones, Jun 08 '18 at 18:21
If you want to *modify* an existing user that's very different from adding a new one: you'd need to loop over the collection of USER objects and identify which one needed to be updated. — Tim Williams, Jun 08 '18 at 18:24
What I meant was let say my original user had two field below LANGUAGE called for instance, FNAME and LNAME, but I don't modify them in the excel, how can I transfer those two to the new object USER I just made below it — Kevin Jones, Jun 08 '18 at 18:32
I have, it would be easier that way but was wondering if there a way to do it without having too? — Kevin Jones, Jun 08 '18 at 18:56
You already have the JSON object loaded, and you know how to access the existing keys, so read from the existing user object and add the keys and values to your new user. — Tim Williams, Jun 08 '18 at 19:03
You need to clone the collection by copying each element from original within loop, and inserting the new element at the necessary position, then assign new cloned object instead of original. — omegastripes, Jun 08 '18 at 19:23
@TimWilliams how would I access the key, I know how to do values but for the key I can't figure it out — Kevin Jones, Jun 08 '18 at 19:26
`For each k in JSON("root")(1)("STATUS_RESPONSE")("RESULT")(1)("USER"): debug.print k, JSON("root")(1)("STATUS_RESPONSE")("RESULT")(1)("USER")(k)` — Tim Williams, Jun 08 '18 at 19:28
That just gives me the values for each thing inside and not the keys — Kevin Jones, Jun 08 '18 at 19:32
Take a look at [this answer](https://stackoverflow.com/a/46245469/4717755) to see if it helps — PeterT, Jun 08 '18 at 20:25

Adding to existing JSON from excel

0 Answers0