Excel VBA:解析的 JSON 对象循环 [英] Excel VBA: Parsed JSON Object Loop
问题描述
以下示例...从解析的 JSON 字符串中循环遍历对象会返回错误对象不支持此属性或方法".谁能建议如何使这项工作?非常感谢(在问这里之前,我花了 6 个小时寻找答案).
Per example below...Looping through an object from a parsed JSON string returns an error "Object doesn't support this property or method". Could anyone advise how to make this work? Much appreciated (I spent 6 hours looking for an answer before asking here).
将 JSON 字符串解析为对象的函数(这工作正常).
Function to parse JSON string into object (this works OK).
Function jsonDecode(jsonString As Variant)
Set sc = CreateObject("ScriptControl"): sc.Language = "JScript"
Set jsonDecode = sc.Eval("(" + jsonString + ")")
End Function
遍历解析的对象返回错误对象不支持此属性或方法".
Looping through the parsed object returns error "Object doesn't support this property or method".
Sub TestJsonParsing()
Dim arr As Object 'Parse the json array into here
Dim jsonString As String
'This works fine
jsonString = "{'key1':'value1','key2':'value2'}"
Set arr = jsonDecode(jsonString)
MsgBox arr.key1 'Works (as long as I know the key name)
'But this loop doesn't work - what am I doing wrong?
For Each keyName In arr.keys 'Excel errors out here "Object doesn't support this property or method"
MsgBox "keyName=" & keyName
MsgBox "keyValue=" & arr(keyName)
Next
End Sub
附注.我已经研究过这些库:
PS. I looked into these libraries already:
-vba-json 无法让示例工作.
-VBJSON 没有 vba 脚本包括在内(这可能有效,但不知道如何将其加载到 Excel 中,而且文档最少).
-vba-json Wasn't able to get the example working.
-VBJSON There's no vba script included (this might work but don't know how to load it into Excel and there is minimum documentation).
另外,是否可以访问多维解析的 JSON 数组?只是让一个基本的键/值数组循环工作会很棒(如果要求太多,抱歉).谢谢.
Also, Is it possible to access Multidimensional parsed JSON arrays? Just getting a basic key/value array loop working would be great (sorry if asking too much). Thanks.
这里有两个使用 vba-json 库的工作示例.上面的问题仍然是个谜...
Here are two working examples using the vba-json library. The question above is still a mystery though...
Sub TestJsonDecode() 'This works, uses vba-json library
Dim lib As New JSONLib 'Instantiate JSON class object
Dim jsonParsedObj As Object 'Not needed
jsonString = "{'key1':'val1','key2':'val2'}"
Set jsonParsedObj = lib.parse(CStr(jsonString))
For Each keyName In jsonParsedObj.keys
MsgBox "Keyname=" & keyName & "//Value=" & jsonParsedObj(keyName)
Next
Set jsonParsedObj = Nothing
Set lib = Nothing
End Sub
Sub TestJsonEncode() 'This works, uses vba-json library
Dim lib As New JSONLib 'Instantiate JSON class object
Set arr = CreateObject("Scripting.Dictionary")
arr("key1") = "val1"
arr("key2") = "val2"
MsgBox lib.toString(arr)
End Sub
推荐答案
JScriptTypeInfo
对象有点不幸:它包含所有相关信息(正如您在 Watch 窗口)但似乎不可能用 VBA 来实现.
The JScriptTypeInfo
object is a bit unfortunate: it contains all the relevant information (as you can see in the Watch window) but it seems impossible to get at it with VBA.
如果 JScriptTypeInfo
实例引用一个 Javascript 对象,For Each ... Next
将不起作用.但是,如果它引用 Javascript 数组,它确实可以工作(请参阅下面的 GetKeys
函数).
If the JScriptTypeInfo
instance refers to a Javascript object, For Each ... Next
won't work. However, it does work if it refers to a Javascript array (see GetKeys
function below).
因此,解决方法是再次使用 Javascript 引擎来获取我们无法使用 VBA 获取的信息.首先,有一个函数可以获取 Javascript 对象的键.
So the workaround is to again use the Javascript engine to get at the information we cannot with VBA. First of all, there is a function to get the keys of a Javascript object.
一旦您知道密钥,下一个问题就是访问属性.如果密钥的名称仅在运行时已知,则 VBA 也无济于事.所以有两种方法可以访问对象的属性,一种用于值,另一种用于对象和数组.
Once you know the keys, the next problem is to access the properties. VBA won't help either if the name of the key is only known at run-time. So there are two methods to access a property of the object, one for values and the other one for objects and arrays.
Option Explicit
Private ScriptEngine As ScriptControl
Public Sub InitScriptEngine()
Set ScriptEngine = New ScriptControl
ScriptEngine.Language = "JScript"
ScriptEngine.AddCode "function getProperty(jsonObj, propertyName) { return jsonObj[propertyName]; } "
ScriptEngine.AddCode "function getKeys(jsonObj) { var keys = new Array(); for (var i in jsonObj) { keys.push(i); } return keys; } "
End Sub
Public Function DecodeJsonString(ByVal JsonString As String)
Set DecodeJsonString = ScriptEngine.Eval("(" + JsonString + ")")
End Function
Public Function GetProperty(ByVal JsonObject As Object, ByVal propertyName As String) As Variant
GetProperty = ScriptEngine.Run("getProperty", JsonObject, propertyName)
End Function
Public Function GetObjectProperty(ByVal JsonObject As Object, ByVal propertyName As String) As Object
Set GetObjectProperty = ScriptEngine.Run("getProperty", JsonObject, propertyName)
End Function
Public Function GetKeys(ByVal JsonObject As Object) As String()
Dim Length As Integer
Dim KeysArray() As String
Dim KeysObject As Object
Dim Index As Integer
Dim Key As Variant
Set KeysObject = ScriptEngine.Run("getKeys", JsonObject)
Length = GetProperty(KeysObject, "length")
ReDim KeysArray(Length - 1)
Index = 0
For Each Key In KeysObject
KeysArray(Index) = Key
Index = Index + 1
Next
GetKeys = KeysArray
End Function
Public Sub TestJsonAccess()
Dim JsonString As String
Dim JsonObject As Object
Dim Keys() As String
Dim Value As Variant
Dim j As Variant
InitScriptEngine
JsonString = "{""key1"": ""val1"", ""key2"": { ""key3"": ""val3"" } }"
Set JsonObject = DecodeJsonString(CStr(JsonString))
Keys = GetKeys(JsonObject)
Value = GetProperty(JsonObject, "key1")
Set Value = GetObjectProperty(JsonObject, "key2")
End Sub
注意:
- 代码使用早期绑定.因此,您必须添加对Microsoft Script Control 1.0"的引用.
- 在使用其他函数进行一些基本初始化之前,您必须调用一次
InitScriptEngine
.
这篇关于Excel VBA:解析的 JSON 对象循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!