Extract Content of Div from Google Translate with Vba

How to reference and retrieve translation result from new google translate website? (using VBA)

You can still use browser to retrieve translation info. This is just to demonstrate grabbing the translations table on the right hand side. 

Option Explicit

Public Sub GetInfo()
    Dim IE As New InternetExplorer, t As Date, clipboard As Object, ws As Worksheet
    Const MAX_WAIT_SEC As Long = 5

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    With IE
        .Visible = True
        .navigate "https://translate.google.com/#view=home&op=translate&sl=auto&tl=en"

        While .Busy Or .readyState < 4: DoEvents: Wend

        .document.querySelector("#source").Value = "Bonjour"

        Dim hTable As HTMLTable
        t = Timer
        Do
            On Error Resume Next
            Set hTable = .document.querySelector(".gt-baf-table")
            On Error GoTo 0
            If Timer - t > MAX_WAIT_SEC Then Exit Do
        Loop While hTable Is Nothing
        If Not hTable Is Nothing Then
            clipboard.SetText hTable.outerHTML
            clipboard.PutInClipboard
            ws.Cells(1, 1).PasteSpecial
        End If
        .Quit
    End With
End Sub

Check with you local version HTML the class name for the translation table on the right:

Sample Image

From the results box:

Option Explicit
Public Sub GetInfo()
    Dim IE As New InternetExplorer, t As Date, ws As Worksheet
    Const MAX_WAIT_SEC As Long = 5

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With IE
        .Visible = True
        .navigate "https://translate.google.com/#view=home&op=translate&sl=auto&tl=en"

        While .Busy Or .readyState < 4: DoEvents: Wend

        .document.querySelector("#source").Value = "je vous remercie"

        Dim translation As Object, translationText As String
        t = Timer
        Do
            On Error Resume Next
            Set translation = .document.querySelector(".tlid-translation.translation")
            translationText = translation.textContent
            On Error GoTo 0
            If Timer - t > MAX_WAIT_SEC Then Exit Do
        Loop While translationText = vbNullString

        ws.Cells(1, 1) = translationText
       .Quit
    End With
End Sub

VBA language translator UDF not working with special characters

Solved it guys!

As pointed out by @Noam Brand in Extract content of div from Google Translate with VBA, I simply used strInput = WorksheetFunction.EncodeURL(strInput) at the start of my code, so it codifies special characters before sending the query to Google Translate.

How to get the text-area element's ID of Google translate?

This worked for me

Sub dothings()

    Dim objIE As InternetExplorer

    Set objIE = New InternetExplorer
    objIE.Visible = True

    objIE.navigate "https://translate.google.com"

    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    objIE.document.getElementById("source").Value = "Hello world!"

End Sub

This is very useful read http://automatetheweb.net/

How to refer to Google translate result box in VBA

Non-input elements don't have a Value property. Try:

.getElementByID("result_box").innerText

Google translate cell value using VBA

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

Excel VBA Macro for Google Translate

You may find a solution below. I hope it can be usefull.

