How to Google translate from French to English?

Question

With Google Translate, the French to English is not returning the correct values when using the code below.

If I manually use the Google Translate app I get the correct translation.

For example;
From code of "salle de l'émetteur", returns "director's room".
From the Google Translate app, correctly returns "transmitter room".

If I inspect the elements in the Google Translate app, I see the correct translation at

span class="tlid-translation translation" lang = "en" 

I can't figure out how to get this "innertext" from 'span title class'

Is there a way to get the translation from

span class="tlid-translation translation" lang = "en"' 

instead of

objDivs = objHTML.getElementsByTagName("div"), objDiv.className = "t0"

Public Function Translate(strInput As String, strSourceLng As String, strTargetLng As String) As String

    Dim strURL As String
    Dim objHTTP As Object
    Dim objHTML As Object
    Dim objDivs As Object
    Dim objDiv As Object
    Dim strTranslated As String
    
    ' send query to web page
    strURL = "https://translate.google.com/m?hl=" & strSourceLng & _
        "&sl=" & strSourceLng & _
        "&tl=" & strTargetLng & _
        "&ie=UTF-8&prev=_m&q=" & strInput

    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") 'late binding
    objHTTP.Open "GET", strURL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ""
    ' create an html document
    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
            strTranslated = objDiv.innerText
            If strTranslated <> "" Then 
               Translate = strTranslated
             End If
        End If
        
    Next objDiv
    
    Set objHTML = Nothing
    Set objHTTP = Nothing

End Function

Answer 1

If you use early bound html document i.e. MSHTML.HTMLDocument then you get access to querySelector and can try using css selectors to target that element

e.g.

Dim html As MSHTML.HTMLDocument

Set html = New MSHTML.HTMLDocument

html.body.innerHTML = objHTTP.responseText

Debug.Print html.querySelector(".translation[lang=en]").innerText

Requires VBA>Tools>References> Add reference to Microsoft HTML Object Library.

This assumes the translation is in the .innerText. It would help to share the relevant part of the response showing the translation.

Answer 2

Thanks QHarr,

I tried your recommendation, but get error 91 "Object variable or With block not set"... I must be missing something or it's just not clear to me.

Maybe the translation issue is with "XMLHTTP" because it seems to return the same translation results when you use try the =Translate(A1,"fr","en") to a cell in Excel.

I did however try your recommendation Excel vba Translate IE.Document empty and this does return the correct translated response "salle de l'émetteur" now = "transmitter room" with ie.navigate2 "https://translate.google.com/#" & "fr" & '/' & "en" & "/" & str". However, this method is too slow to translate worksheets, since I need to translate worksheets 60 rows x 36 cols.

I tweaked your code up so that it will open IE and then loop through each cell with just the one IE (Goggle Translate) window open, instead of open/close the window for each cell data translation. Works super quick in comparison. I just need to figure out timing issues because it's doesn't always put the correct data to the cells when it fetches the translation from Google Translate.

I sometimes get a 'run time error' at MyStrings = ie.document.querySelector (".translation").innerText because it's empty. So I added a dwell time to the process. It seems that <0.5 sec not long enough but seems to work at >0.8 sec per translated cell.

At least it's a step in the right direction. I'll keep playing with the timing to optimize the process, unless there's some other workable solutions or recommendations.

So after play around with this, my crud code below seems to be doing the job and seem to translate faster...so far!

    Function Translate_fr_en()
    '***This function loops through an array called Data0 loaded with all the values of the worksheet "Translate_Sheet". 
    '    It translates each row of select columns to a mirror image temp worksheet called "Temp". 
    '    It skip past columns not requiring translations since the values are numeric.

    Dim str, strTranslated, TempStr As String
    Dim ws As Worksheet
    Dim i, ii As Integer
    Dim col As Integer
    Dim LastRow As Integer
    Dim ie As Object
    Dim Flag As Boolean

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Temp")
    Set ie = CreateObject("InternetExplorer.Application")
    rcount = 3 'Temp worksheet row  counter
    ii = 0 'array row counter
    lrow = wb.Sheets("Translate_Sheet").UsedRange.rows.count 'worksheet requiring translation
    LastRow = lrow - 2
    With ie
        For col = 1 To 36
            Select Case col
            Case 1, 5, 6, 9, 10, 11, 18, 21, 31, 32, 33, 34, 36 'Selected columns containing text to translate...other columns not need translation because of numeric values
                'Translate columns with text values requiring translation
                Do Until ii = LastRow '# of rows
                    i = col - 1
                    str = Data0(i, ii) '"Translate_Worksheet" worksheet data preloaded into Data0() array
                    If str = "" Then 'If blank row than do nothing and skip translation
                        ii = ii + 1 'Array row counter
                    Else 'then translate columns with text
            
    skip:
                        .navigate2 "https://translate.google.com/#" & "fr" & "/" & "en" & "/" & str
                        While .busy Or .readystate < 4: DoEvents: Wend
                        If Flag = False Then
                            Application.Wait (Now + timevlue("0:00:01") / 1.3) 'wait 0.769 second for the first tanslation otherwise returns blank value
                            Flag = True
                        End If
                        strTranslated = ie.document.querySelector(".translation").innerText 'get translated text
                        'String comparison...if translated text has "..." at the end of string do again.
                        If strTranslated = CStr(TempStr & "...") Then
                            GoTo skip
                        End If
                        ws.Cells(rcount, col) = strTranslated 'load cell with translation
                        TempStr = ws.Cells(rcount, col)  'TempStr is temp string to hold value for comparison
                        rcount = rcount + 1
                    End If
                Loop
                ii = 0
                rcount = 3
            Case Else 'load Temp worksheet columns with numeric values not requiring translation
                ii = 0
                rcount = 3
                Do Until ii = LastRow
                    str = Data0(i, ii) 'worksheet data
                    ws.Cells(rcount, col) = str
                    ii = ii + 1
                    rcount = rcount + 1
                Loop
                ii = 0
                rcount = 3
            End Select
        Next col
    End With
    Set ie = Nothing

    End Function