Results 1 to 3 of 3

Thread: Using Google Translate

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    San Jose, CA

    Using Google Translate

    I have a worksheet that contains random words and phrases in several non-English languages, plus dates and numbers. The "Translate" sub in this code which I found on the web drives the process of using Google Translate to translate the words to English. It works great most of the time but once in a while the code will display the "Can not translate" message for one or more of the words or phrases even though it translated them in a previous run. Is it because the website is busy? How can I avoid that? BTW the translateFrom = "auto" tells Google Translate to detect the language in the cell and translateTo = "en" tells it to translate it to English.

    Sub Translate()
    Dim cel As Range
    With ActiveSheet
        For Each cel In .UsedRange.Cells
            If Not IsEmpty(cel) Then
                TranslateCell cel
            End If
    End With
    End Sub
    Private Sub TranslateCell(cel As Range)
        Dim getParam As String, trans As String, translateFrom As String, translateTo As String
        Dim objHTTP As Object
        Dim URL As String
        translateFrom = "auto"
        translateTo = "en"
        Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
        getParam = ConvertToGet(cel.Value)
        URL = "" & translateFrom & "&sl=" & translateFrom & "&tl=" & translateTo & "&ie=UTF-8&prev=_m&q=" & getParam
        objHTTP.Open "GET", URL, False
        objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        objHTTP.send ("")
        If InStr(objHTTP.responseText, "div dir=""ltr""") > 0 Then
            trans = RegexExecute(objHTTP.responseText, "div[^""]*?""ltr"".*?>(.+?)</div>")
            cel.Value = Clean(trans)
            MsgBox ("Error: Can not translate '" & cel & "'")
            Debug.Print "Could not translate '" & cel & "'"
        End If
    End Sub
    '----Used functions----
    Function ConvertToGet(val As String)
        val = Replace(val, " ", "+")
        val = Replace(val, vbNewLine, "+")
        val = Replace(val, "(", "%28")
        val = Replace(val, ")", "%29")
        ConvertToGet = val
    End Function
    Function Clean(val As String)
        val = Replace(val, "&quot;", """")
        val = Replace(val, "%2C", ",")
        val = Replace(val, "'", "'")
        Clean = val
    End Function
    Public Function RegexExecute(str As String, reg As String, _
                                 Optional matchIndex As Long, _
                                 Optional subMatchIndex As Long) As String
        Dim regex As Object
        Dim matches
        On Error GoTo ErrHandl
        Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = reg
        regex.Global = Not (matchIndex = 0 And subMatchIndex = 0) 'For efficiency
        If regex.Test(str) Then
            Set matches = regex.Execute(str)
            RegexExecute = matches(matchIndex).SubMatches(subMatchIndex)
            Exit Function
        End If
        RegexExecute = CVErr(xlErrValue)
    End Function

  2. #2
    New Member
    Join Date
    Aug 2022

    Re: Using Google Translate

    With the help of Google translator, it is excellent to translate sites and their text elements. But for more complex tasks, it is not suitable. Anyway, the result when you edit the translated text yourself will be much better. The same goes for video translation. If you use the services of Vidbay, they make it possible to correct the translated text from the video before voice acting. And they have over 40 languages available for translation.

  3. #3
    Join Date
    May 2021

    Re: Using Google Translate

    I'm familiar with that routine - John_w (an MVP) wrote it. But the version above is different to what (I think) is his most recent version - as of Sept 2021.

    The HTML code for the Google Translatate site changed, so the code needed to be updated accordingly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width