Public Function GoogleTranslate(strInput As String, strFromSourceLanguage As String, strToTargetLanguage As String) As String
    Dim strURL As String
    Dim objHTTP As Object
    Dim objHTML As Object
    Dim objDivs As Object, objDiv As Object
    Dim strTranslated As String

    ' send query to web page
    strURL = "https://translate.google.com/m?hl=" & strFromSourceLanguage & _
        "&sl=" & strFromSourceLanguage & _
        "&tl=" & strToTargetLanguage & _
        "&ie=UTF-8&prev=_m&q=" & strInput

    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") 'late binding
    objHTTP.Open "GET", strURL, False
    'objHTTP.setRequestHeader "Accept-Encoding", "gzip;q=1.0", "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ""

    'MsgBox objHTTP.getResponseHeader("Content-Encoding")

    Dim httpresponse() As Byte
    httpresponse = objHTTP.responseBody
    'Mod_Inflate64.Inflate httpresponse
    'MsgBox StrConv(httpresponse, vbUnicode)
    'Debug.Print StrConv(httpresponse, vbUnicode)

    ' create an html document
    Set objHTML = CreateObject("htmlfile")
    With objHTML
        .Open
        .Write objHTTP.responseText
        '#YGE 221001 for trialrun --  Z = objHTTP.responseText
        '#YGE 221001 for trialrun --  Y = InStr(Z, "result-container")
        '#YGE 221001 for trialrun --  If Y > 0 Then
        '#YGE 221001 for trialrun --      Debug.Print Z
        '#YGE 221001 for trialrun --  End If
        
        .Close
    End With
    
    '#YGE 221001 for trialrun --  Range("H1") = objHTTP.responsetext
    
    '#YGE 221001 for trialrun --  Set objDivsBody = objHTML.getElementsByTagName("body")
    Set objDivs = objHTML.getElementsByTagName("div")
    '#YGE 221001 for trialrun --  Set objDivs2 = objDivsBody(0).getElementsByTagName("div")
    '#YGE 221001 for trialrun --  Set objSpans = objHTML.getElementsByTagName("span")
    '#YGE 221001 for trialrun --  Set objSpans2 = objDivsBody(0).getElementsByTagName("span")
    
    
    '#YGE 221001 for trialrun --  Set objDivs2 = objHTML.getElementsByClassName("Q4iAWc")
    '#YGE 221001 for trialrun --  Set objDivs2 = objHTML.getElementsByClassName("JLqJ4b ChMk0b")
    
    '#YGE 221001 for trialrun --  For Each objDiv In objDivsBody
        '#YGE 221001 for trialrun --  Z = objDiv.className
        '#YGE 221001 for trialrun --  Debug.Print Z
    '#YGE 221001 for trialrun --  Next objDiv
    
    For Each objDiv In objDivs
        '#YGE 221001 for trialrun --  Z = objDiv.className
        '#YGE 221001 for trialrun --  Debug.Print Z
        
        GoogleTranslate = GoogleTranslateRecursion(objDiv.ChildNodes)
        '#YGE 221001 for trialrun --  Debug.Print GoogleTranslate
        If GoogleTranslate <> "" Then
            Exit For
        End If
        If objDiv.className = "result-container" Then
            strTranslated = objDiv.innerText
            GoogleTranslate = strTranslated
            Exit For
        End If
        
    Next objDiv

    Set objHTML = Nothing
    Set objHTTP = Nothing

End Function

Function GoogleTranslateRecursion(pobjDivs As Object) As String
    Dim objDivs As Object, objDiv As Object
    Dim strTranslated As String
    GoogleTranslateRecursion = ""
    Set objDivs = pobjDivs
    For Each objDiv In objDivs
        If objDiv.nodeName = "DIV" Then
            '#YGE 221001 for trialrun --  Z = objDiv.className
            '#YGE 221001 for trialrun --  Debug.Print Z
            strTranslated = GoogleTranslateRecursion(objDiv.ChildNodes)
            '#YGE 221001 for trialrun --  Debug.Print strTranslated
            If strTranslated <> "" Then
                GoogleTranslateRecursion = strTranslated
                Exit For
            End If
            
            If objDiv.className = "result-container" Then
                strTranslated = objDiv.innerText
                GoogleTranslateRecursion = strTranslated
                Exit For
            End If
        End If
        
    Next objDiv
End Function

Google Translate Using VBA - (Excel Macro) Issue

Looks like maybe source code changed? The code does not throw any errors, but line InStr(objHTTP.responseText, "div dir=""ltr""") always return 0, so maybe you could replace the Else part that says On Error Resume Next with:

cell.Value = Clean(CStr(Split(Split(ObjHTTP.responsetext, "<div class=""result-container"">")(1), "</div>")(0)))

Google Translate via VBA setting for accept-encoding

I had some success with Google Translate and Excel VBA using the MSXML2.ServerXMLHTTP object. I note you are using MSXML2.XMLHTTP. The solution appears to work well only setting a User-Agent request header so I did not delve into accept-encoding etc.

