json - 从 excel 添加到现有 JSON

Question

我想使用 vba 在 excel 中添加这个 JSON 文件。

所以我有这个 JSON 文件

{
    "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"

                }
            }]
        }
    }]
}

我想在它下面添加另一个“用户”，所以它看起来像

{
    "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"

                }
            }]
        }
    }]
}

这是我目前拥有的

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

所有这一切都是将它添加到现有 JSON 之下，而不是全部连接到它。

我将如何使用 excel 中的信息在当前用户下方添加另一个“用户”

标签： jsonvbaexcel