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.

Answer 1

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