VBA-JSON: Add entry to Existing File

Question

I'm using excel to modify existing JSON with the VBA-JSON library and I'm effectively trying to do what .push from javascript does, adding another entry to the nested array.

There's this previously posted question which illustrates what I'm trying to do but in the scope of creating a JSON file.

VBA-JSON Create nested objects

The accepted answer while helpful, doesn't show me how to add to what I already have without rebuilding it entirely. I can picture a mess of loops to parse my existing file into strings, then do something similar to what is in the post. I'm curious if there's another way to do it.

'download json

Set json = JsonConverter.ParseJson(H.responseText)

Debug.print json(1)("entries")(1)("Date")
'2019-09-25

json(1)("entries")(2)("Date") = "2019-09-26"
'error 9 Subscript out of Range

'upload json

[
    {
        "Entries": [
            {
                "Date": 2019-09-25,
                "a": 1,
                "b": 2
            }
        ]
    }
]

I'm able to access and modify existing entries but not add to it. I'm guessing this is because the library parses it as a defined array rather than variable? Any ideas on a simple solution or will I have to deconstruct my file into dictionaries/collections and rebuild it first?

JNevill · Accepted Answer · 2019-09-25 18:17:23Z

3

VBA Json is really just a cheap (and effective) converter/deconverter of a json string into a nested set of dictionaries and collections. So you don't have to "deconstruct [your] file into dictionaries/collections and rebuild it" since... it already is. This is why it's necessary to add in the Microsoft Scripting Runtime library to use it.

The struggle you are dealing with is that you are treating every object in your json object as a dictionary. Instead you want to add(pop) to the entries collection. A good way to keep these separate is that in VBA JSON (as far as I've seen) if an object is a key/value pair, then it's a Dictionary and if it's just an object without a key then it's a Collection. I'm certain there is more nuance there, but it works well in this scenario.

Consider:

Sub test()
    Dim Json As Object
    Set Json = JsonConverter.ParseJson("[{""Entries"":[{""Date"": 2019-09-25,""a"": 1,""b"": 2}]}]")

    Dim entry1 As Object
    Set entry1 = Json(1)("Entries")

    Stop '<--inspect Locals and see that we have a Collection here

    'Add to the collection (different then adding to a dictionary)
    'We will add a dictionary to the collection though
    Json(1)("Entries").Add New Dictionary

    'Now add to the dictionary.
    Json(1)("Entries")(2).Add "Date", "2019-09-26"

    Stop

    Debug.Print JsonConverter.ConvertToJson(Json)

    'Alternatively you could create the dictionary and add your keys, then `pop` your entries
    Dim Entry As Dictionary
    Set Entry = New Dictionary
    Entry.Add "Date", "2019-09-26"
    Entry.Add "a", "2"
    Entry.Add "b", "3"

    Json(1)("Entries").Add Entry

    Debug.Print JsonConverter.ConvertToJson(Json)
End Sub

This should spit out what you are after (I think):

[{"Entries":[{"Date":"2019-09-25","a":1,"b":2},{"Date":"2019-09-26"}]}]

I guess another way to think through this is that if we want to add a new array to our Entries then we have to add a new Dictionary object to the Entries collection. Then we can add into the dictionary our key/value pairs like Date, a, and b.

edited Sep 25, 2019 at 18:17

answered Sep 25, 2019 at 18:05

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sigil Over a year ago

This is perfect! Thank you for the explanation and code. I'd upvote this but I can't yet. The edit with the alternative is useful reference too, that's what I was thinking after seeing your initial response.

JNevill Over a year ago

Great! I'm glad you saw the edit. I was rereading the answer and I was like... "Duh.. this would make more sense if I created the dictionary first, then added it to the collection".

Marcelo Scofano Diniz Over a year ago

@JNevill and OP: Brilliant approach, so obvious that it was hidden in plain view, at least for me. Thanks.

Marcelo Scofano Diniz Over a year ago

And to add 0.02$: Json(1)("Entries")(2).Add "Dates", array("2019-09-26","2019-01-23","2020-01-24")

Collectives™ on Stack Overflow

VBA-JSON: Add entry to Existing File

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related