Consulting

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

Thread: Help with code on language translation file

  1. #1

    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
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  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/#" 
    
    
    Formatting tags added by mark007
    And try again ...
    Regards

  3. #3
    Thanks Yasser but the macro stuck on this statement:
     ' Wait for the results.
    Do 
        Sleep 10 
    Loop While oElement.outerText Like "*..." 
    
    
    Formatting tags added by mark007

  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
    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) 
    
    
    Formatting tags added by mark007

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

  7. #7
    Now it will actually translate the text, but it still gets stuck in the same do loop.
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  8. #8
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,074
    Location
    With a bit of tidying up
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    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
    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,074
    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) 
    
    
    Formatting tags added by mark007
    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
    Thanks again. This was even better.

  12. #12
    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 
    
    
    Formatting tags added by mark007
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  13. #13
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,074
    Location
    Some minor changes
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    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
    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 
    
    
    Formatting tags added by mark007

  15. #15
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,074
    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
    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,074
    Location
    Range(r, r.End(xlDown)).Delete Shift:=xlUp 
    
    
    Formatting tags added by mark007
    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
    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,074
    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 
    
    
    Formatting tags added by mark007
    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
    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
  •