Excel to json and back

Before you start..

This is by far the most popular topic on this site. If you want to ask some questions, have difficulty with the downloads, or if this is not what you expected to find here, please leave a comment or ask a question on our Forum gadget to help improve the usefulness of the material.  

If you are interested in excel to JSON or JSON to excel conversion then this is the right place. It’s also the right place if you are wondering how to represent objects which are more than two dimensions in VBA, whether or not JSON conversion is involved.  

Using JSON with Excel

Here you can learn how to create and read JSON directly to and from Excel, or use the javaScript-like object (cJobject) to work with JSON  data directly in Excel, but first ;

Here’s a quick primer on how JSON is handled once parsed from a JSON string.

Are you in the right place?

Normally visitors to this page have a specific JSON conversion problem. The following bullets should help guide you to a solution.

Exchange two-dimensional Spreadsheet data using JSON or some other JSON related topic. Read on – but you probably really wanted one of the categories above

Excel and JSON

In all cases, I use a custom class cJobject to both serialize and deserialize JSON. Although its purpose is about this kind of conversion, it is a useful object that can be used for all kinds of hierarchical storage. See Rest Results Explorer for visualizing a cJobject. The Data Manipulation Classes used to abstract Excel data have a .Jobject property. This means that entire excel tables can be converted to cJobject and then serialized in one line of code.

Here is a very simple example using the cJobject. You will need to start with a workbook containing the required classes, cDataSet.xlsm which you can download from Download Complete Projects

Public Sub testcJobject1()
    Dim job As cJobject
    Set job = New cJobject
    
    With job
        .init Nothing, "our first cJobject"
        With .add("bill", "founder")
            With .add("kids")
                With .AddArray
                    .add , "mary"
                    .add , "janet"
                End With
            End With
        End With
    End With
    
    Debug.Print job.Serialize
    Debug.Print job.formatData
End Sub

Serializing the object gives us a nice JSON string that we can exchange with web services or another language. 

{"our first cJobject":{"bill":{"kids":["mary","janet" ]}}}

Excel-JSON-Excel

A very simple example – creating JSON representation of an Excel Sheet and cloning the whole sheet to another sheet. Normally you would JSON to exchange data with web services, but it’s this simple to use the cJobject inside Excel also.  

Option Explicit
Public Sub jobjectExample()
    Dim dSet As cDataSet, dsClone As cDataSet, jo As cJobject
    
    Set dSet = New cDataSet
    
    With dSet
        .populateData Range("orders!$a$1"), , , , , , True
        
        If .Where Is Nothing Then
            MsgBox ("No data to process")
        Else
            ' make a json object of one sheet, and the deJSon it in a clone
            Set dsClone = New cDataSet
            dsClone.populateJSON .jObject, Range("Clone!$A$1")           
            
        End If
    End With

End Sub

Want Something simpler? Take a look at Excel JSON conversion for 1 line of code to populate an Excel sheet from a web service, and 1 line to convert an entire worksheet to JSON.

What to download

All the examples contain all the classes needed for them to work and all projects can be found here. 
If you just want the main utility classes used throughout this site, in the Downloads section cDataSet.xlsm includes everything you need including some examples. 

You can also read the 3 following blog posts here

Explore more Excel to Json topics here or return home