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.

  2. 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…

  3. 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

  4. 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

  5. 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 ?

  6. 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

Leave a Reply

Your email address will not be published. Required fields are marked *