0

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
  • 154,628
  • 8
  • 97
  • 125
Nixen85
  • 1,253
  • 8
  • 24

2 Answers2

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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 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")` – Nixen85 Mar 18 '22 at 22:00
0

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

Set jsonObject = DecodeJsonString(objHTTP.responseText)
Summer-Time
  • 1,824
  • 15
  • 19
  • You mean by using `Set ScriptEngine = CreateObject("ScriptControl")` `ScriptEngine.Language = "JScript"` and so on as shown here https://stackoverflow.com/a/7300963/8900664 – Nixen85 Mar 18 '22 at 19:31
  • 1
    The typical method recommended here is to use this library: https://github.com/VBA-tools/VBA-JSON Using a script engine can work but does have some significant security concerns. – Tim Williams Mar 18 '22 at 21:28