0

I'm trying to convert other languages to English using auto detect by triggering the below code.

Sub transalte_using_vba()

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")
        
        inputstring = "auto"
    
        outputstring = "en"
        
        text_to_convert = Sheet3.Range("A2")

 '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
    Sheet3.Range("B2") = result_data
    ie.Quit
    MsgBox "Done", vbOKOnly
    
End Sub

However i'm facing Runtime error 424 object required in line CLEAN_DATA = Split(Application.WorksheetFunction.Substitute(ie.Document.getElementById("result_box").innerHTML, "</SPAN>", ""), "<")

What is wrong with the code?

This code is working bit slow.. as I need to work on bulk data more than 70K is there any quick way to do this?

In my system I have google chrome as default browser and can we use it for translation, which may help to run the script faster?

Linga
  • 945
  • 1
  • 14
  • 31

2 Answers2

4

A solution based on Internet Explorer is very slow by definition. Please, try the next function:

Private Function GTranslate(strInput As String, strFromLang As String, strToLang As String) As String
    Dim strURL As String, objHTTP As Object, objHTML As Object, objDivs As Object, objDiv As Variant
    
    strInput = WorksheetFunction.EncodeURL(strInput)
    strURL = "https://translate.google.com/m?hl=" & strFromLang & _
        "&sl=" & strFromLang & _
        "&tl=" & strToLang & _
        "&ie=UTF-8&prev=_m&q=" & strInput
        
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    objHTTP.Open "GET", strURL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.Send ""
    
    Set objHTML = CreateObject("htmlfile")
    With objHTML
        .Open
        .Write objHTTP.responseText
        .Close
    End With
    
    Set objDivs = objHTML.getElementsByTagName("div")
    For Each objDiv In objDivs
        If objDiv.className = "t0" Then
            GTranslate = objDiv.innerText: Exit For
        End If
    Next objDiv
    
    Set objHTML = Nothing: Set objHTTP = Nothing
End Function

It can be tested in this simple way:

Sub testTranslateG()
  Debug.Print GTranslate("Libro muy grande", "auto", "en")
End Sub

Or to translate the cells value in a range:

Private Sub Google_translate()
  Dim thisWbs As Worksheet
  Dim i As Long, lastRow As Long
  
  Set thisWbs = ActiveSheet
  lastRow = thisWbs.Range("B" & rows.count).End(xlUp).row
  thisWbs.Range("C2:C" & lastRow).Clear
  
  For i = 2 To lastRow
    thisWbs.Range("C" & i).Value = GTranslate(thisWbs.Range("B" & i).Value, "auto", "en")
  Next i
  MsgBox "Ready..."
End Sub

In order to obtain a more accurate translation, you can use (instead of "auto"): "es" for Spanish, "ru" for Russian, "ro" for Romanian, "nl" for "Duch" etc. You can find a language abbreviation by looking into Google Translate source and searching for 'English'. You will find an area where all possible language appear with their abbreviation to be used...

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • @Linga: Glad I could help! – FaneDuru Sep 29 '20 at 12:47
  • FaneDuru one small doubt value 色值:744V-#6F4048 converted as Color value: 744V-. Why after - the strings are not coming? – Linga Sep 29 '20 at 14:51
  • Not sure I understood where is the problem... when you manually try translating it, do you obtain a different string? Did you use "auto"? – FaneDuru Sep 29 '20 at 19:43
  • @Linga: It looks to be a matter of URL encoding... The character "#" set the right string side and nothing after it is processed. Please try the next testing way: Put the string in discussion in a cell and activate it. Then, try running the next code line: `Debug.Print GTranslate(WorksheetFunction.EncodeURL(ActiveCell.Value), "zh-CN", "en")`. It works as expected in my case... I did not face such a situation until now. If it works for you, too, the function call must be adapted in this way for everything. Please, **confirm that it works in this way** and I will adapt the above code, too. – FaneDuru Sep 30 '20 at 08:06
  • @Linga: The above solution works for "auto" instead of "zh-CN", too... – FaneDuru Sep 30 '20 at 08:11
  • @Linga: Didn't the solution I proposed above solved your problem? It works in my case for any kind of phrases. I already adapt the function to do that and no more need to encode the string prior to function call. 1. I do not care about notoriety at all! 2. I can see you changed your mind regarding the above code like being **accepted answer**. May I know why? Did you find a better solution? I would only like to understand why. – FaneDuru Oct 01 '20 at 07:10
  • Its partially solving.. Its not working with the words with - – Linga Oct 02 '20 at 05:34
  • @Linga: But, does the manual way work? Is it happening only when try translating from Chinese? Can you show such an example? – FaneDuru Oct 02 '20 at 06:42
  • 1
    Now Google changed their page layout, the div's classname is "result-container" instead of "t0". With that change it works flawlessly, even with large amount of data (I had to translated 14k text, added Application.Wait(Now + TimeValue("0:00:02")) to wait 2 seconds between each call. – Attila Aug 21 '22 at 12:00
0

@FaneDuru solution works with these fix:

Similar with Youtube Automate Language Translations Using Excel VBA by Dinesh Kumar Takyar "t0" changed.

Replace If objDiv.className = "t0" Then with

 If objDiv.className = "result-container" Then

Note: Do not look at the Google translate regular web page HTML, the "/m?" stands for "mobile" using the Google translate mobile page that has a different and simpler HTML code.

Noam Brand
  • 335
  • 3
  • 13