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’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.
- REST queries to bring data into Excel from a variety of sources. Try Rest to Excel library or get started with How to populate Excel from JSON
- Understand how multidimensional JSON data could be worked with inside Excel. Try How to use cJobject
- See how to make more declarative VBA apps, by defining the ‘what’ using a JSON manifest. Try Data-driven VBA apps with JSON
- Get data out of Google Docs. Try Get Data From Google Docs or Google docs import with oauth2
- Format Excel data for Google Maps or Earth, or to make geoCoding queries. Try Integrating Excel with Maps and Earth
- Any Excel thing to a treeview in one line of code. Try Create a treeview from json
- Use JSON to pass arguments in VBA and deal with optional values. See this blog post Passing complex arguments with default values in VBA – like jQuery $.extend()
- To manipulate data like rshapes melt. Simple implementation of R- melt
- XML to JSON with VBA
- Delegating xml to JSON conversion to GAS
- Deduce column headings from rest Query
- OAUTH2 and VBA
- Oauth2 and Excel-Rest library
- cJobject deep dive
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.
Serializing the object gives us a nice JSON string that we can exchange with web services or another language.
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