Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 22

Thread: Help with code on language translation file

  1. #1
    VBAX Regular
    Joined
    Jul 2017
    Posts
    23
    Location

    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

  2. #2
    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

  3. #3
    VBAX Regular
    Joined
    Jul 2017
    Posts
    23
    Location
    Thanks Yasser but the macro stuck on this statement:
     ' Wait for the results.
        Do
            Sleep 10
        Loop While oElement.outerText Like "*..."

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

  5. #5
    VBAX Regular
    Joined
    Jul 2017
    Posts
    23
    Location
    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)

  6. #6
    Hello
    Try to use this line instead
    Declare PtrSafe Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

  7. #7
    VBAX Regular
    Joined
    Jul 2017
    Posts
    23
    Location
    Now it will actually translate the text, but it still gets stuck in the same do loop.

  8. #8
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    With a bit of tidying up
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    VBAX Regular
    Joined
    Jul 2017
    Posts
    23
    Location
    Thanks Mad Max, works great! You are a true gem. Very much appreciated!

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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)
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    VBAX Regular
    Joined
    Jul 2017
    Posts
    23
    Location
    Thanks again. This was even better.

  12. #12
    VBAX Regular
    Joined
    Jul 2017
    Posts
    23
    Location
    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

  13. #13
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Some minor changes
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  14. #14
    VBAX Regular
    Joined
    Jul 2017
    Posts
    23
    Location
    MD,Error.jpg
    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

  15. #15
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Probably a missing reference. Check this thread

    Note: you will need to amend that section of code to allow for blank rows.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  16. #16
    VBAX Regular
    Joined
    Jul 2017
    Posts
    23
    Location
    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

  17. #17
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  18. #18
    VBAX Regular
    Joined
    Jul 2017
    Posts
    23
    Location
    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

  19. #19
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    '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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  20. #20
    VBAX Regular
    Joined
    Jul 2017
    Posts
    23
    Location
    Thanks MD, that works perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •