Accessing keys and values in nested object with VBA-CodePudding

I use the translation API from Deepl in VBA. My request is working pretty fine and returns some translated html-text. However, I am not able to get the "text"-value in the returned object:

So my request looks as follows:

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
url = api & "?" & authKey & "&" & targetLng & "&" & tagHandling & "&" & sourceLng
Debug.Print url
objHTTP.Open "POST", url, False
objHTTP.setRequestHeader "Host", "api-free.deepl.com"
objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Accept", "*/*"
objHTTP.send "text=" & text

textResponse = objHTTP.responseText 'textResponse is defined as String
Debug.Print textResponse

I get the following output:

{
   "translations":
   [
       {"detected_source_language":"DE",
        "text":"<h2>SizeI</h2>love \"Paperwhite\".<br><br>&lt;img 
         src=\"https://ws-eu.amazon-adsystem.com/widgets/q?_encoding=UTF8"
       }
   ]
}

I further tried:

    'get the script control:
    Set ScriptEngine = CreateObject("ScriptControl")
    ScriptEngine.Language = "JScript"
    
    'Get the string and parse it:
    Set jsonObject = ScriptEngine.Eval("(" & textResponse & ")")

jsonObject returns [object Object] and I have no idea how to process this. How can I access this object and return just the text-value?

CodePudding user response：

Using VBA-JSON from here: https://github.com/VBA-tools/VBA-JSON

Function Test20220318()
    Dim json As Object, txt, trans As Collection, t As Object, k
    
    txt = [E1].Value 'using json stored in a cell for testing
    
    Set json = JsonConverter.ParseJson(txt) 'a Dictionary object
    
    Set trans = json("translations")    'access dictionary by key to get collection/array
    
    For Each t In trans                 'loop over items in collection/array
        For Each k In t                 'loop over keys in t
            Debug.Print k, "=", t(k)    'print key and associated value
        Next
    Next t
End Function

CodePudding user response：

The response is a JSON String. You have to convert the JSON string to an object

Set jsonObject = DecodeJsonString(objHTTP.responseText)