Solved

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

Posted on 2014-03-12
13
3,582 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 27

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 27

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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 1

Author Comment

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

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 27

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
 
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 27

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 27

Expert Comment

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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

820 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