Translating Text

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.

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

translate

5 Comments

  1. Andrew says:

    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)

  2. Jake Marx says:

    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]

  3. z3nny says:

    CAN YOU HELP ME..

    WHATS THE SYNTAX OF THE FUNCTION??

    THE STRUCTURE OF THE WEB PAGE STILL THE SAME??

  4. stedawa says:

    Exactly what do I save the file as, in what directory do I place it, and how do I “call” it?

    Thanks.

  5. […] translating text from one language to another.Many of you are familiar with the approach found at Translating text, or perhaps your searching has brought you to Google translate by Internet Explorer Automation, a […]

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: