Best IT Recipes

Is there a JSON Parser for VB6 / VBA?

I am trying to use a web service in VB6. The service I manage can currently return a SOAP / XML message or JSON. I have a very difficult time figuring out if the VB6 SOAP type (version 1) can handle the returned object - unlike simple types like string , int , etc. So far I can’t understand what I need to get VB6 to play with the returned objects.

So, I thought I could serialize the response in a web service as a JSON string. Is there a JSON parser for VB6?

+38
json vba serialization web-services vb6
May 6 '10 at 3:03 a.m.
source share
14 answers

Check out JSON.org for an updated list (see bottom of the main page) of JSON parsers in different languages. At the time of this writing, you will see a link to two different JSON parsers:

  • Vb-json

    • When I tried to download the zip file, Windows reported that the data was corrupted. However, I was able to use 7-zip to pull out the files. It turns out that the main "folder" in the zip file is not recognized as a Windows folder, and 7-zip can see the contents of this main "folder", so you can open it and then extract the files accordingly.

    • The actual syntax for this VB JSON library is very simple:

       Dim p As Object Set 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" libraries as links through "Tools"> "Links" in the VBA editor.
    • Note. The VBJSON code is actually based on the google vba-json code project. However, VBJSON promises fixed several bugs from the original version.
  • Pw.json
    • This is actually a library for VB.NET , so I did not spend much time studying it.
+39
May 6 '10 at 3:41 a.m.
source share

Building an ozmike solution that didn't work for me (Excel 2013 and IE10). The reason is that I could not call the methods of the public JSON object. Thus, its methods are now displayed through functions related to DOMElement. I did not know that this is possible (it must be an IDispatch thing), thanks ozmike.

As ozmike stated, there are no third-party libraries, only 30 lines of code.

 Option Explicit Public JSON As Object Private ie As Object Public 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 Sub Public Function closeJSON() ie.Quit End Function

The following test constructs a JavaScript object from scratch, and then builds 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 closeJSON End Sub

exits

 Construction JS object ... Object: { "a": 1, "b": [ 13, 1.4142135623730951, 15 ] } Parsing JS object ... a: 1 Length of b: 3 Second element of b: 1,4142135623731 Iterate over all keys ... a: 1 b: 13,1.4142135623730951,15
+14
Feb 09 '14 at 20:58
source share

I know this is an old question, but my answer, hopefully, will be a big help for others who keep coming to this page after searching for "vba json".

I found this page very useful. It provides several Excel compatible VBA classes that process processing data in JSON format.

+7
Jun 14 '12 at 17:19
source share

UPDATE: Found a safer way to parse JSON than using Eval, this blog post shows the dangers of Eval ... http://exceldevelopmentplatform.blogspot.com/2018/01/vba-parse-json-safer-with-jsonparse- and .html

It's late for this party, but sorry guys, but by far the easiest way is to use Microsoft Script Control. Sample code that uses VBA.CallByName for granularity

 'Tools->References-> 'Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx Private Sub TestJSONParsingWithCallByName() Dim oScriptEngine As ScriptControl Set oScriptEngine = New ScriptControl oScriptEngine.Language = "JScript" Dim sJsonString As String sJsonString = "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }" Dim objJSON As Object Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")") Debug.Assert VBA.CallByName(objJSON, "key1", VbGet) = "value1" Debug.Assert VBA.CallByName(VBA.CallByName(objJSON, "key2", VbGet), "key3", VbGet) = "value3" End Sub

In fact, I spent a series of questions and answers that cover topics related to JSON / VBA.

IN 1. In Excel VBA on Windows, how do I solve the problem of JSON parsing broken by the behavior of capital letters in the IDE?

Q2 In Excel VBA on Windows, how to get through the parsed JSON array?

Q3 In Excel VBA on Windows, how to get a JSON string representation instead of "[object of an object]" for parsed JSON variables?

Q4 In Windows Excel VBA, how do I get JSON keys for the warning "Runtime Error" 438 ": the object does not support this property or method"?

Q5 In Excel VBA on Windows, for parsed JSON variables, what is this JScriptTypeInfo?

+5
Jul 11 '16 at 10:07 on
source share

Here is the Native VB JSON Library.

You can use JSON, which is already in IE8 +. Thus, you do not depend on a third-party library that is outdated and not verified.

