Excel Macros - Google Translation Using VBA

[ashish]

If you want to translate the text via Google Translate using VBA . Snapshot below-

• In "Input cell" type the data you want to translate 
  
• In "Select The Input Language" select the language which you want to translate or language in which the input data is . Choose "Detect" if you don't know the language.
• In "Select The Output Language"  select the output of language
• In "Output Cell" keep it blank . As macro will run and paste the output in it.

Here is the code-

Sub transalte_using_vba()

' Tools Refrence Select Microsoft internet Control

Dim ie As Object, i As Long

Dim inputstring As String, outputstring As String, text_to_convert As String, result_data As String, CLEAN_DATA

Set ie = CreateObject("InternetExplorer.application")

        '   TO CHOOSE INPUT LANGUAGE

    If Sheet1.ComboBox1.Value = "Detect" Then

        inputstring = "auto"

    Else

        inputstring = Application.WorksheetFunction.VLookup(Sheet1.ComboBox1.Value, Sheets("Country List").Range("a:b"), 2, 0)

    End If

        '   TO CHOOSE OUTPUT LANGUAGE

    If Sheet1.ComboBox2.Value = "English" Then

        outputstring = "en"

    Else

        outputstring = Application.WorksheetFunction.VLookup(Sheet1.ComboBox2.Value, Sheets("Country List").Range("a:b"), 2, 0)

    End If

    text_to_convert = Sheets("Translator").Range("b6").Value

 'open website

    ie.Visible = False

    ie.navigate "http://translate.google.com/#" & inputstring & "/" & outputstring & "/" & text_to_convert

   

    Do Until ie.ReadyState = 4

        DoEvents

    Loop

  

    Application.Wait (Now + TimeValue("0:00:5"))

    

    Do Until ie.ReadyState = 4

        DoEvents

    Loop

    CLEAN_DATA = Split(Application.WorksheetFunction.Substitute(ie.Document.getElementById("result_box").innerHTML, "</SPAN>", ""), "<")

    For i = LBound(CLEAN_DATA) To UBound(CLEAN_DATA)

        result_data = result_data & Right(CLEAN_DATA(i), Len(CLEAN_DATA(i)) - InStr(CLEAN_DATA(i), ">"))

    Next

    Sheets("Translator").Range("L6").Value = ""

    Sheets("Translator").Range("L6").Value = result_data

    ie.Quit

    MsgBox "Done", vbOKOnly

    

End Sub

Download Working Macro  https://www.box.com/s/d9fa9e2b0d2af1ca4168

[Nur Hossain]

Dear Ashis Bhai,

I have down loaded “Google Translator using VBA” from www.box.com

But it showing “Compile error  Method or data member not found.”

Also drop down are not working. Please check the attachment.  

--
----------------------------------------------------------------------------------------------------------------------
You received this message because you are subscribed to the Google
Groups "Excel VBA Codes & Macros" group.
 
To post to this group, send email to
excelvb...@googlegroups.com
 
To unsubscribe from this group, send email to
excelvbamacro...@googlegroups.com
 
For more options, visit this group at
http://groups.google.com/group/excelvbamacros
 
 

[koul....@gmail.com]

I think u r using 2003 ver

Sent on my BlackBerry® from Vodafone

---

From: Nur Hossain <nur...@gmail.com>

Sender: excelvb...@googlegroups.com

Date: Sat, 18 Aug 2012 01:04:06 +0600

To: <excelvb...@googlegroups.com>

ReplyTo: excelvb...@googlegroups.com

Subject: Fwd: browse("excel macros", "excel tips") Excel Macros - Google Translation Using VBA

[Nur Hossain]

yea i am using office 2003. 

[ashish koul]

try the attachment see if it helps 

Regards

 

Ashish Koul

Visit

http://www.excelvbamacros.com/

http://www.accessvbamacros.com/

P Before printing, think about the environment.

 

[Nur Hossain]

Dear ashish Bhaia,

It works perfectly but little bit slower. thanks for modifying it from 2007 to 2003.

Nur