Using Excel VBA to copy and paste a google translate translation into an Excel workbook

John Carney
John Carney used Ask the Experts™
on
I have a macro that will open up google translate and return the English translation of a designated Spanish text. I can then copy the translation, activate my Excel workbook and paste it into a designated cell. What I'd like to be able to do is have additional code in the macro that will do that as well.

Here is my code. I've also attached the workbook.
Sub TranslateText()
Application.ScreenUpdating = False
Dim cel As Range, TxT As String
Set cel = [E2]
TxT = Replace(cel.Value, " ", "%20")
   [J2].Hyperlinks.Add Anchor:=[J2], Address:= _
       "https://translate.google.com/", SubAddress:="es/en/" & TxT, _
        TextToDisplay:="https://translate.google.com/#es/en/" & TxT
   '[J2].Font.ColorIndex = 2
   [J2].Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
   'Here I need code that will copy the English translation
ThisWorkbook.Activate
   'and then append it to a designated cel
'[E4].Select
'ActiveSheet.Paste
[E6].Select
Application.ScreenUpdating = True
End Sub

Open in new window

TranslatorMacro.xlsm
The link to google translate is in cell J2. The macro populates J2 with the phrase to be translated: "https://translate.google.com/#es/en/40C,%2015A%20IFE%20no%20permite%20sellecionar%20peliculas.%20Se%20congela%20y%20luego%20vuelve%20a%20menu%20inicial.%20Reset%20y%20no%20funciona"

Thanks,
John
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reliability Business Tools Analyst II
Commented:
I found another code that solves my problem perfectly.
Public Sub GetInfo()
    Dim IE As Object, t As Date, ws As Worksheet
    Const MAX_WAIT_SEC As Long = 5

Set IE = CreateObject("InternetExplorer.application")
    Set ws = ThisWorkbook.Worksheets("Sheet1")
Application.ScreenUpdating = False
    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 = [E2]
        
        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(4, 5) = translationText
       .Quit
    End With
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial