Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Public Sub exceltojson()
- Dim rng As Range, items As New Collection, myitem As New Dictionary, subitem As New Dictionary, i As Integer, cell As Variant
- Set rng = Range("A2:A3")
- 'Set rng = Range(Sheets(2).Range("A2"), Sheets(2).Range("A2").End(xlDown)) use this for dynamic range
- i = 0
- For Each cell In rng
- Debug.Print (cell.Value)
- myitem("name") = cell.Value
- myitem("email") = cell.Offset(0, 1).Value
- myitem("phone") = cell.Offset(0, 2).Value
- subitem("id") = cell.Offset(0, 3).Value
- myitem.Add "contact", subitem
- items.Add myitem
- Set myitem = Nothing
- Set subitem = Nothing
- i = i + 1
- Next
- Sheets(2).Range("A4").Value = ConvertToJson(items, Whitespace:=2)
- End Sub
Add Comment
Please, Sign In to add comment