Excel VBA: 해석된 JSON 객체 루프

Excel VBA: 해석된 JSON 객체 루프

아래 예에 따르면...구문 분석된 JSON 문자열에서 개체를 반복하면 "Object does not support this property or method" 오류가 반환됩니다.이 일을 어떻게 해야 하는지 조언해 주실 수 있나요?대단히 감사합니다(여기에 묻기 전에 답을 찾기 위해 6시간을 소비했습니다).

JSON 문자열을 오브젝트로 해석하는 기능(이 기능은 정상적으로 동작합니다.

Function jsonDecode(jsonString As Variant)
    Set sc = CreateObject("ScriptControl"): sc.Language = "JScript" 
    Set jsonDecode = sc.Eval("(" + jsonString + ")")
End Function

구문 분석된 개체를 반복하면 "개체가 이 속성 또는 메서드를 지원하지 않습니다." 오류가 반환됩니다.

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 

추신. 저는 이미 이 라이브러리들을 조사했습니다.

vba-json 예제를 사용할 수 없었습니다.
- VBJSON vba 스크립트는 포함되어 있지 않습니다(이 스크립트는 동작할 수 있지만 Excel에 로드하는 방법은 알 수 없으며 최소한의 문서도 있습니다).

또, 다차원 해석된 JSON 어레이에 액세스 할 수 있습니까?기본적인 키/값 어레이 루프만 작동시키면 됩니다(너무 많은 것을 요구하면 죄송합니다).감사해요.

---

편집: 다음은 vba-json 라이브러리를 사용한 두 가지 작업 예입니다.위의 질문은 아직 수수께끼입니다만...

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오브젝트에는 (워치창에서 볼 수 있듯이) 모든 관련 정보가 포함되어 있습니다만, VBA에서는 취득할 수 없는 것 같습니다.

이 경우,JScriptTypeInfoinstance는 Javascript 객체를 참조합니다.For Each ... Next작동하지 않습니다.단, Javascript 어레이를 참조하는 경우에는 동작합니다( 참조).GetKeys이하와 같이 기능합니다.

따라서 회피책은 Javascript 엔진을 사용하여 VBA에서 얻을 수 없는 정보를 얻는 것입니다.우선 Javascript 객체의 키를 취득하는 기능이 있습니다.

키를 알게 되면 다음 문제는 속성에 액세스하는 것입니다.실행 시에만 키의 이름을 알 수 있는 경우에도 VBA는 도움이 되지 않습니다.오브젝트의 속성에 액세스하기 위해서는 두 가지 방법이 있습니다.하나는 값용이고 다른 하나는 오브젝트와 어레이용입니다.

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기본 초기화를 수행하기 전에 다른 기능을 사용하기 전에 한 번 클릭합니다.

코도의 답변은 훌륭하고 해결책의 근간을 이루고 있다.

그러나 VBA의 CallByName을 사용하면 JSON 구조를 조회할 수 있습니다.방금 Google Places Details에서 VBA를 사용한 Excel에 대한 솔루션을 작성했습니다.

실제로 이 예시와 같이 Script Engine에 추가된 함수를 사용하지 않고 다시 작성했습니다.CallByName만을 사용하여 어레이를 루프할 수 있었습니다.

예를 들어 설명해야 할 샘플 코드가 있습니다.

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

Option Explicit

Sub TestJSONParsingWithVBACallByName()

    Dim oScriptEngine As ScriptControl
    Set oScriptEngine = New ScriptControl
    oScriptEngine.Language = "JScript"

    Dim jsonString As String
    jsonString = "{'key1':'value1','key2':'value2'}"

    Dim objJSON As Object
    Set objJSON = oScriptEngine.Eval("(" + jsonString + ")")

    Debug.Assert VBA.CallByName(objJSON, "key1", VbGet) = "value1"
    Debug.Assert VBA.CallByName(objJSON, "key2", VbGet) = "value2"

    Dim jsonStringArray As String
    jsonStringArray = "[ 1234, 4567]"

    Dim objJSONArray As Object
    Set objJSONArray = oScriptEngine.Eval("(" + jsonStringArray + ")")

    Debug.Assert VBA.CallByName(objJSONArray, "length", VbGet) = "2"

    Debug.Assert VBA.CallByName(objJSONArray, "0", VbGet) = "1234"
    Debug.Assert VBA.CallByName(objJSONArray, "1", VbGet) = "4567"


    Stop

End Sub

또한 하위 개체(내포된 개체)도 Google Places Details에서 Google Maps 예제에서 VBA를 사용하여 Excel을 참조하십시오.

편집: 평가판을 사용하지 말고 JSON을 안전하게 해석해 보십시오.이 블로그 투고를 참조해 주세요.

초간단한 답변 - OO(또는 javascript)의 힘으로 원하는 아이템(n)메서드를 추가할 수 있습니다!

여기서의 나의 완전한 답변

Private ScriptEngine As ScriptControl

Public Sub InitScriptEngine()
    Set ScriptEngine = New ScriptControl
    ScriptEngine.Language = "JScript"
    ScriptEngine.AddCode "Object.prototype.myitem=function( i ) { return this[i] } ; "
    Set foo = ScriptEngine.Eval("(" + "[ 1234, 2345 ]" + ")") ' JSON array
    Debug.Print foo.myitem(1) ' method case sensitive!
    Set foo = ScriptEngine.Eval("(" + "{ ""key1"":23 , ""key2"":2345 }" + ")") ' JSON key value
    Debug.Print foo.myitem("key1") ' WTF

End Sub

Json은 문자열에 불과하기 때문에 구조가 아무리 복잡해도 올바르게 조작할 수 있으면 간단하게 처리할 수 있습니다.이 트릭을 사용하기 위해 외부 라이브러리나 컨버터를 사용할 필요는 없다고 생각합니다.다음은 문자열 조작을 사용하여 json 데이터를 해석한 예입니다.

Sub Json_data()
Const URL = "https://api.redmart.com/v1.5.8/catalog/search?extent=2&pageSize=6&sort=1&category=bakery"
Dim http As New XMLHTTP60, html As New HTMLDocument
Dim str As Variant

With http
    .Open "GET", URL, False
    .send
    str = Split(.responseText, "category_tags"":")
End With
On Error Resume Next
y = UBound(str)

    For i = 1 To y
        Cells(i, 1) = Split(Split(str(i), "title"":""")(1), """")(0)
        Cells(i, 2) = Split(Split(str(i), "sku"":""")(1), """")(0)
        Cells(i, 3) = Split(Split(str(i), "price"":")(1), ",")(0)
        Cells(i, 4) = Split(Split(str(i), "desc"":""")(1), """")(0)
    Next i
End Sub

2020년인데 엔드 투 엔드 솔루션이 없어서 우연히 이 실마리를 찾았습니다.도움이 되었습니다만, 런타임에 키를 사용하지 않고 데이터에 동적으로 액세스 할 필요가 있는 경우, 상기의 답변은, 필요한 데이터를 취득하기 위해서 몇개의 수정이 필요합니다.

드디어 VBA에서 이 JSON 해석 문제에 대한 엔드 투 엔드의 깔끔한 솔루션을 얻을 수 있는 기능을 생각해 냈습니다.이 함수는 JSON 문자열(임의의 레벨로 내포함)을 입력으로 사용하여 포맷된 2차원 배열을 반환합니다.이 어레이는 일반 i/j 루프를 통해 워크시트로 쉽게 이동하거나 인덱스 기반 접근성이 뛰어나 쉽게 재생할 수 있습니다.

입출력 예시

함수는 Github repo의 JSON2Array.bas 파일에 저장됩니다.JSON2Array-VB

데모 사용 서브루틴도 .bas 파일에 포함되어 있습니다.VBA 모듈의 파일을 다운로드하여 Import하십시오.도움이 됐으면 좋겠어요.

늦은 건 알지만, VBJSON을 사용할 줄 모르는 사람은 다음 절차를 따르기만 하면 됩니다.

1) 프로젝트에 JSON.bas Import (VBA Editor 열기, Alt + F11; File > Import File)

2) 사전 참조/클래스 추가 Windows 전용의 경우는, 「Microsoft 스크립팅 런타임」에 대한 참조를 포함합니다.

VBA-JSON도 같은 방법으로 사용할 수 있습니다.이 방법은 VB6 대신 VBA 전용으로 모든 매뉴얼이 준비되어 있습니다.

언급URL : https://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop