ranjithkumar10

Excel to json - nested structure

Sep 26th, 2016
74
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VB.NET 0.66 KB | None | 0 0
  1. Public Sub exceltojson()
  2. Dim rng As Range, items As New Collection, myitem As New Dictionary, subitem As New Dictionary, i As Integer, cell As Variant
  3. Set rng = Range("A2:A3")
  4. 'Set rng = Range(Sheets(2).Range("A2"), Sheets(2).Range("A2").End(xlDown)) use this for dynamic range
  5. i = 0
  6. For Each cell In rng
  7. Debug.Print (cell.Value)
  8. myitem("name") = cell.Value
  9. myitem("email") = cell.Offset(0, 1).Value
  10. myitem("phone") = cell.Offset(0, 2).Value
  11. subitem("id") = cell.Offset(0, 3).Value
  12. myitem.Add "contact", subitem
  13. items.Add myitem
  14. Set myitem = Nothing
  15. Set subitem = Nothing
  16. i = i + 1
  17. Next
  18. Sheets(2).Range("A4").Value = ConvertToJson(items, Whitespace:=2)
  19. End Sub
Add Comment
Please, Sign In to add comment