Solved

VBA code to translate a range of cells from Spanish to English

Posted on 2014-03-12
13
3,218 Views
Last Modified: 2014-03-24
There may be better ways to approach it but my first attempt was to use the answer posted here: http://stackoverflow.com/questions/19098260/translate-text-using-vba

The problems are that
I don't fully understand what's supposed to happen on the "DoEvents" Loops'
I have IE8 open but nothing happens when it successfuly passes thru the "IE.navigate ..." line .
it bugs on the "CLEAN_DATA = Split ..." line with an "Object or With variable not set" error.

The code seems to have worked for the original questioner, so I don't know. I like how simple and terse the code is so I'm hoping that it can be fixed with some input from you. I've already adapted the code a little but I may have introduced some errors which is one of the reasons I included the link.

Thanks!
John

Sub test()
For Each cel In Range([T6], [T10000].End(xlUp))
    Dim s As String
    s = cel
    MsgBox translate_using_vba(s)
Next cel
End Sub
Function translate_using_vba(str) As String
' 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
    inputstring = "es"
    '   TO CHOOSE OUTPUT LANGUAGE
    outputstring = "en"
    text_to_convert = str
    'open website
    IE.Visible = False
    IE.navigate "http://translate.google.com/?hl=en&tab=wT/#" & inputstring & "/" & outputstring & "/" & text_to_convert
    
        Do Until IE.ReadyState = 4
            DoEvents
        Loop

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

        Do Until IE.ReadyState = 4
            DoEvents
        Loop
        
    CLEAN_DATA = Split(Application.WorksheetFunction.Substitute(IE.Document.getElementById("result_box").innerHTML, "</SPAN>", ""), "<")
    
    For j = LBound(CLEAN_DATA) To UBound(CLEAN_DATA)
        result_data = result_data & Right(CLEAN_DATA(j), Len(CLEAN_DATA(j)) - InStr(CLEAN_DATA(j), ">"))
    Next
    IE.Quit
    translate_using_vba = result_data
End Function

Open in new window

0
Comment
Question by:gabrielPennyback
  • 7
  • 6
13 Comments
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39924544
This works for me.
Sub test()
    Dim cel As Range
    Dim s As String

    For Each cel In Range("A1:A3").Cells
        s = cel
        MsgBox translate_using_vba(s)
    Next cel
    
End Sub

Function translate_using_vba(str As String) As String
' Tools Refrence Select Microsoft internet Control
    Dim IE As Object, i As Long, j 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
    inputstring = "es"
    '   TO CHOOSE OUTPUT LANGUAGE
    outputstring = "en"
    text_to_convert = str
    'open website
    IE.Visible = False
    IE.Navigate "http://translate.google.com/?hl=en&tab=wT/#" & inputstring & "/" & outputstring & "/" & text_to_convert

    Do Until IE.ReadyState = 4
        DoEvents
    Loop

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

    Do Until IE.ReadyState = 4
        DoEvents
    Loop

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

    For j = LBound(CLEAN_DATA) To UBound(CLEAN_DATA)
        result_data = result_data & Right(CLEAN_DATA(j), Len(CLEAN_DATA(j)) - InStr(CLEAN_DATA(j), ">"))
    Next
    IE.Quit
    translate_using_vba = result_data
End Function

Open in new window

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 39924592
Thanks for posting MacroShadow.  It still doesn't work for me. I don't see anything different in the function, but I did put 3 spanish words in A1:A3, and when I step through the code, nothing happens until it bugs on the "CLEAN_DATA = Split ... " line. What all do you have checkmarked in references?

What happens when you step through the code? Where does it put the translations?


Thanks,
John
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39924806
You don't need any references (contrary to the comment in the beginning of the function).

What error do you get?

You can replace (and probably should) the "CLEAN_DATA = Split ... " line with this:
CLEAN_DATA = Split(Replace(IE.Document.getElementById("result_box").innerHTML, "</SPAN>", ""), "<")

