Help with code on language translation file

I found this file on another forum. It works great except for the output. When I am trying to translate multiple rows that template will only translate the first row and populate same in rest of the rows. Can someone please provide input how to fix this issue? You can see what it is doing on the Data Translation Output tab.
File is attached.
Thanks so much in advance,
Ken Mc

Hello
Press Alt+F11 >> Navigate to "ZZ_Functions" module >> Change "https" to "http" like that

Code:

---

Const strBASE_URL As String = "http://translate.google.com/#"

---

And try again ...
Regards

Thanks Yasser but the macro stuck on this statement:

Code:

---

' Wait for the results.
    Do
        Sleep 10
    Loop While oElement.outerText Like "*..."

---

May be because of the internet connection .. I have no idea
But I changed https to http and it worked fine for me ...

I noticed the code on the "Aux" module this code. I have a 64 bit pc. This could be the problem. If so, can that be changed?

Code:

---

Option Explicit
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

---

Hello
Try to use this line instead

Code:

---

Declare PtrSafe Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

---

Now it will actually translate the text, but it still gets stuck in the same do loop.

With a bit of tidying up

Thanks Mad Max, works great! You are a true gem. Very much appreciated!

Tried my own code on other PC and it failed. Further investigation showed it due to Results code. It appears "..." is not 3 x "." but is chr(133) "…" i.e. elipsis.
This works on my second PC

Code:

---

' Wait for the results.
    Do
        Sleep 100
    Loop While oElement.outerText Like "*" & Chr(133)

---

Thanks again. This was even better.

MD,
I'm putting the finishing touches on my Translator sheet and had a couple of questions? First should I change the part of the code you sent on both of the Do statements?
My second question is when I try to translate the attached Word doc from English to Spanish, I get an error. It translates perfectly if I put it directly to Google Translate.
Also for some reason when I try and copy to doc text into the Data translation input sheet, it only puts part of the doc text into the "A" column.

Code:

---

' Wait for the results.
    If i < lrow Then
        Do
            Sleep 10
        Loop While oElement.outerText Like "*..."
    Else
        Do
            Sleep 10
        Loop Until oElement.outerText Like "*..."
    End If

---

Some minor changes

MD,Attachment 20074
I get this compile error on this code:

Code:

---

Sub Module2_Prepare_text_01_Clear_output_sheet()
'
'This macro will perfom action 1 - Clear output sheet

Set r = Sheets("Data_translation_output").Range("A2:B2")
    Range(r, r.End(xlDown)).Delete Shift:=xlUp
End Sub

---

Probably a missing reference. Check this thread

Note: you will need to amend that section of code to allow for blank rows.

Are you talking about my code to copy the translation to the Word file? If so, do you have an easy way to do that?
Thanks

Code:

---

Range(r, r.End(xlDown)).Delete Shift:=xlUp

---

This line will stop at the first blank row

Quote:

---

copy the translation to the Word file?

---

Are you wanting to append the translation to the Word document or to a new one. Are you also looking to import the Word text to Excel for processing?

No MD, If you look at the Start Page, I made a routine to copy the translated text to a Word file, but with the blank spaces it only copies the title and the first paragraph.
How could I amend my code to copy all the translation on any text in Column "B"?
Thanks

Code:

---

'Copy Range from Excel
 
    Dim r As Range
    With Sheets("Data_translation_output")
    Set r = Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))
    End With
    r.Copy

---

Thanks MD, that works perfectly.

or

Code:

---

Sub M_snb()
    Sheet1.UsedRange.Columns(1).Copy
    GetObject(, "Word.application").activedocument.Paragraphs(1).Range.Paste
End Sub

---

NB. Avoid any 'select' or 'activate' in VBA.

MD,
I just wanted to thank you for all your help on this. I sincerely appreciate all that you have done for me.
Regards,
Ken Mc