Accessing keys and values in nested object with VBA

Question

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?

Tim Williams · Accepted Answer · 2022-03-18 21:48:01Z

1

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

answered Mar 18, 2022 at 21:48

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nixen85 Over a year ago

I already tried it, but your post encouraged me to give it another try - with success. My mistake was, that I didn't set the (1) to choose the first array inside the object. So the solution is:

Dim JsonText As Object Set JsonText = JsonConverter.ParseJSON(textResponse) Debug.Print JsonText("translations")(1)("text")

Summer-Time · Accepted Answer · 2022-03-18 19:03:12Z

0

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

Set jsonObject = DecodeJsonString(objHTTP.responseText)

answered Mar 18, 2022 at 19:03

Summer-Time

1,89215 silver badges19 bronze badges

2 Comments

Nixen85 Over a year ago

You mean by using Set ScriptEngine = CreateObject("ScriptControl") ScriptEngine.Language = "JScript" and so on as shown here stackoverflow.com/a/7300963/8900664

Tim Williams Over a year ago

The typical method recommended here is to use this library: github.com/VBA-tools/VBA-JSON Using a script engine can work but does have some significant security concerns.

Collectives™ on Stack Overflow

Accessing keys and values in nested object with VBA

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related