see alternative version of amedeus here

 Sub myJSONtest() Dim oJson As Object Set oJson = oIE_JSON() ' See below gets IE.JSON object ' using json objects Debug.Print oJson.parse("{ ""hello"": ""world"" }").hello ' world Debug.Print oJson.stringify(oJson.parse("{ ""hello"": ""world"" }")) ' {"hello":"world"} ' getting items Debug.Print oJson.parse("{ ""key1"": ""value1"" }").key1 ' value1 Debug.Print oJson.parse("{ ""key1"": ""value1"" }").itemGet("key1") ' value1 Debug.Print oJson.parse("[ 1234, 4567]").itemGet(1) ' 4567 ' change properties Dim o As Object Set o = oJson.parse("{ ""key1"": ""value1"" }") o.propSetStr "key1", "value\""2" Debug.Print o.itemGet("key1") ' value\"2 Debug.Print oJson.stringify(o) ' {"key1":"value\\\"2"} o.propSetNum "key1", 123 Debug.Print o.itemGet("key1") ' 123 Debug.Print oJson.stringify(o) ' {"key1":123} ' add properties o.propSetNum "newkey", 123 ' addkey! JS MAGIC Debug.Print o.itemGet("newkey") ' 123 Debug.Print oJson.stringify(o) ' {"key1":123,"newkey":123} ' assign JSON 'objects' to properties Dim o2 As Object Set o2 = oJson.parse("{ ""object2"": ""object2value"" }") o.propSetJSON "newkey", oJson.stringify(o2) ' set object Debug.Print oJson.stringify(o) ' {"key1":123,"newkey":{"object2":"object2value"}} Debug.Print o.itemGet("newkey").itemGet("object2") ' object2value ' change array items Set o = oJson.parse("[ 1234, 4567]") ' Debug.Print oJson.stringify(o) ' [1234,4567] Debug.Print o.itemGet(1) o.itemSetStr 1, "234" Debug.Print o.itemGet(1) Debug.Print oJson.stringify(o) ' [1234,"234"] o.itemSetNum 1, 234 Debug.Print o.itemGet(1) Debug.Print oJson.stringify(o) ' [1234,234] ' add array items o.itemSetNum 5, 234 ' add items! JS Magic Debug.Print o.itemGet(5) ' 234 Debug.Print oJson.stringify(o) ' [1234,234,null,null,null,234] ' assign JSON object to array item o.itemSetJSON 3, oJson.stringify(o2) ' assign object Debug.Print o.itemGet(3) '[object Object] Debug.Print oJson.stringify(o.itemGet(3)) ' {"object2":"object2value"} Debug.Print oJson.stringify(o) ' [1234,234,null,{"object2":"object2value"},null,234] oIE_JSON_Quit ' quit IE, must shut down or the IE sessions remain. Debug.Print oJson.stringify(o) ' can use after but but IE server will shutdown... soon End Sub

You can connect to IE.JSON from VB.
Create the oIE_JSON function

 Public g_IE As Object ' global Public Function oIE_JSON() As Object ' for array access o.itemGet(0) o.itemGet("key1") JSON_COM_extentions = "" & _ " Object.prototype.itemGet =function( i ) { return this[i] } ; " & _ " Object.prototype.propSetStr =function( prop , val ) { eval('this.' + prop + ' = ""' + protectDoubleQuotes (val) + '""' ) } ; " & _ " Object.prototype.propSetNum =function( prop , val ) { eval('this.' + prop + ' = ' + val + '') } ; " & _ " Object.prototype.propSetJSON =function( prop , val ) { eval('this.' + prop + ' = ' + val + '') } ; " & _ " Object.prototype.itemSetStr =function( prop , val ) { eval('this[' + prop + '] = ""' + protectDoubleQuotes (val) + '""' ) } ; " & _ " Object.prototype.itemSetNum =function( prop , val ) { eval('this[' + prop + '] = ' + val ) } ; " & _ " Object.prototype.itemSetJSON =function( prop , val ) { eval('this[' + prop + '] = ' + val ) } ; " & _ " function protectDoubleQuotes (str) { return str.replace(/\\/g, '\\\\').replace(/""/g,'\\""'); }" ' document.parentwindow.eval dosen't work some versions of ie eg ie10? IEEvalworkaroundjs = "" & _ " function IEEvalWorkAroundInit () { " & _ " var x=document.getElementById(""myIEEvalWorkAround"");" & _ " x.IEEval= function( s ) { return eval(s) } ; } ;" g_JS_framework = "" & _ JSON_COM_extentions & _ IEEvalworkaroundjs ' need IE8 and DOC type g_JS_HTML = "<!DOCTYPE html> " & _ " <script>" & g_JS_framework & _ "</script>" & _ " <body>" & _ "<script id=""myIEEvalWorkAround"" onclick=""IEEvalWorkAroundInit()"" ></script> " & _ " HEllo</body>" On Error GoTo error_handler ' Create InternetExplorer Object Set g_IE = CreateObject("InternetExplorer.Application") With g_IE .navigate "about:blank" Do While .Busy: DoEvents: Loop Do While .ReadyState <> 4: DoEvents: Loop .Visible = False ' control IE interface window .Document.Write g_JS_HTML End With Set objID = g_IE.Document.getElementById("myIEEvalWorkAround") objID.Click ' create eval Dim oJson As Object 'Set oJson = g_IE.Document.parentWindow.Eval("JSON") ' dosen't work some versions of IE Set oJson = objID.IEEval("JSON") Set objID = Nothing Set oIE_JSON = oJson Exit Function error_handler: MsgBox ("Unexpected Error, I'm quitting. " & Err.Description & ". " & Err.Number) g_IE.Quit Set g_IE = Nothing End Function Public Function oIE_JSON_Quit() g_IE.Quit Exit Function End Function

