translate text in Excel
'Here is a VBA User Defined Function to translate text in an Excel cell. It
'uitlizes Google Translate and thus requires an Internet connection:
Function Translate$(sText$, FromLang$, ToLang$)
Dim p1&, p2&, url$, resp$
Const DIV_RESULT$ = "<div class=""result-container"">"
Const URL_TEMPLATE$ = "https://translate.google.com/m?hl=[from]&sl=[from]&tl=[to]&ie=UTF-8&prev=_m&q="
url = URL_TEMPLATE & WorksheetFunction.EncodeURL(sText)
url = Replace(url, "[to]", ToLang)
url = Replace(url, "[from]", FromLang)
resp = WorksheetFunction.WebService(url)
p1 = InStr(resp, DIV_RESULT)
If p1 Then
p1 = p1 + Len(DIV_RESULT)
p2 = InStr(p1, resp, "</div>")
Translate = Mid$(resp, p1, p2 - p1)
End If
End Function
'------------------------------------------------------------------------------
'With the following text in cell A1: Every moment is a fresh beginning.
'In cell B1, enter this formula:
=Translate(A1, "en", "fr") '<--translates text in A1 from English to French.
'The result in cell B1 will be: Chaque instant est un nouveau départ.
'Of course the Translate() function can be used directly from VBA as well:
MsgBox Translate([A1], "en", "de") '<--displays: Jeder Moment ist ein Neuanfang.
'And of course you may also manually use the Translate functionality
'built into Excel, which can be found on the Review tab of the
'Ribbon. But the UDF above provides a quick and streamlined method
'to translate text programmatically. Excel's translation capability
'is not exposed via the Excel Object Model, so a function like the
'above can be quite useful.
'The "FromLang" and "ToLang" arguments must be codes from the following table:
' CODE LANGUAGE
' en English
' fr French
' es Spanish
' it Italian
' de German
' af Afrikaans
' sq Albanian
' am Amharic
' ar Arabic
' hy Armenian
' az Azerbaijani
' eu Basque
' be Belarusian
' bn Bengali
' bs Bosnian
' bg Bulgarian
' ca Catalan
' ceb Cebuano
' ny Chichewa
' zh-CN Chinese (Simplified)
' zh-TW Chinese (Traditional)
' co Corsican
' hr Croatian
' cs Czech
' da Danish
' nl Dutch
' eo Esperanto
' et Estonian
' tl Filipino
' fi Finnish
' fy Frisian
' gl Galician
' ka Georgian
' el Greek
' gu Gujarati
' ht Haitian Creole
' ha Hausa
' haw Hawaiian
' iw Hebrew
' hi Hindi
' hmn Hmong
' hu Hungarian
' is Icelandic
' ig Igbo
' id Indonesian
' ga Irish
' ja Japanese
' jw Javanese
' kn Kannada
' kk Kazakh
' km Khmer
' rw Kinyarwanda
' ko Korean
' ku Kurdish (Kurmanji)
' ky Kyrgyz
' lo Lao
' la Latin
' lv Latvian
' lt Lithuanian
' lb Luxembourgish
' mk Macedonian
' mg Malagasy
' ms Malay
' ml Malayalam
' mt Maltese
' mi Maori
' mr Marathi
' mn Mongolian
' my Myanmar (Burmese)
' ne Nepali
' no Norwegian
' or Odia (Oriya)
' ps Pashto
' fa Persian
' pl Polish
' pt Portuguese
' pa Punjabi
' ro Romanian
' ru Russian
' sm Samoan
' gd Scots Gaelic
' sr Serbian
' st Sesotho
' sn Shona
' sd Sindhi
' si Sinhala
' sk Slovak
' sl Slovenian
' so Somali
' su Sundanese
' sw Swahili
' sv Swedish
' tg Tajik
' ta Tamil
' tt Tatar
' te Telugu
' th Thai
' tr Turkish
' tk Turkmen
' uk Ukrainian
' ur Urdu
' ug Uyghur
' uz Uzbek
' vi Vietnamese
' cy Welsh
' xh Xhosa
' yi Yiddish
' yo Yoruba
' zu Zulu
'
'
'