Translate text using vba
One of the modern solution using Google Translation API To Enable Google Translation API, first you should create the project and credentials. If you receive 403 (Daily Limit), you need to add payment method into your Google Cloud Account, then you will get results instantly.
Private Function GoogleTranslateJ(ByVal text, ByVal resLang, ByVal srcLang) As String
Dim jsonProvider As Object
Dim jsonResult As Object
Dim jsonResultText As String
Dim googleApiUrl As String
Dim googleApiKey As String
Dim resultText As String
Set jsonProvider = CreateObject("MSXML2.ServerXMLHTTP")
text = Replace(text, " ", "%20")
googleApiKey = "ijHF28h283fjijefiwjeofij90f2h923" 'YOUR GOOGLE API KEY
googleApiUrl = "https://translation.googleapis.com/language/translate/v2?key=" & googleApiKey & "&source=" & srcLang & "&target=" & resLang & "&q=" & text
jsonProvider.Open "POST", googleApiUrl, False
jsonProvider.setRequestHeader "Content-type", "application/text"
jsonProvider.send ("")
jsonResultText = jsonProvider.responseText
Set jsonResult = JsonConverter.ParseJson(jsonResultText)
Set jsonResult = jsonResult("data")
Set jsonResult = jsonResult("translations")
Set jsonResult = jsonResult(1)
resultText = jsonResult("translatedText")
GoogleTranslateJ = resultText
End Function
Here you go.
Sub test()
Dim s As String
s = "hello world"
MsgBox translate_using_vba(s)
End Sub
Function translate_using_vba(str) As String
' Tools Refrence Select Microsoft internet Control
Dim IE As Object, i As Long
Dim inputstring As String, outputstring As String, text_to_convert As String, result_data As String, CLEAN_DATA
Set IE = CreateObject("InternetExplorer.application")
' TO CHOOSE INPUT LANGUAGE
inputstring = "auto"
' TO CHOOSE OUTPUT LANGUAGE
outputstring = "es"
text_to_convert = str
'open website
IE.Visible = False
IE.navigate "http://translate.google.com/#" & inputstring & "/" & outputstring & "/" & text_to_convert
Do Until IE.ReadyState = 4
DoEvents
Loop
Application.Wait (Now + TimeValue("0:00:5"))
Do Until IE.ReadyState = 4
DoEvents
Loop
CLEAN_DATA = Split(Application.WorksheetFunction.Substitute(IE.Document.getElementById("result_box").innerHTML, "</SPAN>", ""), "<")
For j = LBound(CLEAN_DATA) To UBound(CLEAN_DATA)
result_data = result_data & Right(CLEAN_DATA(j), Len(CLEAN_DATA(j)) - InStr(CLEAN_DATA(j), ">"))
Next
IE.Quit
transalte_using_vba = result_data
End Function
Here is a more streamlined way to use Excel VBA and Google... to translate text.
This VBA User Defined Function should be entered into a standard code module.
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: Chaque instant est un nouveau départ.
Of course this Translate() function can be used directly from VBA as well:
MsgBox Translate([A1], "en", "de") '<--displays: Jeder Moment ist ein Neuanfang.
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
This is how I would do it. It's function with optional enumeration objects that point to language codes used by google translate. For simplicity I only included a few language codes. Also, in this sample I selected the Microsoft Internet Controls reference so instead of creating an object, there's an InternetExplorer object used. And finally, to get rid of having to clean up the output, I just used .innerText rather than .innerHTML. Keep in mind, there's a character limit of around 3000 or so with google translate, and also, you must set IE=nothing especially if you will be using this multiple times, otherwise you will create multiple IE processes and eventually it won't work anymore.
Setup...
Option Explicit
Const langCode = ("auto,en,fr,es")
Public Enum LanguageCode
InputAuto = 0
InputEnglish = 1
InputFrench = 2
InputSpanish = 3
End Enum
Public Enum LanguageCode2
ReturnEnglish = 1
ReturnFrench = 2
ReturnSpanish = 3
End Enum
Test...
Sub Test()
Dim msg As String
msg = "Hello World!"
MsgBox AutoTranslate(msg, InputEnglish, ReturnSpanish)
End Sub
Function...
Public Function AutoTranslate(ByVal Text As String, Optional LanguageFrom As LanguageCode, Optional LanguageTo As LanguageCode2) As String
Dim langFrom As String, langTo As String, IE As InternetExplorer, URL As String, myArray
If IsMissing(LanguageFrom) Then
LanguageFrom = InputAuto
End If
If IsMissing(LanguageTo) Then
LanguageTo = ReturnEnglish
End If
myArray = Split(langCode, ",")
langFrom = myArray(LanguageFrom)
langTo = myArray(LanguageTo)
URL = "https://translate.google.com/#" & langFrom & "/" & langTo & "/" & Text
Set IE = New InternetExplorer
IE.Visible = False
IE.Navigate URL
Do Until IE.ReadyState = 4
DoEvents
Loop
Application.Wait (Now + TimeValue("0:00:5"))
Do Until IE.ReadyState = 4
DoEvents
Loop
AutoTranslate = IE.Document.getElementByID("result_box").innerText
IE.Quit
Set IE = Nothing
End Function