How to generate multiple json in vertical column in Excel VBA?

Question

Using excel macro The first line is the json file name, and the second and subsequent columns are the values.

With the first column as the key to the json object, only the values ​​in the second column and only the values ​​in the third column.

How do I generate json in batches in the same directory as the Excel respectively?

For example, column B is the value of column A as a key, the value of column B and b1.json. Column C uses the value of Column A as a key and the value of Column C and c1.json

For example, when the completed json has a description up to column C,

b1.json

[{
a2: b2,
a3: b3,
a4: b4,
}]

c1.json

[{
a2: c2,
a3: c3,
a4: c4,
}]

And want to be generated.

Answer 1

Here is VBA example showing how the files could be created. Import JSON.bas module from VBA JSON parser into the VBA project for JSON processing, and include a reference to "Microsoft Scripting Runtime" (take a look here how to import module and add reference).

Put the below code in a standard module:

Option Explicit

Sub test()

    With ActiveSheet.Cells(1, 1).CurrentRegion
        If .Cells.Count < 4 Then
            MsgBox "No data"
            Exit Sub
        End If
        Dim source
        source = .Value
    End With
    Dim i
    For i = 2 To UBound(source, 2)
        Dim data
        Set data = New Dictionary
        Dim j
        For j = 2 To UBound(source, 1)
            data(source(j, 1)) = source(j, i)
        Next
        saveTextToFile _
            JSON.Serialize(Array(data)), _
            ThisWorkbook.path & "\" & source(1, i) & ".json", _
            "UTF-8"
    Next
    MsgBox "Completed"

End Sub

Sub saveTextToFile(content, filePath, charset)

    With CreateObject("ADODB.Stream")
        .Type = 2 ' adTypeText
        .Open
        .charset = charset
        .WriteText content
        .Position = 0
        .Type = 1 ' TypeBinary
        .SaveToFile filePath, 2
        .Close
    End With

End Sub

The source data I tested code with:

The output is as follows:

file1.json

[
    {
        "prop1": "Alice",
        "prop2": "Bob",
        "prop3": "Charlie",
        "prop4": "Eve",
        "prop5": "Dan"
    }
]

file2.json

[
    {
        "prop1": "Tomatoes",
        "prop2": "Bananas",
        "prop3": "Watermelons",
        "prop4": "Apples",
        "prop5": "Grapefruit"
    }
]

file3.json

[
    {
        "prop1": "Russia",
        "prop2": "Canada",
        "prop3": "USA",
        "prop4": "China",
        "prop5": "Brazil"
    }
]

BTW, the similar approach applied in other answers.

Answer 2

This is the exact problem I'm having but I can't seem to import the JSON.bas file. It throws an error saying the file is too complex.