The differences between MSXML2.ServerXMLHTTP and MSXML2.XMLHTTP are touched upon in this question which might be useful for you.

Working code using MSXML2.ServerXMLHTTP:

Option Explicit

Sub Test()

    Debug.Print Translate("Hello", "en", "fr", True) ' french
    Debug.Print Translate("Hello", "en", "de", True) ' german
    Debug.Print Translate("Hello", "en", "pt", True) ' portuguese
    Debug.Print Translate("Hello", "en", "ru", False) ' russian - use romanised alphabet
    Debug.Print Translate("Hello", "en", "ru", True) ' russian - use cyrillic
    ' ThisWorkbook.Sheets(1).Range("A1").Value = Translate("Hello", "en", "ru", True)
    Debug.Print Translate("Hello", "en", "zh-CN", False) ' chinese simplified - use romanised alphabet
    Debug.Print Translate("Hello", "en", "zh-CN", True) ' chinese simplified - use chinese script
    ' ThisWorkbook.Sheets(1).Range("B1").Value = Translate("Hello", "en", "zh-CN", True)

End Sub

Public Function Translate(strInput As String, strFromLanguageCode As String, strToLanguageCode As String, blnTargetAlphabet As Boolean) As String

    Dim strURL As String
    Dim objHTTP As Object
    Dim objHTML As Object
    Dim objDivs As Object, objDiv
    Dim strTranslatedT0 As String
    Dim strTranslatedO1 As String

    ' send query to web page
    strURL = "https://translate.google.com/m?hl=" & strFromLanguageCode & _
        "&sl=" & strFromLanguageCode & _
        "&tl=" & strToLanguageCode & _
        "&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 ""

    ' create a html document
    Set objHTML = CreateObject("htmlfile")
    With objHTML
        .Open
        .Write objHTTP.responseText
        .Close
    End With

    ' o1 has Anglicised translation, t0 as tranlsation in target language
    Set objDivs = objHTML.getElementsByTagName("div")
    For Each objDiv In objDivs
        If objDiv.className = "o1" Then
            strTranslatedO1 = objDiv.innerText
        End If
        If objDiv.className = "t0" Then
            strTranslatedT0 = objDiv.innerText
        End If
    Next objDiv

    ' choose which to return
    If blnTargetAlphabet Then
        Translate = strTranslatedT0
    Else
        Translate = strTranslatedO1
    End If

CleanUp:
    Set objHTML = Nothing
    Set objHTTP = Nothing

End Function

Result:

Bonjour
Hallo
Olá
Privet
??????
Ni hao
??

The VBA immediate window doesn't print Cyrillic or Chinese characters but you can see this feature working by outputing to a cell:

Sample Image

December 2020 update

Looks like this method will no longer work going back maybe to mid November.

Looking at the response

  • the div class names have changed to something more obscure
  • there's some esoteric c-wiz elements doing something wonderful...
  • also, I suspect that some client side script is calling for the actual translation after the document is retrieved

Options: Selenium, Microsoft Translate, free and paid tiers for Google translation APIs ;)


Related Topics

What's The Point of HTML Forms 'Name' Attribute

Prevent Ligatures in Safari (Mavericks/iOS7) via CSS

Image Behavior Within Flexbox (Rows Embedded in Columns)

Where Is The Visual Studio HTML Designer

How to Add Placeholder in Div Tag

What's The Point of HTML Forms 'Name' Attribute

How to Vertical Align an Inline-Block in a Line of Text

Styling The '<HTML>' Element in CSS

Using Images Inside <Button> Element

Making Part of a Div Transparent

Disable Opacity on Child Element When Parent Element Has Opacity

Keep Padding from Making The Element Bigger

How to Stop an Image Displaying Outside of The Div

Why Does The List Style Disappear When Display: Block Is Added to a List Item in a List (<Ul> or <Ol>)

Inner Div with Square Ratio and Flexbox

How to Align an Input Tag to The Center Without Specifying The Width

How to Allow Text to Wrap Inside a Word If Necessary

Can Data-* Attribute Contain HTML Tags


Leave a reply



Submit