Quick Links

Other stuff

Ask questions, get help, comment, collaborate

Site owners

  • Bruce Mcpherson
Services‎ > ‎Excel Liberation‎ > ‎

jSon

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.

Using jSon with Excel

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 to a solution.

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 heirarchical 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 cJobect. 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 webservices 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 webservices, 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 webservice, 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. 

Here are a selection of examples of projects on this site that convert to and from jSon. 
Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. See Excel, VBA , javaScript and Google for more topics