vbaexcelgoogle-translatevocabulary

Excel macro to find words from Google Translate


I have an Excel sheet with almost 30.000 words in column A and I want to create a macro to search each word in Google Translate, get their meaning (or translation), put the meaing in column B (or if there is more than more meaning in column C, column D, etc.) Since I have almost 30.000 words, it is a very time consuming thing to search for each word by myself. It would be great if I can do this with a macro. Any suggestions? (Google Translate is not a "must" for me. If there is another web-site or some other way to do this, I am open to suggestions)

Note: I came across with this topic, but it did not work out the way I hoped.


Solution

  • Since the Google Translate API is not the free service it's tricker to perform this operation. However, I found a workaround on this page Translate text using vba and I made some adjustments so it could work for your purposes. Assuming that the original words are entered into the "A" column in the spreadsheet and translations should appear in the colums on the right here is the code:

    Sub test()
    Dim s As String
    
    Dim detailed_translation_results, basic_translation_results
    Dim cell As Range
    
    For Each cell In Intersect(ActiveSheet.Range("A:A"), ActiveSheet.UsedRange)
        If cell.Value <> "" Then
            detailed_translation_results = detailed_translation(cell.Value)
    
            'Check whether detailed_translation_results is an array value. If yes, each detailed translation is entered into separate column, if not, basic translation is entered into the next column on the right
            On Error Resume Next
                ActiveSheet.Range(cell.Offset(0, 1), cell.Offset(0, UBound(detailed_translation_results) + 1)).Value = detailed_translation_results
    
                If Err.Number <> 0 Then
                    cell.Offset(0, 1).Value = detailed_translation_results
                End If
            On Error GoTo 0
        End If
    Next cell
    
    End Sub
    
    Function detailed_translation(str)
    ' 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
    Dim FirstTablePosition As Long, FinalTablePosition
    
    Set IE = CreateObject("InternetExplorer.application")
    
    '   Choose input language - Default "auto"
    
    inputstring = "auto"
    
    '   Choose input language - Default "en"
    
    outputstring = "en"
    
    text_to_convert = str
    
    '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
    
    'Firstly, this function tries to extract detailed translation.
    
    Dim TempTranslation() As String, FinalTranslation() As String
    
    FirstTablePosition = InStr(IE.Document.getElementById("gt-lc").innerHTML, "<tbody>")
    LastTablePosition = InStr(IE.Document.getElementById("gt-lc").innerHTML, "</tbody>")
    
    On Error Resume Next
    TempTranslation() = Split(Mid(IE.Document.getElementById("gt-lc").innerHTML, FirstTablePosition, LastTablePosition - FirstTablePosition), "class=""gt-baf-cell gt-baf-word-clickable"">")
    
    ReDim FinalTranslation(0 To UBound(TempTranslation) - 1)
    
    For j = LBound(TempTranslation) + 1 To UBound(TempTranslation)
        FinalTranslation(j - 1) = Left(TempTranslation(j), InStr(TempTranslation(j), "<") - 1)
    Next j
    On Error GoTo 0
    
    Dim CheckIfDetailed
    
    'Check whether there is detailed translation available. If not - this function returns a single translation
    On Error Resume Next
        CheckIfDetailed = FinalTranslation(LBound(FinalTranslation))
    
        If Err.Number <> 0 Then
            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
    
            detailed_translation = result_data
            Exit Function
    
        End If
    On Error GoTo 0
    
    IE.Quit
    
    detailed_translation = FinalTranslation()
    
    
    End Function
    

    Please note that the code is extremly slow (due to anti-robot restrictions) and I cannot guarantee that Google will not block the script. However, it should work.

    The only thing you should do is to choose languages in the places marked by the appropriate comment.

    Alternatively, if you seek something faster, you can manipulate Application.Wait method (for example setting the value to 0:00:2 instead of 0:00:5) or google for Microsoft Translate.