Google Translate by Internet Explorer Automation

This VBA Macro Code translates text using Google Translate by automation of Internet Explorer controlled through Excel.

Explanation

The program automatically writes text from Excel into Internet Explorer and uses the Google Translate service to translate the text into desires language. It is possible to translate from and to many different languages, just change the language code and the program is set up accordingly. Today all the major languages are available using google translate. It is one of few services that enables translation of entire sentences not just words. The service is free of charge and can be executed through API. The exact technology used for the translation is not public. Google has started to translate entire web pages on the internet as well when using the google seach function.

The entire VBA/Excel program is available for download at the bottom of this page, enjoy!

Code

Public Sub Google_Translate()

Dim Google_Translate_Internet_Explorer_Automation As Object
Set Google_Translate_Internet_Explorer_Automation = CreateObject("InternetExplorer.Application")
Google_Translate_Internet_Explorer_Automation.Navigate "http://translate.google.com/translate_t#"
Google_Translate_Internet_Explorer_Automation.Visible = True
Wait_Between_Google_Translate_Cycles = Range("G1").Value

Column = 0
While Range("f9").Offset(0, Column).Value <> tom

    Do While Google_Translate_Internet_Explorer_Automation.busy
        Call WaitSeconds(Wait_Between_Google_Translate_Cycles)
    Loop

    to_language_code = Range("f9").Offset(0, Column).Value

    Do While Google_Translate_Internet_Explorer_Automation.busy
        Call WaitSeconds(Wait_Between_Google_Translate_Cycles)
    Loop

    Google_Translate_Internet_Explorer_Automation.document.forms("text_form").elements(5).Value = to_language_code

    Do While Google_Translate_Internet_Explorer_Automation.busy
        Call WaitSeconds(Wait_Between_Google_Translate_Cycles)
    Loop

    rad = 0
    While Range("c10").Offset(rad, 0).Value <> tom

        If Range("f10").Offset(rad, Column).Value = tom Then

            from_language_code = Range("a10").Offset(rad, 0).Value
            Google_Translate_Internet_Explorer_Automation.document.forms("text_form").elements(4).Value = from_language_code

            Do While Google_Translate_Internet_Explorer_Automation.busy
                Call WaitSeconds(Wait_Between_Google_Translate_Cycles)
            Loop

            Google_Translate_Text = Range("c10").Offset(rad, 0).Value

            While Google_Translate_Internet_Explorer_Automation.busy
                Call WaitSeconds(Wait_Between_Google_Translate_Cycles)
            Wend

            Google_Translate_Internet_Explorer_Automation.document.forms("text_form").elements("source").Value = Google_Translate_Text

            While Google_Translate_Internet_Explorer_Automation.busy
                Call WaitSeconds(Wait_Between_Google_Translate_Cycles)
            Wend

            Google_Translate_Internet_Explorer_Automation.document.getElementById("text_form").submit

            While Google_Translate_Internet_Explorer_Automation.busy
                Call WaitSeconds(Wait_Between_Google_Translate_Cycles)
            Wend

            dd2 = Google_Translate_Internet_Explorer_Automation.document.forms(1).elements(4).Value

            While Google_Translate_Internet_Explorer_Automation.busy
                Call WaitSeconds(Wait_Between_Google_Translate_Cycles)
            Wend

            Google_Translate_Variable1 = Replace(dd2, Chr(13), "")
            Range("f10").Offset(rad, Column).Value = Google_Translate_Variable1

        End If

        rad = rad + 1
    Wend

    Column = Column + 1
Wend

Google_Translate_Internet_Explorer_Automation.Quit
Set Google_Translate_Internet_Explorer_Automation = Nothing

End Sub

Public Sub WaitSeconds(sek)

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + sek
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

End Sub

Download excel file! Google_Translate_Internet_Explorer_Automation.xls

Comments (18)

Yours worked like a charm!

I have an excel file with balance sheet I'd like to translate from finnish into english.. I tried with the above macro but its not working.. Plz help me in translating

i have ie8.when i go in translate some world there it not allow to tipe any thing in typing bar.the selection of language is working but typing bar is not.......

Thanks!! Its a nice tool.

But it does not pick up language codes given in Excel. The workaround: change the languages in Google translator webbrowser window (manually).

The "To" function is not getting passed correctly. Can someone help me with the tweaking in this code? I am a novice in VB.

Thanks!

