Answers to other stack overflow question that relate to working with parsed JSON objects use a mini-script approach and we can use this approach here.
Firstly we acknowledge that Douglas Crockford is author of ‘Javascript: The Good Parts’ (http://shop.oreilly.com/product/9780596517748.do)
and is javascript expert. So we are happy to adopt his code with regard to stringification. We can get his code with a simple Xml HTTP Request
(commonly shortened to XHR) and pass the return result to ScriptControl’s AddCode method. Then add some code that allows us to override the default representation
of “[object Object]” by calling into Douglas’s library. AND then make sure we dynamically add that override to all our JScriptTypeInfo variables,
both what comes out of ScriptControl’s Eval method which we wrap with DecodeJsonString()
and also what comes out of VBA.CallByName which we wrap with GetJSONObject().
Thus,
'Tools->References->
'Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx
'Microsoft Xml, v6.0
Option Explicit
Private Function GetScriptEngine() As ScriptControl
Static soScriptEngine As ScriptControl
If soScriptEngine Is Nothing Then
Set soScriptEngine = New ScriptControl
soScriptEngine.Language = "JScript"
soScriptEngine.AddCode GetJavaScriptLibrary("https://raw.githubusercontent.com/douglascrockford/JSON-js/master/json2.js")
soScriptEngine.AddCode "function overrideToString(jsonObj) { jsonObj.toString = function() { return JSON.stringify(this); } }"
End If
Set GetScriptEngine = soScriptEngine
End Function
Private Function GetJavaScriptLibrary(ByVal sURL As String) As String
Dim xHTTPRequest As MSXML2.XMLHTTP60
Set xHTTPRequest = New MSXML2.XMLHTTP60
xHTTPRequest.Open "GET", sURL, False
xHTTPRequest.send
GetJavaScriptLibrary = xHTTPRequest.responseText
End Function
Private Function DecodeJsonString(ByVal JsonString As String) As Object
Dim oScriptEngine As ScriptControl
Set oScriptEngine = GetScriptEngine
Set DecodeJsonString = oScriptEngine.Eval("(" + JsonString + ")")
Call oScriptEngine.Run("overrideToString", DecodeJsonString) '* this gives JSON rendering instead of "[object Object]"
End Function
Private Function GetJSONObject(ByVal obj As Object, ByVal sKey As String) As Object
Dim objReturn As Object
Set objReturn = VBA.CallByName(obj, sKey, VbGet)
Call GetScriptEngine.Run("overrideToString", objReturn) '* this gives JSON rendering instead of "[object Object]"
Set GetJSONObject = objReturn
End Function
Private Sub TestJSONParsingWithCallByName2()
Dim sJsonString As String
sJsonString = "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }"
Dim objJSON As Object
Set objJSON = DecodeJsonString(sJsonString)
Stop
Dim objKey2 As Object
Set objKey2 = GetJSONObject(objJSON, "key2")
Debug.Print objKey2
Stop
End Sub
Here is a screenshot with the new code which shows a stringification of the JScriptTypeInfo variables
