google translate

Excel Google Translate functionality

1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 4.20 out of 5)
Loading...

Translations are not a fascinating task for me. I once received a request to help out a colleague in translating an Excel file to English. Copy to Google Translate, translate, copy back to Excel and again… After going through part of the file we quickly extrapolated that the whole translation would take at least a whole day if done in this manner. I had to optimize this process as much as possible.

That’s when I thought of leveraging Google Translate to help with my localization efforts. Why translate stuff manually when we can put Excel to do the job for you.

Excel Google Translate Code

Find below a quickly written VBA procedure that adds a shortcut to Excel to quickly translate any cell from one language to another. The below is a simple procedure you can easily pin to your Excel shortcuts (e.g. CTRL+K or similar). Simply select a certain Excel range and execute the TranslateCell procedure below.

Looking to translate an Excel or Word document? Why not use my Word / Excel Translator AddIns?
Sub TranslateCell()
    Dim getParam As String, trans As String, translateFrom As String, translateTo As String
    translateFrom = "fr"
    translateTo = "en"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    getParam = ConvertToGet(ActiveCell.Value)
    URL = "https://translate.google.pl/m?hl=" & 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>")
        ActiveCell.Value = Clean(trans)
    Else
        MsgBox ("Error")
    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, "&#39;", "'")
    Clean = val
End Function
Public Function RegexExecute(str As String, reg As String, _
                             Optional matchIndex As Long, _
                             Optional subMatchIndex As Long) As String
    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
ErrHandl:
    RegexExecute = CVErr(xlErrValue)
End Function

How to setup Google Translate code in Excel?

    • Go to the DEVELOPER ribbon and add select Visual Basic a and new Module to your VBA Project
      insert macro
    • Insert the code from sections above (notice that the function is “Public” – therefore will be visible in your workbook as a so called UDF (User Defined Function) add macro
    • Go to the worksheet and input the function as shown above
    • Modify the translateFrom and translateTo languages in the function to change the translation e.g. change “pl” to “de” to change the translation from Polish to German.

Here you can find the full list of language 2 letter codes.

  • In the Excel Developer ribbon open Macros
  • Select the TranslateCell sub and go to Options
  • Add a shortcut: e.g. CTRL+SHIFT+T

How to use it?

  • Select any cell in Excel and click the configured shortcut e.g. CTRL+SHIFT+T

What if I want an UDF?

The procedure above can easily be converted to a User Defined Function (UDF) instead, allowing you to use it as a formula. Be sure to copy the required supporting functions (from the sections above) ConvertToGet, Clean and RegexExecute.

VBA UDF Google Translate

Public Function Translate(rng As Range, Optional translateFrom As String = "nl", Optional translateTo As String = "en")
    Dim getParam As String, trans As String, objHTTP As Object, URL As String
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    getParam = ConvertToGet(rng.Value)
    URL = "https://translate.google.pl/m?hl=" & 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>

")
        Translate = Clean(trans)
    Else
        Translate = CVErr(xlErrValue)
    End If
End Function

 

 

 

How to use the Translate UDF?

See an example below of how to translate text in cell A1 from French (fr) to English (en):

=TRANSLATE("A1","fr","en")

What if I want it to run on all selected cells?

Here is a modification of the function above to run on ALL selected cells:

Sub TranslateCell()
    Dim getParam As String, trans As String, translateFrom As String, translateTo As String
    translateFrom = "pl"
    translateTo = "en"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    Dim r As Range, cell As Range
    Set cell = Selection
    For Each cell In Selection.Cells
        getParam = ConvertToGet(cell.Value)
        URL = "https://translate.google.pl/m?hl=" & 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>")
            cell.Value = Clean(trans)
        Else
            MsgBox ("Error")
        End If
    Next cell
End Sub

Related Posts

16 thoughts on “Excel Google Translate functionality”

  1. Many thanks for your nice approach! I did some in other way with httpWatch for figuring out Google Post data – however it did not work nicely like yours!
    Again, many thanks for this!
    Dang DInh Ngoc
    From Vietnam

      1. This is an excellent function. How can this be created as a custom formula, instead of running the macro and translating in the same cell, so that we enter the formula in a different cell (to write the translated language) and link it to the original language cell.

        1. Firstly – great function, works a lot quicker than others I’ve tried before!

          In response to the last post, I’ve taken the liberty of modifying your Sub to a UDF that can be called from another cell (as well as taking optional parameters to set From and To languages).

          Public Function Translate(rng As Range, Optional translateFrom As String = "nl", Optional translateTo As String = "en")
              Dim getParam As String, trans As String, objHTTP As Object, URL As String
              Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
              getParam = ConvertToGet(rng.Value)
              URL = "https://translate.google.pl/m?hl=" & 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>")
                  Translate = Clean(trans)
              Else
                  Translate = CVErr(xlErrValue)
              End If
          End Function
          

          If you drop the above in your personal.xls (or personal.xlsb) file, you should be able to call it using standard formula syntax – e.g.: =Translate($A$1) , or =Translate($B$2, “fr”, “es”)

  2. Lorna are you connected via a office network behind a proxy server? The code above may not work in such situations as it requires a direct Internet connection. Try connecting directly to the Internet.

    It is possible to modify the code to include the proxy server (xxx.xxx.xxx.xxx and port). As I see there is some interest in this topic I will extend this post soon.

  3. Hi there. When i got your post in google search i was so happy to finally find what was seeking but..
    Can someone alter the code so as to act in a sellected area of cells (a column)?
    I sellect a range of cell in a column but i only get the first cell translated.
    Help would be much appreciated!!

    1. Hi, which version of the translate function are you running? There maybe at least 2 reasons:
      1. You don’t have direct internet access e.g. you are connected via an Office proxy which is not configured above. Read here how to add proxy: http://analystcave.com/web-scraping-proxy-http-request-vba/

      2. The text you are trying to translate is not encoded properly in the URL GET params. Try translating something simple – see if it helps. If so, check what characters did not encode properly.

      Let me know which one is it (or neither of these)

      1. Yes, my office connected to proxy. So, I think this causing the issue.

        I am using the first code in Excel. The string is in Japanese language but I will try again with short since the cells consists more than 10 words.

        Thank you for your feedback

      2. Hi,

        sorry for the delay. I have tested and now I have another error.

        Run-time error ‘429’;
        ActiveX component can’t create object

        Best regards,
        Jason

        1. Hi Jason, you are probably running Mac Os, ActiveX components disabled or something else. Try this help thread: < href="http://forums.iis.net/t/1164511.aspx?Can+t+Create+Object+MSXML2+ServerXMLHTTP" rel="nofollow">ActiveX component disabled…

  4. Hi

    Thank you for posting this! I got here after an hour of google searches and it works perfectly.

    Can you please shed some light on how I could possibly use this to do phonetic transalation rather than literal?

    Thanks

Leave a Reply