Excel to JSON Conversion

Hi Team,

I have a JSON file which I already converted to Excel but unable to convert it back to JSON.

I have come up with the below code but its just creating json object based on the columns I have in the excel.

`Option Explicit

Sub export_in_json_format()

Dim fs As Object
Dim jsonfile
Dim rangetoexport As Range
Dim rowcounter As Long
Dim columncounter As Long
Dim linedata As String

' change range here
Set rangetoexport = Sheet2.Range("A1:AA3")

Set fs = CreateObject("Scripting.FileSystemObject")
' change dir here

Set jsonfile = fs.CreateTextFile("C:\Users\XXX\" & "jsondata.json", True)

linedata = "{""Output"": ["
jsonfile.WriteLine linedata
For rowcounter = 2 To rangetoexport.Rows.Count
    linedata = ""
    For columncounter = 1 To rangetoexport.Columns.Count
        linedata = linedata & """" & rangetoexport.Cells(1, columncounter) & """" & ":" & """" & rangetoexport.Cells(rowcounter, columncounter) & """" & ","
    Next
    linedata = Left(linedata, Len(linedata) - 1)
    If rowcounter = rangetoexport.Rows.Count Then
        linedata = "{" & linedata & "}"
    Else
        linedata = "{" & linedata & "},"
    End If

    jsonfile.WriteLine linedata
Next
linedata = "]}"
jsonfile.WriteLine linedata
jsonfile.Close

Set fs = Nothing

End Sub

My input is:

These are the columns I have in my excel.

abc| xxx | 1 Year | true | Residential address,Business address | All Family | Members | Nominee Name D | No | Trustee A | +60 | Add1| Ms | testing |

My expected Output is:

`[{ "TestCaseID": "abc", "Product": "xxx", "YearOption": "1 Year", "BasicInfo": { "OwnerIsInsured": "true", "AddOtherInformation": "Residential address,Business address", "OwnerInfo": { "lastName": "All Family", "middleName": "Members", "nomineeInfo": [{ "fullName": "Nominee Name D" }

        ],

        "trustee": "No",
        "trusteeInfo": [{
            "fullName": "Trustee A"
        }],
        "areaCode": "+60",
        "residentaladdress": "Add1"
    },
    "InsuredsInfo": [{
        "title": "Ms.",
        "fullName": "testing"
    }]
}

}]`

This is just a sample. JSON structure should be created dynamically based on the columns/rows in the excel.

Thanks!

VBA-tools / VBA-JSON

Excel to JSON Conversion #182