CODEWITHSUNDEEP
jsonvba
Priyanka Das
Priyanka Das

Reputation: 13

Modify a JSON value using VBA

I have below JSON structure to modify. I have expected values stored in excel as rows and columns and the template saved in a File

The part of template looks like:

"name" : [
          {
           "use" : "official",
            "text" : "MS JOHN DOE II",
            "family" : "DOE",
            "given" : [
              "JOHN"
            ],
            "prefix" : [
              "MS"
            ],
            "suffix" : [
              "II"
            ],
            "period" : {
              "end" : "2019-01-22T14:33:53Z"
            }
          }
        ]

Prefix in above template is an array with [] I am writing below code to update the Prefix in above template as below

resource ("resource")("name")(1)("prefix") = Cstr(ws.Cells(i,j))

But the output JSON has prefix as normal object and not as Array

Output :  "prefix" : "ms"

How to write the correct syntax? Please help and guide!

Upvotes: 1

Views: 1226

Answers (1)

QHarr
QHarr

Reputation: 84465

It seems like you need to modify an item in a collection. To do this you need to remove that item and replace it with updated value. 

Option Explicit
Public Sub demo()
    Dim col As Collection
    Set col = New Collection
    col.Add "Orange"
    Debug.Print col.Item(1)
    col.Remove 1
    col.Add "Banana"
    Debug.Print col.Item(1)
End Sub

Something like:

resource ("resource")("name")(1)("prefix").Remove 1
resource ("resource")("name")(1)("prefix").Add Cstr(ws.Cells(i,j))

Upvotes: 2

Related Questions