PDA

View Full Version : [SOLVED] Help with code on language translation file



kwesmc
08-04-2017, 11:20 AM
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

YasserKhalil
08-04-2017, 11:52 AM
Hello
Press Alt+F11 >> Navigate to "ZZ_Functions" module >> Change "https" to "http" like that

Const strBASE_URL As String = "http://translate.google.com/#"
And try again ...
Regards

kwesmc
08-04-2017, 01:25 PM
Thanks Yasser but the macro stuck on this statement:

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

YasserKhalil
08-04-2017, 01:48 PM
May be because of the internet connection .. I have no idea
But I changed https to http and it worked fine for me ...

kwesmc
08-04-2017, 03:27 PM
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?

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

YasserKhalil
08-04-2017, 10:59 PM
Hello
Try to use this line instead

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

kwesmc
08-05-2017, 07:14 AM
Now it will actually translate the text, but it still gets stuck in the same do loop.

mdmackillop
08-05-2017, 10:12 AM
With a bit of tidying up

kwesmc
08-05-2017, 12:37 PM
Thanks Mad Max, works great! You are a true gem. Very much appreciated!

mdmackillop
08-05-2017, 01:22 PM
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

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

kwesmc
08-05-2017, 03:55 PM
Thanks again. This was even better.

kwesmc
08-13-2017, 10:47 AM
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.


' 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

mdmackillop
08-13-2017, 12:08 PM
Some minor changes

kwesmc
08-13-2017, 12:38 PM
MD,20074
I get this compile error on this 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

mdmackillop
08-13-2017, 12:52 PM
Probably a missing reference. Check this thread (http://www.vbaexpress.com/forum/showthread.php?51472-Compile-Errror-Can-t-Find-Project-or-Library&p=319150&viewfull=1#post319150)

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

kwesmc
08-13-2017, 02:06 PM
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

mdmackillop
08-13-2017, 02:19 PM
Range(r, r.End(xlDown)).Delete Shift:=xlUp
This line will stop at the first blank row


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?

kwesmc
08-13-2017, 02:57 PM
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

mdmackillop
08-13-2017, 03:45 PM
'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

kwesmc
08-13-2017, 05:22 PM
Thanks MD, that works perfectly.

snb
08-14-2017, 04:30 AM
or

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.

kwesmc
08-14-2017, 09:56 AM
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