Up if you find useful

+4
Nov 11 '13 at 6:33
source share

VBA-JSON Tim Hall, licensed by MIT and GitHub . This is another vba-json fork that appeared at the end of 2014. Claims to work in Mac Office and Windows 32bit and 64bit.

+4
Jul 09 '15 at 13:58
source share

VB6 - JsonBag, another JSON Parser / Generator should also be imported into VBA with little problems.

+3
Oct 21 '13 at 7:30
source share

I would suggest using the .Net component. You can use .Net components from VB6 through Interop - here is a tutorial . I assume that .Net components will be more reliable and better supported than anything that was created for VB6.

There are components in the Microsoft.Net infrastructure such as DataContractJsonSerializer or JavaScriptSerializer . You can also use third-party libraries such as JSON.NET .

+2
May 7 '10 at 8:46 a.m.
source share

You can write an Excel-DNA add-in in VB.NET. Excel-DNA is a thin library that allows you to write XLL in .NET. This way you gain access to the entire .NET universe and can use things like http://james.newtonking.com/json , the JSON structure that deserializes JSON in any custom class.

If you're interested, write how to create a shared Excel JSON client for Excel using VB.NET:

http://optionexplicitvba.com/2014/05/09/developing-a-json-excel-add-in-with-vb-net/

And here is the link to the code: https://github.com/spreadgit/excel-json-client/blob/master/excel-json-client.dna

+2
May 11 '14 at 9:02
source share

