Google Translate via VBA setting for accept-encoding

Question

I'm trying to use the google translate API via VBA (works in python so far), and I found that the only difference between the python request and the VBA one is on the header "accept-encoding", the python one uses the "application/gzip" and works, but the VBA one is automatically changed to "gzip,deflate" even if I change it through code. Here is the code:

Function Test_GoogleTranslate()
    Dim strTranslate As String
    Dim objRequest As Object
    Dim strUrl As String
    Dim blnAsync As Boolean
    Dim strResponse As String
    Dim strWeather As String
    Dim payload As String
    Set objRequest = CreateObject("MSXML2.XMLHTTP")
    payload = "target=es&q=something&source=en"
    strTranslate = "https://google-translate1.p.rapidapi.com/language/translate/v2"
    strTranslate = strTranslate & "?" & payload  
    With objRequest
    .Open "POST", strTranslate, True
    .setRequestHeader "host", "google-translate1.p.rapidapi.com"
    .setRequestHeader "x-forwarded-port", "443"
    .setRequestHeader "x-forwarded-proto", "https"
    .setRequestHeader "connection", "keep-alive"


    .setRequestHeader "content-type", "application/x-www-form-urlencoded"
    .setRequestHeader "accept-encoding", "application/gzip"
    .setRequestHeader "x-rapidapi-host", "google-translate1.p.rapidapi.com"
    .setRequestHeader "x-rapidapi-key", "856e8ba78dmsh443766612c5a923p14f661jsn72323e803261"
    .Send
        While objRequest.readyState <> 4
            DoEvents
        Wend
        strResponse = .ResponseText
    End With
    MsgBox (strResponse)
End Function

When I changed the accept-encoding to "gzip, deflate" in python it crashed, so I figured that might be the problem. Any help is greatly appreciated

Answer 1

I had some success with Google Translate and Excel VBA using the MSXML2.ServerXMLHTTP object. I note you are using MSXML2.XMLHTTP. The solution appears to work well only setting a User-Agent request header so I did not delve into accept-encoding etc.

The differences between MSXML2.ServerXMLHTTP and MSXML2.XMLHTTP are touched upon in this question which might be useful for you.

Working code using MSXML2.ServerXMLHTTP:

Option Explicit

Sub Test()

    Debug.Print Translate("Hello", "en", "fr", True) ' french
    Debug.Print Translate("Hello", "en", "de", True) ' german
    Debug.Print Translate("Hello", "en", "pt", True) ' portuguese
    Debug.Print Translate("Hello", "en", "ru", False) ' russian - use romanised alphabet
    Debug.Print Translate("Hello", "en", "ru", True) ' russian - use cyrillic
    ' ThisWorkbook.Sheets(1).Range("A1").Value = Translate("Hello", "en", "ru", True)
    Debug.Print Translate("Hello", "en", "zh-CN", False) ' chinese simplified - use romanised alphabet
    Debug.Print Translate("Hello", "en", "zh-CN", True) ' chinese simplified - use chinese script
    ' ThisWorkbook.Sheets(1).Range("B1").Value = Translate("Hello", "en", "zh-CN", True)

End Sub

Public Function Translate(strInput As String, strFromLanguageCode As String, strToLanguageCode As String, blnTargetAlphabet As Boolean) As String

    Dim strURL As String
    Dim objHTTP As Object
    Dim objHTML As Object
    Dim objDivs As Object, objDiv
    Dim strTranslatedT0 As String
    Dim strTranslatedO1 As String

    ' send query to web page
    strURL = "https://translate.google.com/m?hl=" & strFromLanguageCode & _
        "&sl=" & strFromLanguageCode & _
        "&tl=" & strToLanguageCode & _
        "&ie=UTF-8&prev=_m&q=" & strInput

    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    objHTTP.Open "GET", strURL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ""

    ' create a html document
    Set objHTML = CreateObject("htmlfile")
    With objHTML
        .Open
        .Write objHTTP.responseText
        .Close
    End With

    ' o1 has Anglicised translation, t0 as tranlsation in target language
    Set objDivs = objHTML.getElementsByTagName("div")
    For Each objDiv In objDivs
        If objDiv.className = "o1" Then
            strTranslatedO1 = objDiv.innerText
        End If
        If objDiv.className = "t0" Then
            strTranslatedT0 = objDiv.innerText
        End If
    Next objDiv

    ' choose which to return
    If blnTargetAlphabet Then
        Translate = strTranslatedT0
    Else
        Translate = strTranslatedO1
    End If

CleanUp:
    Set objHTML = Nothing
    Set objHTTP = Nothing

End Function

Result:

Bonjour
Hallo
Olá
Privet
??????
Ni hao
??

The VBA immediate window doesn't print Cyrillic or Chinese characters but you can see this feature working by outputing to a cell:

December 2020 update

Looks like this method will no longer work going back maybe to mid November.

Looking at the response

• the div class names have changed to something more obscure
• there's some esoteric c-wiz elements doing something wonderful...
• also, I suspect that some client side script is calling for the actual translation after the document is retrieved

Options: Selenium, Microsoft Translate, free and paid tiers for Google translation APIs ;)