I got an email asking how to translate text to another language using Excel. I don’t know, but it sounds like a fun project.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Function Translate(sWord As String, sFrLang As String, sToLang As String) As String Dim oIE As Object Dim sUrl As String Dim sAllLang As String sAllLang = "en,es,fr,de,it,pt," If InStr(1, sAllLang, sFrLang & ",") > 0 And _ InStr(1, sAllLang, sToLang & ",") > 0 Then 'Create template URL sUrl = "http://www.systranlinks.com/systran/cgi"" sUrl = sUrl & "AID=5338060&PID=1143024&partner=systran-AffiliatesBox" sUrl = sUrl & "-en&urltext=#WORD#&y=9&lp=#LANGFR#_#LANGTO#&x=18" 'Replace place holders with arguments sUrl = Replace(sUrl, "#WORD#", sWord) sUrl = Replace(sUrl, "#LANGTO#", sToLang) sUrl = Replace(sUrl, "#LANGFR#", sFrLang) 'Create IE object and navigate to site Set oIE = CreateObject("InternetExplorer.Application") oIE.Navigate sUrl 'Wait until site is fully loaded Do Loop Until oIE.ReadyState = 4 'READYSTATE_COMPLETE 'retrieve translated word Translate = oIE.Document.all.Item(184).innertext oIE.Quit Else Translate = CVErr(2015) End If End Function |
If you couldn’t tell by looking at it, this function is super slow. And for some reason it appears to be volatile, that is, it recalculates even when none of its arguments change.
It automates Internet Explorer and uses a web site to do the translation (is that legal?). I had to find a website that used the word and the languages in the URL instead of javascript. Maybe they can all do that, but this is the one I found.
Whenever I automate IE, I use the MSDN Library to get the objects, etc. By the way, the 184 (index for the DocElement) was pure trial and error. I just looped through all the tables on the website and printed the index number and innertext until I saw what I was looking for. Using a word I knew, of course. If they ever change their website, this function will be worthless. I could still loop through the tables, but the function is slow enough as it is.
Here’s what the results look like
Dick, I got a similar request, asking me how to translate using Excel. Maybe it was from the same guy. In any case, I didn’t know it was possible to combine IE with Excel to do an automatic translation. This is indeed a useful bit of code.
Regarding machine translation, I’ve notice some sites are more accurate than others when dealing with specific languages, so I am wondering which part of your code I would need to change to a different site.
For example, this is a specialist translation site for English and Japanese.
http://www.csse.monash.edu.au/cgi-bin/cgiwrap/jwb/wwwjdic?9T
Regarding legality, I’m no expert either, but I figure these translation sites are free to use anyway, so theoretically there shouldn’t be any problem. (On the other hand, some sites do try to push their translation software so they might get a little upset)
Dick,
Someone asked about this in the excel programming NG about six weeks ago. Here is my reply to that post.
However, it looks like Altavista has changed their page structure a bit since then. Here’s an updated version, which utilizes the XMLHTTP object and should be quite a bit faster than Automating IE:
Public Enum TranslateLanguages
Eng_Fren = 1
Eng_Ger = 2
Eng_Ita = 3
Eng_Port = 4
Eng_Span = 5
Fren_Eng = 6
Fren_Ger = 7
Ger_Eng = 8
Ger_Fren = 9
Ita_Eng = 10
Port_Eng = 11
Span_Eng = 12
End Enum
Public Function gsTranslateText(rsTextToTranslate, _
rMode As TranslateLanguages) As String
Dim xml As XMLHTTP40
Dim abytPostData() As Byte
Dim sMode As String
Dim sResponse As String
Dim nStartPos As Integer
Dim nEndPos As Integer
Select Case rMode
Case Eng_Fren
sMode = “en_fr”
Case Eng_Ger
sMode = “en_de”
Case Eng_Ita
sMode = “en_it”
Case Eng_Port
sMode = “en_pt”
Case Eng_Span
sMode = “en_es”
Case Fren_Eng
sMode = “fr_en”
Case Fren_Ger
sMode = “fr_de”
Case Ger_Eng
sMode = “de_en”
Case Ger_Fren
sMode = “de_fr”
Case Ita_Eng
sMode = “it_en”
Case Port_Eng
sMode = “pt_en”
Case Span_Eng
sMode = “es_en”
End Select
abytPostData = StrConv(“doit=done&intl=1? _
& “&tt=urltext&lp=” & sMode & “&urltext=” _
& rsTextToTranslate, vbFromUnicode)
Set xml = New XMLHTTP40
With xml
.Open “POST”, _
“http://babelfish.altavista.com/babelfish/tr”
.setRequestHeader “Content-Type”, _
“application/x-www-form-urlencoded”
.send abytPostData
sResponse = .responseText
End With
‘/ find translation
nStartPos = InStr(1, sResponse, “name=””q”””, vbTextCompare)
If nStartPos Then
nStartPos = nStartPos + 16
nEndPos = InStr(nStartPos, sResponse, _
“”””, vbTextCompare) – 1
If nEndPos >= nStartPos Then gsTranslateText = _
Mid$(sResponse, nStartPos, nEndPos – _
nStartPos + 1)
End If
Set xml = Nothing
End Function
You should be able to utilize the XMLHTTP object with the Systran URL in your example if you’d like to test it out. [note: the above code requires a reference to the Microsoft XML v4.0 library]
CAN YOU HELP ME..
WHATS THE SYNTAX OF THE FUNCTION??
THE STRUCTURE OF THE WEB PAGE STILL THE SAME??
Exactly what do I save the file as, in what directory do I place it, and how do I “call” it?
Thanks.