Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-03-12
13
Medium Priority
?
4,480 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

636 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