Tag Archives: translate

word translate addin

Word Translator AddIn and Excel Translator AddIn

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

Working in multinational organisations many of us struggle with ad-hoc translations of Word documents or Excel spreadsheets. Translations are a boring task. Usually what most of us have mastered is a simple process of copying texts from documents to Google Translate and back. If you have my VBA Time Saver AddIn you probably already have a neat VBA function for translations which you can add as a shortcut. However, on a daily basis if you often switch between languages you might want an AddIn instead. Thankfully I am introducing the Word Translator AddIn and the Excel Translator AddIn!

Word Translator AddIn: Translate text in Word

Word Translator AddIn
Word Translator AddIn
The Word Translator AddIn is very simple to use. First set the from and to languages. Now all you need to do is select any text and hit the Translate button (or CTRL+SHIFT+T shortcut)!

The AddIn is provided with a list of supported languages. Making it easy to simply select the language you want to translate from and to in Word. Hitting the translate button with result in the text being translated and replaced, however the formatting might change – so watch out!

The Undo functionality does not work when running a VBA macro in Word. A translated text can’t be undone. Undo will however work properly in other cases of course

Install Word Translator AddIn

Download the Word Translator AddIn

Download the Word Translator AddIn from here. Provide your email to get the download link:

Copy the AddIn to your Word Startup folder

Copy the dotm template document to your Word Startup folder. In most Windows systems it should be similar to:

C:\Users\[user.name]\AppData\Roaming\Microsoft\Word\STARTUP

Remember to replace

[user.name]

with your username!

Excel Translator AddIn: Translate text in Excel

excel translator addin example
The Excel Translator AddIn appears as an additional Ribbon tab
The Excel Translator AddIn is similarly simple to use. First set the from and to languages. Now all you need to do is select any amount of Excel ranges and hit the Translate button (or CTRL+SHIFT+T shortcut)!

The Undo functionality does not work when running a VBA macro in Excel. A translated text can’t be undone. Undo will however work properly in other cases of course

Install Excel Translator AddIn

Download the Excel Translator AddIn

Download the Excel Translator AddIn from here. Provide your email to get the download link:

Copy the AddIn to your Microsoft AddIns folder

Copy the xlam AddIn Workbook to your Microsoft AddIns folder. In most Windows systems it should be similar to:

C:\Users\[user.name]\AppData\Roaming\Microsoft\AddIns

Remember to replace

[user.name]

with your username!

How Word and Excel translation works

The Word and Excel Translator AddIns are proudly powered by the Google Translate engine. If you want to learn more read my post here. Do read more on how to use Google Translate responsibly.

Summary

Translations in Word and Excel can be a dull task. With the use of the above AddIns I was however able to translate my documents in 50% less time, giving me more time to check the quality of the translations and make minor language fixes.

Issues

Don’t post issues/error as comments below! Please post them on my Questions page.

The AddIns are powered by Google Translate functionality and require a working direct Internet Connection. If you get errors, most likely you are working from behind a proxy server and need to connect directly to the Internet.

google translate

Excel Google Translate functionality

1 Star2 Stars3 Stars4 Stars5 Stars (9 votes, average: 4.56 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