How to Convert Excel Data to JSON Using VBA

11 thoughts on “How to Convert Excel Data to JSON Using VBA”

1. Hi Eric, Is it possible to export the json code and write it to an external file? How would I be able to do this? Thanks for your help.

   Reply

2. Thanks.

   Reply

3. Thanks Eric. You make my day:)

   Reply

4. thanks a lot eric but im getting an overflow error.
   In my case i have 153.972 rows and 40 columns and all are numbers instead of names besides the first row.
   How can i fix this? much appreciated…

   Reply

5. Thanks Eric…I like your code and thanks for sharing it. I just wanted to let you know that I changed the getValuesRange function because I have a cleaner way to get this same range. It is a little hard coded in terms on columns which for my work is fine but I thought that I would share it with your. Thanks:

   Function getValuesRange2(sheet As String, StartingCell As String) As Range
   
   ' Row variables
   
   Dim rngStart As Range
   
   Dim rngWholeRange As Range

   With ThisWorkbook.Sheets(sheet)
   
   Set rngStart = .Range(StartingCell)
   
   Set getValuesRange2 = .Range(StartingCell, .Range(StartingCell).End(xlDown).Cells(1, 9))
   
   End With

   End Function

   Reply

6. CAN we have code for
   Ex: If First Row has 1 Cell (With data)
   Then From 2 has A,b COLUMNS data and 3 rows has A,b HAS data
   And 4 TH row has 1 coulumn with data

   I want to print row 1 has header like Name. and 2,3 rowas to display in flower brackets
   ["nAME(row1)": {
   "STREET"(row2 (Acolumn) : "123"(row2 b column)
   "Address" (row3 (Acolumn): "abc"(row3 b column)
   },
   "Name2":{ same as above)

   I need code for if row has 1 column that should be header, row has 2 columns that should be string row has >2 columns that should be displayed as Array

   please help me…waiting for your valuable reply to my mail venkatrao.vce@gmail.com

   Reply

7. Thank you Eric , you saved me 🙂 , what if i have more than 1 sheet in same workbook how can i get all sheets in one button and in same cell ?

   Reply

8. If there is integer, how to add integer column without using " " . Thank you.

   Reply

9. Hey Eric, Thanks, this tutorial was very helpful Can you also post a video of doing a reverse action? I want to convert the JSON data to excel and cannot find a good tutorial for it. Thanks

   Reply

10. You help me alot. Thanks

    Reply

11. Thank you Eric. This tutorial has been very helpful for my interview. I’m very grateful.

    Reply