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.

omegastripes · Accepted Answer · 2020-02-10 07:22:09Z

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.

edited Feb 10, 2020 at 7:22

answered Feb 9, 2020 at 13:48

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

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

6 Comments

project sixth Over a year ago

Thank you for answering! I did the same but got the following compile error. new Dictionary

omegastripes Over a year ago

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).

project sixth Over a year ago

It was impossible on Mac, but made on Windows! ! I wasn't telling my Mac environment, sorry. Simple English words could be json. However, character strings containing long Japanese characters or html are described as "error run time error 5 invalid procedure call or argument" and stopped at '.Write JSON.Serialize (Array (data))'

omegastripes Over a year ago

To make it possible to run on Mac you may use VBA-Dictionary. Then you have to remove the reference to "Microsoft Scripting Runtime".

omegastripes Over a year ago

I changed save to file method to support UTF-8 files encoding.

|

Paolo Valente · Accepted Answer · 2022-05-05 15:13:04Z

0

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.

answered May 5, 2022 at 15:13

Paolo Valente

1

2 Comments

ytan11 Over a year ago

This is not an answer, but a comment.

Dani3le_ Over a year ago

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related