Since Json is nothing but strings, it can be easily handled if we can manage it correctly, no matter how complex the structure is. I do not think that you need to use any external library or converter. Here is an example where I parsed json data using string manipulation.

 Sub GetJsonContent() Dim http As New XMLHTTP60, itm As Variant With http .Open "GET", "http://jsonplaceholder.typicode.com/users", False .send itm = Split(.responseText, "id"":") End With x = UBound(itm) For y = 1 To x Cells(y, 1) = Split(Split(itm(y), "name"": """)(1), """")(0) Cells(y, 2) = Split(Split(itm(y), "username"": """)(1), """")(0) Cells(y, 3) = Split(Split(itm(y), "email"": """)(1), """")(0) Cells(y, 4) = Split(Split(itm(y), "street"": """)(1), """")(0) Next y End Sub
+1
Jun 16 '17 at 8:29
source share

Using JavaScript functions to parse JSON, on top of ScriptControl, we can create a parser in VBA that will list each data point inside JSON. No matter how nested or complex the data structure is, as long as we provide a valid JSON, this analyzer will return a complete tree structure.

JavaScripts The Eval, getKeys, and getProperty methods provide building blocks for validating and reading JSON.

In combination with a recursive function in VBA, we can iterate over all keys (up to the nth level) in a JSON string. Then, using the Tree control (used in this article) or a dictionary, or even on a simple sheet, we can arrange the JSON data as needed.

The full VBA code is here. Using JavaScript functions to parse JSON, on top of ScriptControl, we can create a parser in VBA that will list each data point inside JSON. No matter how nested or complex the data structure is, as long as we provide a valid JSON, this analyzer will return a complete tree structure.

JavaScripts The Eval, getKeys, and getProperty methods provide building blocks for validating and reading JSON.

In combination with a recursive function in VBA, we can iterate over all keys (up to the nth level) in a JSON string. Then, using the Tree control (used in this article) or a dictionary, or even on a simple sheet, we can arrange the JSON data as needed.

The full VBA code is here

0
Nov 13 '14 at 6:41
source share

EXCEL cell formula

 =JSON2("{mykey:1111, mykey2:{keyinternal1:22.1,keyinternal2:22.2}, mykey3:3333}", "mykey2", "keyinternal2")

DISPLAYS: 22.2

 =JSON("{mykey:1111,mykey2:2222,mykey3:3333}", "mykey2")

SHOWS: 2222

  • INSTRUCTIONS:
  • Step 1. Press ALT + F11
  • Step 2. Insert → Module
  • Step 3. Tools -> Links -> tick Microsoft Script Control 1.0
  • Step4. paste it below.
  • Step5. ALT + Q close the VBA window.

Tools → Links → Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C: \ Windows \ SysWOW64 \ MSScript.ocx

 Public Function JSON(sJsonString As String, Key As String) As String On Error GoTo err_handler Dim oScriptEngine As ScriptControl Set oScriptEngine = New ScriptControl oScriptEngine.Language = "JScript" Dim objJSON As Object Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")") JSON = VBA.CallByName(objJSON, Key, VbGet) Err_Exit: Exit Function err_handler: JSON = "Error: " & Err.Description Resume Err_Exit End Function Public Function JSON2(sJsonString As String, Key1 As String, Key2 As String) As String On Error GoTo err_handler Dim oScriptEngine As ScriptControl Set oScriptEngine = New ScriptControl oScriptEngine.Language = "JScript" Dim objJSON As Object Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")") JSON2 = VBA.CallByName(VBA.CallByName(objJSON, Key1, VbGet), Key2, VbGet) Err_Exit: Exit Function err_handler: JSON2 = "Error: " & Err.Description Resume Err_Exit End Function
0
Aug 29 '16 at 8:49
source share

this is vb6 example code checked in order, work done

from the good examples above, I made changes and got this good result

it can read keys {} and arrays []

 Option Explicit 'in vb6 click "Tools"->"References" then 'check the box "Microsoft Script Control 1.0"; Dim oScriptEngine As New ScriptControl Dim objJSON As Object ''to use it Private Sub Command1_Click() MsgBox JsonGet("key1", "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }")''returns "value1" MsgBox JsonGet("key2.key3", "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }") ''returns "value3" MsgBox JsonGet("result.0.Ask", "{'result':[{'MarketName':'BTC-1ST','Bid':0.00004718,'Ask':0.00004799},{'MarketName':'BTC-2GIVE','Bid':0.00000073,'Ask':0.00000074}]}") ''returns "0.00004799" MsgBox JsonGet("mykey2.keyinternal1", "{mykey:1111, mykey2:{keyinternal1:22.1,keyinternal2:22.2}, mykey3:3333}") ''returns "22.1" End Sub Public Function JsonGet(eKey$, eJsonString$, Optional eDlim$ = ".") As String Dim tmp$() Static sJsonString$ If Trim(eKey$) = "" Or Trim(eJsonString$) = "" Then Exit Function If sJsonString <> eJsonString Then sJsonString = eJsonString oScriptEngine.Language = "JScript" Set objJSON = oScriptEngine.Eval("(" + eJsonString + ")") End If tmp = Split(eKey, eDlim) If UBound(tmp) = 0 Then JsonGet = VBA.CallByName(objJSON, eKey, VbGet): Exit Function Dim i&, o As Object Set o = objJSON For i = 0 To UBound(tmp) - 1 Set o = VBA.CallByName(o, tmp(i), VbGet) Next i JsonGet = VBA.CallByName(o, tmp(i), VbGet) Set o = Nothing End Function Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) Set objJSON = Nothing End Sub
0
Nov 22 '17 at 14:28
source share

Understand this is an old post, but I recently stumbled upon it by adding web service consumption to an old VB6 application. The accepted answer (VB-JSON) is still valid and seems to work. However, I found that Chilkat was updated and now includes REST and JSON functionality, which makes it a universal (albeit paid) tool for me. They even have an online code generator that generates code to analyze inserted JSON data.

JsonObject link

Link to the code generator

0
May 22 '19 at 18:52
source share


More articles:


All Articles