Open in new window

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 39924986
Object or with variable not set
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39925760
Perhaps your internet connection is too slow, try increasing the time the code waits for the complete loading of the page.
i.e. instead of:
Application.Wait (Now + TimeValue("0:00:2"))
try:
Application.Wait (Now + TimeValue("00:00:05"))

Open in new window

0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39925761
Here is a optimized commented version:
Sub Demo()
    Dim cel As Range
    Dim s As String

    For Each cel In Range("A1:A3").Cells
        s = cel
        MsgBox GoogleTranslate(s, "es", "en")
    Next cel
End Sub

Function GoogleTranslate(strTextToConvert As String, strInputLang As String, strOutputString As String) As String
    
    Dim oIE As Object
    Dim strResult As String
    
    'Create instance of Internet Explorer
    Set oIE = CreateObject("InternetExplorer.Application")
    
    'Hide the new instance of Internet Explorer
    oIE.Visible = False
    
    'open website
    oIE.Navigate "http://translate.google.com/?hl=en&tab=wT/#" & strInputLang & "/" & strOutputString & "/" & strTextToConvert
    
    'Wait for page to load, freeing the proccesor for other tasks in the meanwhile
    While oIE.busy And oIE.ReadyState <> 4
        DoEvents
    Wend
    
    'Suspend code execution for 2 seconds, giving the page time to load
    Application.Wait (Now + TimeValue("00:00:02"))

    While oIE.busy And oIE.ReadyState <> 4
        DoEvents
    Wend
    
    'Extract the result from the html
    'the name of the element that holds the translation is: result_box
    'the innerHTML will look something tike this: <span class="hps">hola mundo</span>
    'therefore we remove the ending: </span> then we're left with: <span class="hps">hola mundo
    'next we split the string into two parts at the ">" the result is the second part
    strResult = Split(Replace(oIE.Document.getElementById("result_box").innerHTML, "</span>", ""), ">")(1)
    
    'Cleanup - Quit Internet Explorer and reset the variable
    oIE.Quit
    Set oIE = Nothing
    
    'Return the result
    GoogleTranslate = strResult
    
End Function

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:gabrielPennyback
ID: 39927730
I still get the same bug. One thing that I know is wrong is that line 23 of the code does not open the website. Does that tell us anything?

If there's another way to do this I'm certainly open to that.

Thanks,
John
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 39927839
For what it's worth, this code does open a webpage with no problem, if that helps.
Option Explicit
Private Declare Function ShellExecute _
  Lib "shell32.dll" Alias "ShellExecuteA" ( _
  ByVal hWnd As Long, _
  ByVal Operation As String, _
  ByVal Filename As String, _
  Optional ByVal Parameters As String, _
  Optional ByVal Directory As String, _
  Optional ByVal WindowStyle As Long = vbMinimizedFocus _
  ) As Long
Public Sub OpenUrl()
    Dim lSuccess As Long
    lSuccess = ShellExecute(0, "Open", "http://translate.google.com/?hl=en&tab=wT")
End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39928549
The original code opens the webpage but hides Internet Explorer, if you want to see the IE window change
oIE.Visible = False

Open in new window

to
oIE.Visible = True

Open in new window

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 39930035
Thanks. As we peel back this layer of the onion, a new problem emerges! When the macro tries to open the google translate page, my firewall at work won't allow it "Your system policy has denied access to the requested URL." Maybe that's the source of the bug on the "CLEAN_DATA = Split ..." line?  Is it possible to get the code to open my default browser, Chrome, and work with it?
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39933146
Automating chrome is a whole new ball game, I can try.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39938886
It seems like you will need to install the Selenium Wrapper.

Now I don't have time to experiment with it, in the meantime you can read this article, I hope to get around to it later this week.

p.s. Being as this is a new question (how to automate Chrome as apposed to the original question how to automate IE) you probably should ask a new question.
0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 39951803
Thanks Macro Shadow. I will open up a new question, thanks for all your input.   ~ John
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now