The code works great with the few changes suggested (as Google Translate) has changed...but I can't get the "to" a specific language to change. Only what opens with the initial translate page. Does anyone know how to correct the call so that it changes the Google translator to the correct "to" language? :0(

Hi,
I would need your a little bit help. It only works translation from different language ex English / Polish to German, but it does not work fully translation to English.

Somehow it stopp on the second colum (example) DE_EN or PL_EN the translation to English and turn to translate to German.

Is it somethink you can help me ?

Regards
Maggie

Public Function fnGoogTranslate(strData As String, strLangTo As String, strLangFrom As String, Optional UniCodeID As Long, Optional Timeout As Integer = 5) As String
Dim objGoog As Object
Dim strText As String
Dim waittime As Date
On Error GoTo Err_Handler

If strLangTo = strLangFrom Then
fnGoogTranslate = strData
Exit Function 'don't waste my time
End If

If strData "" Then
Set objGoog = CreateObject("InternetExplorer.Application")
objGoog.Navigate "http://translate.google.com/?sl=" & strLangTo & "&tl=" & strLangFrom & "#" & strLangFrom & "|" & strLangTo & "|" & strData
objGoog.Visible = False
'objGoog.Visible = True
waittime = Now + TimeValue("00:00:" & Timeout)
Do Until fnGoogTranslate ""
DoEvents
fnGoogTranslate = objGoog.Document.all("result_box").innertext
If Now() >= waittime Then
'the function timed out
'default to the origial language
fnGoogTranslate = strData
Exit Do
End If
Loop
If UniCodeID 0 Then
fnGoogTranslate = StrConv(fnGoogTranslate, vbUnicode, UniCodeID)
End If
If fnGoogTranslate = "" Or fnGoogTranslate = strData Then
fnGoogTranslate = ""
End If
objGoog.Quit
Set objGoog = Nothing
End If
Exit Function
Err_Handler:
Select Case Err.Number
Case Else
sbLogError Err.Number & " " & Err.Description & " In Function: fnGoogTranslate"
Resume Next
End Select

End Function

I have recently experienced that the line
dd2 = Google_Translate_Internet_Explorer_Automation.document.forms(1).elements(4).Value
is no longer working. You should use:

.Document.all("result_box").innertext
from now on

This is an amazing little tool. Thanks so much for posting it! I can't code well from scratch, certainly not using an API, but I can fix code. And since it is 2010, and Google has changed up their translate page a bit, this code needed some tweaking.

@Mark Freund: Your mods were very helpful and led me to understand how to fix the part that others are still having trouble with, namely the variable 'dd2'. In addition to the adjustments @Mark has listed below, make the following adjustment to your code.

From:
dd2 = Google_Translate_Internet_Explorer_Automation.document.forms(1).elements(4).Value

To:
dd2 = Google_Translate_Internet_Explorer_Automation.document.getElementById("result_box").innerText

There are no guarantees that this will work going forward since Google is apt to change it again. But it works for now!

Happy translating!
Mark

Hello,
I have the error message: "Object variable or With block variable not set" with this line:
dd2 = Google_Translate_Internet_Explorer_Automation.document.forms(1).elements(4).Value
Can you help please?
Thanks a lot

is it possible to upload a file with list of values in column A to google translator and get the result back in excel, using VBA ??

Thanks

Hi there does this work with autodetect languages too?

Thanks for the wonderful macro,

I do have small query about this.

I noticed that macro do not always select source and target languages from XLS cells. Once it is set to particular language pair then it will only translate it into same language pair although you have specified different language pairs.

Could you please do something about this?

Thanks!

Thanks for posting the tool. It has probably saved me a few days effort in writing and debugging my own. I had to make a few minor changes to get it working + it looks like a couple of the language codes have changed. See below for code mods..

From
..elements(5).Value = to_language_code

To
..elements("sl").Value = to_language_code

and
From
..elements(4).Value = from_language_code

To
..elements("tl").Value = from_language_code

also added this
Range("f10").Offset(rad, Column).select

immediately after
If Range("f10").Offset(rad, Column).Value = tom Then

to keep a watch on progress.

Thanks again!!
Mark

Have tried to download the file and run that instead of just copy the code?

Regards,
Admin

my adress is delaban@yahoo.fr

HELLO

I have an excel file I'd like to translate from french into english
I don't understand how to do with your macro (bug messages, nothing runs)
could u help pls???
thanks

Add your comment

yvComment v.CommentVersion