Parse and access deepl.com API response with VBA

Question

I have a VBA function which retrieves data from the deepl.com API (translation).

Private Function Translate_Text(INPUT_TEXT As String) As String

  Dim apiKey As String
  Dim textToTranslate As String
  Dim fromLanguage As String
  Dim toLanguage As String
  Dim request As New MSXML2.XMLHTTP60
  Dim response As New MSHTML.HTMLDocument

  ' Set my API key here
  apiKey = myAPIKey

  ' Get the text to translate from a text box
  textToTranslate = INPUT_TEXT

  ' Set the languages to translate from and to
  fromLanguage = "en"
  toLanguage = "de"

  ' Send the request to the DeepL API
  request.Open "POST", "https://api-free.deepl.com/v2/translate?", False
  request.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
  request.Send "auth_key=" & apiKey & "&text=" & textToTranslate & "&source_lang=" & fromLanguage & "&target_lang=" & toLanguage

  ' Parse the response from the API
  response.body.innerHTML = request.responseText

  Debug.Print request.responseText

This print shows that the request itself was successful:

{"translations":[{"detected_source_language":"EN","text":"Steigerung der Benutzereffizienz"}]}

I then try to access the "text" element:

translatedText = response.getElementsByTagName("text")(0).innerText

However this throws an error ("Object variable or with block variable not defined")

So my question is how I can correctly access the text in the response object.

Get this value first: `response.getElementsByTagName("text")` then check to see if it is `Nothing` before you do anything with it. — braX, Jan 05 '23 at 21:56
I can't perform ```Debug.Print response.getElementsByTagName("text")``` (throws an error). When supervising the variable it is classified as "object" but has a length of 0 — Geole, Jan 05 '23 at 22:04
The response is a full object which can be expanded etc in the supervision window — Geole, Jan 05 '23 at 22:44
`request.responseText` is not HTML, but a json-formatted string, so you can use a library like VBA-JSON to parse it. see - https://github.com/VBA-tools/VBA-JSON Plenty of examples here on SO of how that works. — Tim Williams, Jan 05 '23 at 23:11

score 0 · Answer 1 · answered Jan 09 '23 at 13:39

The request you get from the API is JSON formatted, not HTML. You can not use getElementsByTagName on JSON that's why it always says its empty.

You would need to use something like VBA-JSON (as suggested by Tim Williams above). https://github.com/VBA-tools/VBA-JSON

When using VBA-JSON you can do something like this:

Dim Json As Object
Set Json = JsonConverter.ParseJson(request.responseText)

Debug.Print Json.text

Parse and access deepl.com API response with VBA

1 Answers1