Hello everyone,
A few years ago I used a macro that would translate Japanese to English and vice versa.
It would look up the content of a cell in google and copy and paste the result back into the cell.
I have tried to use the same macro today without luck.
Could anyone help me with my code?
>>> use code - tags <<<
A few years ago I used a macro that would translate Japanese to English and vice versa.
It would look up the content of a cell in google and copy and paste the result back into the cell.
I have tried to use the same macro today without luck.
Could anyone help me with my code?
>>> use code - tags <<<
Code:
________________________________
Sub test()
MsgBox getGoogleTranslation("Lion", "english", "hindi")
End Sub
Public Function getGoogleTranslation(ByVal strSource As String, ByVal strSourceLang As String, ByVal strDestLang As String) As String
Dim strURL As String
Dim strRes As String
Dim varArrLanguage() As Variant
Dim varArrGoogleLanguage() As Variant
Dim lngLangVal As Long
varArrLanguage = Array("AFRIKAANS", "ALBANIAN", "ARABIC", "BELARUSIAN", "BULGARIAN", _
"CATALAN", "CHINESE", "CHINESE SIMPLIFIED", "CHINESE TRADITIONAL", _
"CROATIAN", "CZECH", "DANISH", "DUTCH", "ENGLISH", "ESTONIAN", _
"FILIPINO", "FINNISH", "FRENCH", "GALICIAN", "GERMAN", "GREEK", _
"HEBREW", "HINDI", "HUNGARIAN", "ICELANDIC", "INDONESIAN", "IRISH", _
"ITALIAN", "JAPANESE", "KOREAN", "LATVIAN", "LITHUANIAN", "MACEDONIAN", _
"MALAY", "MALTESE", "NORWEGIAN", "PERSIAN", "POLISH", "PORTUGUESE", _
"ROMANIAN", "RUSSIAN", "SERBIAN", "SLOVAK", "SLOVENIAN", "SPANISH", _
"SWAHILI", "SWEDISH", "TAGALOG", "THAI", "TURKISH", "UKRAINIAN", _
"VIETNAMESE", "WELSH", "YIDDISH")
varArrLanguage = Application.Transpose(Application.Transpose(varArrLanguage))
varArrGoogleLanguage = Array("af", "sq", "ar", "be", "bg", _
"ca", "zh", "zh-CN", "zh-TW", _
"hr", "cs", "da", "nl", "en", "et", _
"tl", "fi", "fr", "gl", "de", "el", _
"iw", "hi", "hu", "is", "id", "ga", _
"it", "ja", "ko", "lv", "lt", "mk", _
"ms", "mt", "no", "fa", "pl", "pt-PT", _
"ro", "ru", "sr", "sk", "sl", "es", _
"sw", "sv", "tl", "th", "tr", "uk", _
"vi", "cy", "yi")
varArrGoogleLanguage = Application.Transpose(Application.Transpose(varArrGoogleLanguage))
lngLangVal = 0
On Error Resume Next
lngLangVal = WorksheetFunction.Match(UCase(Trim(strSourceLang)), varArrLanguage, 0)
On Error GoTo 0: Err.Clear
If lngLangVal > 0 Then
strSourceLang = varArrGoogleLanguage(lngLangVal)
Else
strSourceLang = vbNullString
End If
lngLangVal = 0
On Error Resume Next
lngLangVal = WorksheetFunction.Match(LCase(Trim(strDestLang)), varArrLanguage, 0)
On Error GoTo 0: Err.Clear
If lngLangVal > 0 Then
strDestLang = varArrGoogleLanguage(lngLangVal)
Else
strDestLang = vbNullString
End If
If strSourceLang <> vbNullString Or strDestLang <> vbNullString Then
strURL = strURL & "http://translate.google.com/translate_a/t?client=t&text="
strURL = strURL & Replace(strSource, " ", "%20")
strURL = strURL & "&hl=en&sl=" & strSourceLang
strURL = strURL & "&tl=" & strDestLang & "&multires=1&pc=0&rom=1&sc=1"
With CreateObject("msxml2.xmlhttp")
.Open "get", strURL, False
.send
strRes = .responseText
End With
getGoogleTranslation = Replace(Replace(Split(strRes, ",")(0), "[", ""), """", "")
Else
getGoogleTranslation = vbNullString
End If
End Function
Last edited by a moderator:
