XML and JSON conversion in VBA

If you are familiar with this blog and the Excel Liberation site, you’ll know that there is a pretty significant focus on getting stuff in and out of Excel. Ease of JSON and javaScript like object manipulation has always been a big gap in Excel, so I make heavy use of cJobject for playing around with jSon.

I never did too much with XML, because its kind of native in Excel and there’s plenty of stuff out there already. However, I didn’t like the idea of manipulating data differently if it came in as XML compared to JSON, so I’ve added XML parsing to cJobject. In other words, you can use it to create jSon from XML, and traverse the XML originated object as if it were JSON.

Here’s a one liner to create jSon from XML.

jsonString = xmlStringToJobject (xmlString).stringify

So now you can just treat the XML data the same way as JSON data, by creating a javaScript like object like this

set job = xmlStringToJobject (xmlString)

For more about how this works, see XML to JSON with VBA for a VBA native version. For a little sidebar, I also provided a lazy alternative that delegates the XML to JSON conversion to Google Apps Script by posting XML data to a public GAS webapp and getting back the JSON, but in this article we’ll stick to the VBA native version.

Rest Excel library

This is a large library of APIs that can generally be called as a one liner to populate an Excel Sheet, or otherwise provide structured access to API results. Up till now, it only handled JSON responses, but I’ve added a capability to automatically detect XML and parse that to a cJobject as well.

An example

The open weather API provides both XML and JSON format. In this example, we’ll get the XML version and convert it to JSON inside VBA. Thereafter, we can populate an excel sheet just like every other entry in the REST-EXCEL library.

Here, we provide a column of place names, along with some column headings that correspond to fields in the XML data. The API populates the columns from the XML data after it has converted it to JSON, as below

The library entry

Every API known by the rest-excel library has an entry describing how to process it. Here is the entry for the XML version of the open weather API. This new parameter, resultsFormat with the value erAUTO, indicates that it should detect the format and convert if necessary

        With .add(“open weather xml”)
            .add “restType”, erQueryPerRow
            .add “url”, “http://api.openweathermap.org/data/2.5/weather?q=”
            .add “results”, “current”
            .add “treeSearch”, True
            .add “ignore”, vbNullString
            .add “append”, “&mode=xml”
            .add “resultsFormat”, erAUTO
        End With

The code

This is just pretty much a one liner as usual

    With generalDataSetQuery(“weather”, “open weather xml”, “place”)
        .tearDown
    End With

And that’s all there is to it. You can download this in the cDataSet.xlsm workbook from Excel Liberation, ot if you are feeling more adventurous you can get the code right off the web into your workbook as described here.

For more stuff like this see Excel Liberation

(function() { var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true; po.src = ‘https://apis.google.com/js/plusone.js’; var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(po, s); })();

Author: bm082975

Leave a Reply

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