Create JSON from excel range

[YasserKhalil]

Hello everyone

I am trying to create JSON from the excel range. The range has three columns like that

pr1 hw LC
pr1 hw Repl
pr1 web LC
pr1 web Repl

This is my try but I am lost in how to use nested dictionaries

Code: Select all

Sub TestJSON()
    Dim items As New Collection, dic As New Dictionary, rng As Range, cel As Range, i As Integer

    Set rng = Sheets(1).Range("A1:A4")
    i = 0
Dim sKey As String

    For Each cel In rng
        sKey = cel.Value & Chr(165) & cel.Offset(, 1).Value
        If Not dic.Exists(sKey) Then dic.Add sKey, cel.Offset(, 1).Value & "|" & cel.Offset(, 2).Value
        
        i = i + 1
    Next cel
    
    Dim ky
    
    For Each ky In dic.Keys
        'Debug.Print ky
        Debug.Print dic.Item(ky)
    Next ky
    

    Debug.Print ConvertToJson(dic, Whitespace:=2)
End Sub

I expect to get such a result

Code: Select all

{
  "pr1": [
    {
      "hw": [
        "LC",
        "Repl"
      ]
    },
    {
      "web": [
        "LC",
        "Repl"
      ]
    }
  ]
}