1

I want to modify a 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 modify just the "BUSINESS_ID"

Then I can export to the same JSON file using this

   Private Sub CommandButton2_Click()
Dim rng As Range, items As New Collection, myitem As New Dictionary, i As Integer, cell As Variant, myfile As String
Dim FSO As New FileSystemObject
Dim buss As String
Dim JsonTS As TextStream
Set rng = Range("A2")
Set JsonTS = FSO.OpenTextFile("test.json", ForReading)
JsonText = JsonTS.ReadAll
JsonTS.Close
Set JSON = ParseJson(JsonText)
JSON("root")(1)("STATUS_RESPONSE")("RESULT")(1)("USER")("BUSINESS_ID") = Sheets(1).Cells(2, 1).Value
buss = JSON("root")(1)("STATUS_RESPONSE")("RESULT")(1)("USER")("BUSINESS_ID")
myfile = "test.json"
Open myfile For Output As #1
Write #1, buss
Close #1
End Sub

I can edit the cell and this would replace the JSON file but it takes away the whole structure from the JSON file above.

I get something similar to this like in the json file if I change the business id to C:

"C"

Is there a way that I can just modify the thing I need in the existing file without everything else disappearing

4
  • You should be exporting the whole JSON object, not just one part of it. Did you delete your previous queston? Commented Jun 6, 2018 at 17:54
  • How would I do the whole JSON object Commented Jun 6, 2018 at 17:57
  • 1
    Write #1, JsonConverter.ConvertToJson(JSON, Whitespace:=2) github.com/VBA-tools/VBA-JSON Commented Jun 6, 2018 at 18:03
  • ...wow mind blown, put it as the answer so I can reward you Commented Jun 6, 2018 at 18:08

1 Answer 1

2

You should be exporting the whole JSON object, not just one part of it.

Write #1, JsonConverter.ConvertToJson(JSON, Whitespace:=2)
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.