如何用VBA正确解析并访问DeepL API的翻译响应内容？

你错误地使用MSHTML.HTMLDocument来解析DeepL API返回的JSON响应——getElementsByTagName是用于检索HTML标签的方法，而API返回的是JSON格式数据，并非HTML结构，这就是导致报错“Object variable or with block variable not defined”的原因。

下面提供两种正确的解析方案：

方案1：使用VBA-JSON库（推荐） #

这是最稳定的JSON解析方式，只需先在VBA编辑器中导入VBA-JSON库（通过「工具」→「引用」添加，或直接导入对应模块），再修改代码如下：

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 jsonResponse As Object ' 存储JSON解析后的对象

  ' 配置API参数
  apiKey = myAPIKey
  textToTranslate = INPUT_TEXT
  fromLanguage = "en"
  toLanguage = "de"

  ' 发送POST请求（注意移除URL末尾多余的问号）
  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

  ' 解析JSON响应
  Set jsonResponse = JsonConverter.ParseJson(request.responseText)
  
  ' 提取翻译文本（VBA-JSON中数组索引从1开始）
  Translate_Text = jsonResponse("translations")(1)("text")

  Debug.Print Translate_Text
End Function

方案2：使用ScriptControl（无需额外库） #

如果不想导入外部库，可以利用VBA自带的ScriptControl解析JSON，需先在「工具」→「引用」中勾选「Microsoft Script Control 1.0」：

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 sc As New ScriptControl
  Dim jsonObj As Object

  ' 配置API参数
  apiKey = myAPIKey
  textToTranslate = INPUT_TEXT
  fromLanguage = "en"
  toLanguage = "de"

  ' 发送POST请求
  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

  ' 初始化JScript引擎解析JSON
  sc.Language = "JScript"
  Set jsonObj = sc.Eval("(" & request.responseText & ")")
  
  ' 提取翻译文本（JScript中数组索引从0开始）
  Translate_Text = jsonObj.translations(0).text

  Debug.Print Translate_Text
End Function

额外注意事项 #

• 原代码中请求URL末尾的多余问号?建议移除，避免潜在的请求格式问题
• 使用VBA-JSON时，确保库已正确导入，否则会出现JsonConverter未定义的错误

内容的提问来源于stack exchange，提问作者Geole