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

Question

Currently this is the code I have used (successfully) to get the result from google translate after loading the website (from a generated URL that still brings up google translate correctly) in an IE object (I have not touched the file in 2 days and tried it the day after google updated their google translate website):

Function GetTransItem(IE As Object) As String

Dim strInnerHTML As String
Dim ArraySplitHTML
Dim iArrayItem As Long
Dim strTranslated As String

strInnerHTML = IE.Document.getElementById("result_box").innerHTML

'some other code here to fix hmtl character encodings, clean up HTML, etc. etc.

GetTransItem = strTranslated

End Function

This line now gives an error as of last thursday (11/29), when google updated the google translate website:

strInnerHTML = IE.Document.getElementById("result_box").innerHTML

The HTML is completely different now and I don't know enough to find out what i need to "GET" instead of "result_box" to find the translated text on the website.

If anyone can help me figure out what line(s) of code will return me the string with the translation result in it i would greatly appreciate it.

Yeah, I don't see any Ids in the new website build - you'll have to use `GetElementsByTagName` and run down the divs to get that new `text-wrap tlid-copy-target`. — dwirony, Dec 03 '18 at 22:35

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

It looks like it is a paid service now:

"Translate API

Google also has a more robust paid offering. The Translate API can be used to power web apps, as well as translate website content.".

"Simple integration

Translation API is an easy-to-use Google REST API. You don’t have to extract text from your document, just send it HTML documents and get back translated text.".

Translation:

Google Speak: "You don’t have to extract text from your document ...".
English: "You ~~don’t have~~ are no longer able to extract text from your document ...".

Google's Translation FAQ:

"Is there any free quota?
No, the Cloud Translation API is only available as a paid service. Please see Pricing for more details.".

The results returned from an URL have now been made more difficult to scrape, to prevent bypassing the paid service. Free translation is still available for humans.

Thanks for adding this advice. Code I had working last year has stopped working and I this helps. — Ben, Jul 11 '19 at 18:06
[Here](https://stackoverflow.com/a/47249553/1898524) is an alternative method that at the time of this writing still works with VBA and is faster than using IE. — Ben, Feb 19 '20 at 14:55

QHarr · Accepted Answer · 2018-12-04T14:36:35.710

2

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:

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

edited Dec 04 '18 at 14:36

answered Dec 03 '18 at 23:14

QHarr

83,427
12
54
101

Thanks! I'll give this a shot – Zamorak Dec 03 '18 at 23:31
@QHarr .. Thank you. I can't find this class '.gt-baf-table' and nothing copied to cell A1 – YasserKhalil Dec 04 '18 at 03:51
@YasserKhalil you may have a local variation. I will post html image. – QHarr Dec 04 '18 at 07:07
At first the Arabic section is selected so I got the error. But when manually opened IE browser and selected the English section. Then I ran the code it works fine for me .. so is there a way to select English by default? – YasserKhalil Dec 04 '18 at 13:33
This worked great, however if i try to translate more than one word, this table does not populate so it doesnt copy anything. Is it possible to get the text directly from that result box? Or will i have to translate word by word? – Zamorak Dec 04 '18 at 14:31
1

Thanks, i was able to find the right spot in the code but seeing it all put together is helpful thank you. Also using the HTML library instead of a ton of string manipulation like i did is much more efficient. – Zamorak Dec 04 '18 at 15:07
@QHarr Sometimes google will split the text into multiple spans, the HTML looks like this: `NAM_L42L_TR2k2_3 evil _ Part 1 part 2 .pdf (bill board letter) We will send you thank you

Wish` I can't figure out how to get the text from all of these elements or even just one. Ive tried using a variety of getelementsbytagname, getelementsbyid, queryselector but i cant figure it out. – Zamorak Dec 05 '18 at 19:08
querySelector(".tlid-translation.translation").innerText – QHarr Dec 05 '18 at 19:14
I actually tried that and still got a value of null "" when i looked i saw that google actually didnt translate the chunk of text i put in. So i need to figure out why google translate won't do it. IE throws this error when i debug the website: SCRIPT5025: The URI to be decoded is not a valid encoding translate_m.js (207,484) I may start another question to figure this out but if you have an idea of whats going on that would help greatly. – Zamorak Dec 05 '18 at 19:41
If you put up a new question and I will have a look. It sounds like you are trying to pass something to translate in an URL query string that should possibly be URLEncoded e.g. something with spaces in. – QHarr Dec 05 '18 at 19:45
@QHarr here's the question link: [https://stackoverflow.com/questions/53640216/google-translate-not-translating-in-ie-when-website-opened-from-vba] Let me know on that forum if you need more information. Thanks for all your help. – Zamorak Dec 05 '18 at 20:28

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

2 Answers2