在Windows中的Excel VBA中，如何获取字符串的JSON表示而不是“[object Object]”解析JSON变量？ [英] In Excel VBA on Windows, how to get stringified JSON respresentation instead of "[object Object]" for parsed JSON variables?

问题描述

在这里回答我自己的问题。

我已经在Excel VBA中完成了一些JSON工作，还有很多结果可以在Q&格式

这是系列5的问题3。是全系列

Q1

answering my own question here.
I have done some work with JSON in Excel VBA and lots of findings to post which I will do so in Q & A format https://stackoverflow.com/help/self-answer http://blog.stackoverflow.com/2011/07/its-ok-to-ask-and-answer-your-own-questions/

So elsewhere on stackoverflow one can see questions about parsing JSON in VBA but they seem to miss a trick or two.

To begin with, I resile from using custom JSON parsing libraries and instead use the ScriptControl's Eval method as the basis of all my JSON code. And also we express a preference from native Microsoft solutions.

Here is a prior question In Excel VBA on Windows, how to mitigate issue of dot syntax traversal of parsed JSON broken by IDE's capitalisation behaviour? upon which this question builds. It shows how using VBA.CallByName is more robust than using the dot syntax to traverse a parsed JSON object. Also another prior question In Excel VBA on Windows, how to loop through a JSON array parsed? shows how it also can be used to access array elements. But CallByName returns a curious variable type that appears in Watch window as Object/JScriptTypeInfo and if one type Debug.Print in the immediate window (or hovers over the variable) one gets the uninformative "[object Object]".

How can we improve on this and get a JSON stringified represenation?

Here is a screenshot of what you see in Immediate windows after a Debug.Print (?) and if you hover over a variable.

This is Question 3 of series of 5. Here is the full series

Q1 In Excel VBA on Windows, how to mitigate issue of dot syntax traversal of parsed JSON broken by IDE's capitalisation behaviour?

Q2 In Excel VBA on Windows, how to loop through a JSON array parsed?

Q3 In Excel VBA on Windows, how to get stringified JSON respresentation instead of "[object Object]" for parsed JSON variables?

Q4 In Windows Excel VBA,how to get JSON keys to pre-empt "Run-time error '438': Object doesn't support this property or method"?

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

解决方案

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

这篇关于在Windows中的Excel VBA中，如何获取字符串的JSON表示而不是“[object Object]”解析JSON变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！