Pulling translated word from google translate result box using VBA

Question

I've been trying to translate a word passed through the transFunc function and display the translated word in a MsgBox. When I try to run my code, I get an error saying Object doesn't support this property or method. I know it is messing up at the "cleanData = " line, but I can't figure out why. I've been trying for days now, so any help is greatly appreciated. Thank you so much!!!

Public Sub transFunc(Optional Word As String)
    
    Dim IE As Object    
    Dim i As Long    
    Dim inputString As String    
    Dim outputString As String    
    Dim textToConvert As String    
    Dim resultData As String    
    Dim cleanData As Variant
    Dim Url As String    
    Dim j As Integer
    
    Set IE = CreateObject("InternetExplorer.application")
    
    inputString = "auto"    
    outputString = "es"    
    textToConvert = Word
    
    IE.Visible = True
    
    Url = "https://translate.google.com/?sl=" & inputString & "&tl=" & outputString & "&text=" & 
    
    textToConvert & "&op=translate"
    
    IE.navigate Url    
    Do Until IE.readyState = 4       
        DoEvents    
    Loop
    
    Application.Wait (Now + TimeValue("0:00:5"))    
    Do Until IE.readyState = 4        
        DoEvents    
    Loop
    
    replaceInput = IE.document.getElementById("result_box").innerHTML
    splitInput = Replace(replaceInput, "</SPAN>", "")
    cleanData = Split(splitInput, "<")
    
    For j = LBound(cleanData) To UBound(cleanData)
        resultData = resultData & Right(cleanData(j), Len(cleanData(j)) - InStr(cleanData(j), ">"))
    Next j
    
    MsgBox (resultData)    
    IE.Quit
End Sub

That line `cleanData = ...` has too many things going on to be able to easily debug: try breaking it up into separate steps so you can see where the problem is. FYI VBA has `Replace()` so use that in place of `Application.WorksheetFunction.Substitute()` — Tim Williams, Jun 28 '22 at 19:36
Thank you for your reply. I cleaned it up and it is all working except for this one specific line I can't understand "= IE.document.getElementById("result_box").innerHTML". Do you know how to successfully pull the translated word from google translate? The error is either using .getElementById, the string "result_box" (could be something else), or using .innerHTML. — Lucas, Jun 28 '22 at 19:59
Please *update your question* if you want to add code or other info. — Tim Williams, Jun 28 '22 at 20:00
I don't see any element on that page with id = "result_box" Where did you find that? — Tim Williams, Jun 28 '22 at 20:21
See https://stackoverflow.com/questions/64119110/google-translate-cell-value-using-vba — Tim Williams, Jun 28 '22 at 20:26

Pulling translated word from google translate result box using VBA

0 Answers0