Excel VBA谷歌翻译宏遗漏特殊字符的解决方法咨询

你的GTranslate宏处理含双引号这类特殊字符时会丢失内容，核心问题是WorksheetFunction.EncodeURL对部分特殊字符的编码不完整，导致请求发送到谷歌翻译接口时这些字符被丢弃。

修改方案 #

替换原有编码逻辑，用自定义的URL编码函数处理输入字符串，确保所有特殊字符都被正确编码。

修改后的完整代码 #

Function GTranslate(strInput As String, strFromLang As String, strToLang As String) As String
    Dim strURL As String, objHTTP As Object, objHTML As Object, objDivs As Object, objDiv As Variant
    
    ' 使用自定义URL编码函数替换WorksheetFunction.EncodeURL
    strInput = URLEncode(strInput)
    strURL = "https://translate.google.com/m?hl=" & strFromLang & _
        "&sl=" & strFromLang & _
        "&tl=" & strToLang & _
        "&ie=UTF-8&prev=_m&q=" & strInput
        
    Set objHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
    objHTTP.Open "GET", strURL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (compatible; MSIE 6.0; Windows NT 10.0)"
    objHTTP.Send ""
    
    Set objHTML = CreateObject("htmlfile")
    With objHTML
        .Open
        .Write objHTTP.responseText
        .Close
    End With
    
    Set objDivs = objHTML.getElementsByTagName("div")
    For Each objDiv In objDivs
        If objDiv.className = "result-container" Then
            GTranslate = objDiv.innerText: Exit For
        End If
    Next objDiv
    
    Set objHTML = Nothing: Set objHTTP = Nothing
End Function

' 自定义URL编码函数，处理所有特殊字符
Function URLEncode(strText As String) As String
    Dim intChar As Integer
    Dim strChar As String
    For intChar = 1 To Len(strText)
        strChar = Mid(strText, intChar, 1)
        Select Case Asc(strChar)
            Case 48 To 57, 65 To 90, 97 To 122 ' 数字、字母直接保留
                URLEncode = URLEncode & strChar
            Case 32 ' 空格编码为+
                URLEncode = URLEncode & "+"
            Case Else ' 其他字符编码为%XX格式
                URLEncode = URLEncode & "%" & Hex(Asc(strChar))
        End Select
    Next intChar
End Function

关键说明 #

• 自定义的URLEncode函数会逐个检查输入字符串的每个字符：
  • 数字、字母直接保留
  • 空格转换为+（符合URL编码规范）
  • 双引号（ASCII码34）等特殊字符会被编码为%22，确保这些字符能被谷歌翻译接口正确识别
• 替换原有WorksheetFunction.EncodeURL后，特殊字符不会再丢失，翻译结果会完整保留原内容中的特殊符号

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