Excel-jSon-Excel in one line of code

Excel-jSon-Excel in one line of code

On the ramblings site, I set myself the challenge to write some classes that could be invoked to convert an entire Worksheet to jSon, or alternatively, to consume a webservice and populate and entire worksheet in one line of code


These examples, and the classes they use can be downloaded from the ramblings site, and are in included in cDataSet.xlsm. 

Here we go
Get a jSon response from a web service and populate a worksheet with the result.

    Dim dSet As New cDataSet, cb As New cBrowser, jo As New cJobject
    dSet.populateJSON jo.deSerialize( _
        cb.httpGET( _
            “http://dl.dropbox.com/u/14196218/files/jSon1.html” _
            ) _
        ).Child(“cDataSet”), Range(“json1!$a$1”)

create and print a jSon string from a worksheet.

    Dim dSet As New cDataSet

    Debug.Print dSet.populateData( _
        Range(“jSon2!$a$1”), , , , , , True _
        ).jObject.Serialize(True)

The jSon string both created and consumed looks like this – or see it online

  {
   “cDataSet”:[      {
         “Customer”:”Acme”,
         “Contact”:”john”,
         “Total”:”100″,
         “Country”:”US”
      },
      {
         “Customer”:”Smiths”,
         “Contact”:”fred”,
         “Total”:”460″,
         “Country”:”UK”
      },
      {
         “Customer”:”Jones”,
         “Contact”:”joe”,
         “Total”:”24″,
         “Country”:”US”
      },
      {
         “Customer”:”Renault”,
         “Contact”:”Marie”,
         “Total”:”536″,
         “Country”:”FRANCE”
      },
      {
         “Customer”:”Schneider”,
         “Contact”:”Hans”,
         “Total”:”1334″,
         “Country”:”Germany”
      }
    ]
 }

The Excel sheet that gets created looks like this

Hopefully you will see how that works from some of the other jSon examples on the ramblings site, or you can ask me on the forum

Author: brucemcp

Leave a Reply

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