Is There a JSON Parser for VB6 / VBA?
Check out JSON.org for an up-to-date list (see bottom of main page) of JSON parsers in many different languages. As of the time of this writing, you'll see a link to several different JSON parsers there, but only one is for VB6/VBA (the others are .NET):
- When I tried to download the zip file, Windows said the data was corrupt. However, I was able to use 7-zip to pull the files out. It turns out that the main "folder" in the zip file isn't recognized as a folder by Windows, by 7-zip can see the contents of that main "folder," so you can open that up and then extract the files accordingly.
The actual syntax for this VB JSON library is really simple:
Dim p As ObjectSet p = JSON.parse(strFormattedJSON)'Print the text of a nested property 'Debug.Print p.Item("AddressClassification").Item("Description")'Print the text of a property within an array 'Debug.Print p.Item("Candidates")(4).Item("ZipCode")- Note: I had to add the "Microsoft Scripting Runtime" and "Microsoft ActiveX Data Objects 2.8" library as references via Tools > References in the VBA editor.
- Note: VBJSON code is actually based on a google code project vba-json. However, VBJSON promises several bug fixes from the original version.
Building on ozmike solution, which did not work for me (Excel 2013 and IE10).The reason is that I could not call the methods on the exposed JSON object.So its methods are now exposed through functions attached to a DOMElement.Didn't know this is possible (must be that IDispatch-thing), thank you ozmike.
As ozmike stated, no 3rd-party libs, just 30 lines of code.
Option ExplicitPublic JSON As ObjectPrivate ie As ObjectPublic Sub initJson() Dim html As String html = "<!DOCTYPE html><head><script>" & _ "Object.prototype.getItem=function( key ) { return this[key] }; " & _ "Object.prototype.setItem=function( key, value ) { this[key]=value }; " & _ "Object.prototype.getKeys=function( dummy ) { keys=[]; for (var key in this) if (typeof(this[key]) !== 'function') keys.push(key); return keys; }; " & _ "window.onload = function() { " & _ "document.body.parse = function(json) { return JSON.parse(json); }; " & _ "document.body.stringify = function(obj, space) { return JSON.stringify(obj, null, space); }" & _ "}" & _ "</script></head><html><body id='JSONElem'></body></html>" Set ie = CreateObject("InternetExplorer.Application") With ie .navigate "about:blank" Do While .Busy: DoEvents: Loop Do While .readyState <> 4: DoEvents: Loop .Visible = False .document.Write html .document.Close End With ' This is the body element, we call it JSON:) Set JSON = ie.document.getElementById("JSONElem")End SubPublic Function closeJSON() ie.QuitEnd FunctionThe following test constructs a JavaScript Object from scratch, then stringifies it.Then it parses the object back and iterates over its keys.
Sub testJson() Call initJson Dim jsObj As Object Dim jsArray As Object Debug.Print "Construction JS object ..." Set jsObj = JSON.Parse("{}") Call jsObj.setItem("a", 1) Set jsArray = JSON.Parse("[]") Call jsArray.setItem(0, 13) Call jsArray.setItem(1, Math.Sqr(2)) Call jsArray.setItem(2, 15) Call jsObj.setItem("b", jsArray) Debug.Print "Object: " & JSON.stringify(jsObj, 4) Debug.Print "Parsing JS object ..." Set jsObj = JSON.Parse("{""a"":1,""b"":[13,1.4142135623730951,15]}") Debug.Print "a: " & jsObj.getItem("a") Set jsArray = jsObj.getItem("b") Debug.Print "Length of b: " & jsArray.getItem("length") Debug.Print "Second element of b: "; jsArray.getItem(1) Debug.Print "Iterate over all keys ..." Dim keys As Object Set keys = jsObj.getKeys("all") Dim i As Integer For i = 0 To keys.getItem("length") - 1 Debug.Print keys.getItem(i) & ": " & jsObj.getItem(keys.getItem(i)) Next i Call closeJSONEnd Suboutputs
Construction JS object ...Object: { "a": 1, "b": [ 13, 1.4142135623730951, 15 ]}Parsing JS object ...a: 1Length of b: 3Second element of b: 1,4142135623731 Iterate over all keys ...a: 1b: 13,1.4142135623730951,15
Hopefully this will be a big help to others who keep on coming to this page after searching for "vba json".
I found this page to be very helpful. It provides several Excel-compatible VBA classes that deal with processing data in JSON format.