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.

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


5 thoughts on “Translating Text

  1. 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.

    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. 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”, _
          .setRequestHeader “Content-Type”, _
          .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]




Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.