Closed Thread
Results 1 to 20 of 20

Macro: translate text from cell using google translate

  1. #1
    Registered User
    Join Date
    04-02-2015
    Location
    Netherlands
    MS-Off Ver
    2007
    Posts
    2

    Post Macro: translate text from cell using google translate

    Hi folks,

    first of all, let me say this is a great forum. I use it regularly to solve issues I have with macro's.
    And I have issues often, as I am not very keen in VBA.
    Unfortunately, this time I couldn't find the answer on the forum, so thats why I made this post and hopefully someone can help me out.

    A couple of months ago, I found a great macro/VBA script on this forum that made it possible to translate text from a cell into another language using google translate. It worked great then, but I tried the macro/VBA script yesterday and suddenly it didn't work anymore.
    It looks like something has been changed by google translate, that messes up the macro/VBA script .

    Hereby the macro:
    PHP Code: 
    Option Explicit

    Private Const strSHORTCODES As String ",en,af,sq,ar,hy,az,eu,be,bn,bg,ca,zh,hr,cs,da,nl,eo,et,tl,fi,fr,gl,ka,de,el,gu,ht,iw,hi,hu,is,id,ga,it,ja,kn,ko,lo,la,lv,lt,mk,ms,mt,no,fa,pl,pt-PT,ro,ru,sr,sk,sl,es,sw,sv,ta,te,th,tr,uk,ur,vi,cy,yi"

    Public Enum eLanguage
        auto_detect 
    0
        English 
    1
        Afrikaans 
    2
        Albanian 
    3
        Arabic 
    4
        Armenian 
    5
        Azerbaijani 
    6
        Basque 
    7
        Belarusian 
    8
        Bengali 
    9
        Bulgarian 
    10
        Catalan 
    11
        Chinese 
    12
        Croatian 
    13
        Czech 
    14
        Danish 
    15
        Dutch 
    16
        Esperanto 
    17
        Estonian 
    18
        Filipino 
    19
        Finnish 
    20
        French 
    21
        Galician 
    22
        Georgian 
    23
        German 
    24
        Greek 
    25
        Gujarati 
    26
        Haitian_Creole 
    27
        Hebrew 
    28
        Hindi 
    29
        Hungarian 
    30
        Icelandic 
    31
        Indonesian 
    32
        Irish 
    33
        Italian 
    34
        Japanese 
    35
        Kannada 
    36
        Korean 
    37
        Lao 
    38
        Latin 
    39
        Latvian 
    40
        Lithuanian 
    41
        Macedonian 
    42
        Malay 
    43
        Maltese 
    44
        Norwegian 
    45
        Persian 
    46
        Polish 
    47
        Portuguese 
    48
        Romanian 
    49
        Russian 
    50
        Serbian 
    51
        Slovak 
    52
        Slovenian 
    53
        Spanish 
    54
        Swahili 
    55
        Swedish 
    56
        Tamil 
    57
        Telugu 
    58
        Thai 
    59
        Turkish 
    60
        Ukrainian 
    61
        Urdu 
    62
        Vietnamese 
    63
        Welsh 
    64
        Yiddish 
    65
    End Enum

    Public Function Translate(ByVal strText As String_
                              Optional ByVal eFrom 
    As eLanguage English_
                              Optional ByVal eTo 
    As eLanguage Dutch) As String
        Dim strUrl 
    As String
        Dim strResult 
    As String

        strText 
    Replace$(strTextChr$(32), "%20")
        
    strText Replace$(strTextChr$(160), "%20")

        
    strUrl "http://translate.google.com/translate_a/t?client=t&text={S}&hl=en&sl={F}&tl={T}&multires=1&pc=0&rom=1&sc=1"
        
    strUrl Replace$(strUrl"{S}"strText)
        
    strUrl Replace$(strUrl"{F}"Split(strSHORTCODES",")(eFrom))
        
    strUrl Replace$(strUrl"{T}"Split(strSHORTCODES",")(eTo))

        
    With CreateObject("MSXML2.XMLHTTP")
            
    Call .Open("get"strUrlFalse)
            
    Call .Send
            strResult 
    = .responseText
        End With

        strResult 
    Replace$(Mid$(CStr(Split(strResult",")(0)), 4), Chr$(34), "")
        
    Translate strResult
    End 
    Function 
    If I try to translate something using this script, I get #value as answer, where normally I would get the translation.

    Anyone understand why or where it goes wrong?
    Or does anybody have another macro/script for translating text from a cell into another language??

    All help is welcome!
    And, if we can get this script working again....then we have a darn good script back. Because it worked great!!!

    Thanks in advance!
    Greetings,
    Marc

  2. #2
    Registered User
    Join Date
    03-17-2015
    Location
    London
    MS-Off Ver
    Office 2013
    Posts
    2

    Re: Macro: translate text from cell using google translate

    Hello there,

    I have the same problem. I used to use the exact same script.

    I found an other script here in the forum, that works quite well.
    http://www.excelforum.com/excel-prog...-from-vba.html

    I really dont have any clue about VBA scripting, so i dont know how the script works, but you have to change the languages in the script.

    Greetings!

  3. #3
    Registered User
    Join Date
    04-02-2015
    Location
    Netherlands
    MS-Off Ver
    2007
    Posts
    2

    Re: Macro: translate text from cell using google translate

    Hi Wegwerf,

    thanks for your reply, especially for pointing out the other script.
    HTML Code: 
    http://www.excelforum.com/excel-prog...-from-vba.html
    Somehow I couldn't get the scripts working in my own file, so I decided to just download the "Japanese" file that was in Fred Lo's post.
    The script in that file works great > thanks for that Fred, I have added your reputation

    I tweaked the script a bit, as I didn't find it handy to change the language in the script itself.
    Specially if the macro needs to be used by people that don't understand much of excel.
    So I turned the "Japanese" file into a more "tool"-like sheet, that is easy to use for everyone if you just follow the directions on the start sheet.

    The only thing I am still struggling with, is that the macro works great but is a bit slow sometimes.
    Especially when you put more then 1000 cells in it for translation it takes a while before it's done running.
    Also, the macro sometimes get stuck if the input is not correct (i.e. input = start...), the macro seems to get stuck on those "...".
    It would be great if I had something like an "on-error-skip" script that could be added to the script.

    But for now it's working pretty fine.
    Attached the "tool"-like version of my file (maybe you find it easy to use as well Wegwerf.

    Again, thanks for pointing me on the other script.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-14-2016
    Location
    Stuttgart, Germany
    MS-Off Ver
    2013
    Posts
    1

    Re: Macro: translate text from cell using google translate

    Hallo Marc,

    ich habe dein Tool gerade ausprobiert, da passiert aber leider nichts. Der "übersetzte" Text ist genau das gleiche wie der Originaltext (habe versucht von türkisch zu englisch zu übersetzen).

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,353

    Re: Macro: translate text from cell using google translate

    Hallo !

    In English, Danke !

  6. #6
    Registered User
    Join Date
    05-03-2017
    Location
    coimbatore
    MS-Off Ver
    MS OFFICE 2010
    Posts
    2

    Re: Macro: translate text from cell using google translate

    This tool is working only in 32 bit machine. Can someone let me know how to make it work in windows 64 bit machine?

  7. #7
    Registered User
    Join Date
    08-01-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    1

    Re: Macro: translate text from cell using google translate

    Quote Originally Posted by Marc_excel_tips View Post
    Hi Wegwerf,

    thanks for your reply, especially for pointing out the other script.
    HTML Code: 
    http://www.excelforum.com/excel-prog...-from-vba.html
    Somehow I couldn't get the scripts working in my own file, so I decided to just download the "Japanese" file that was in Fred Lo's post.
    The script in that file works great > thanks for that Fred, I have added your reputation

    I tweaked the script a bit, as I didn't find it handy to change the language in the script itself.
    Specially if the macro needs to be used by people that don't understand much of excel.
    So I turned the "Japanese" file into a more "tool"-like sheet, that is easy to use for everyone if you just follow the directions on the start sheet.

    The only thing I am still struggling with, is that the macro works great but is a bit slow sometimes.
    Especially when you put more then 1000 cells in it for translation it takes a while before it's done running.
    Also, the macro sometimes get stuck if the input is not correct (i.e. input = start...), the macro seems to get stuck on those "...".
    It would be great if I had something like an "on-error-skip" script that could be added to the script.

    But for now it's working pretty fine.
    Attached the "tool"-like version of my file (maybe you find it easy to use as well Wegwerf.

    Again, thanks for pointing me on the other script.
    Hi,
    Thank you for providing the template. When I am trying to translate multiple rows that template only translate for the first row and populate same in rest of the rows. can someone please provide input how to fix this issue?

  8. #8
    Registered User
    Join Date
    07-27-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    4

    Re: Macro: translate text from cell using google translate

    Hi Marc,

    Awesome work!

    Just want one help.

    When I put source strings (even in plain text) in column A, it translates only cell A2.

    Could you please have a look?

    Thanks.

  9. #9
    Registered User
    Join Date
    11-08-2018
    Location
    Khobar, Saudi Arabia
    MS-Off Ver
    2017
    Posts
    1

    Re: Macro: translate text from cell using google translate

    Thanks Marc

  10. #10
    Registered User
    Join Date
    12-06-2018
    Location
    ksa
    MS-Off Ver
    2003
    Posts
    1

    Re: Macro: translate text from cell using google translate

    Thanks Mark
    thanks owner of the site

  11. #11
    Registered User
    Join Date
    01-17-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    1

    Re: Macro: translate text from cell using google translate

    Please help me to Translate the cell text using vba from English to Spanish Language.
    Using Windows 10 and Excel version 2016.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,743

    Re: Macro: translate text from cell using google translate

    Quote Originally Posted by Moonday View Post
    Please help me to Translate the cell text using vba from English to Spanish Language.
    Using Windows 10 and Excel version 2016.
    Hi, welcome to the forum

    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  13. #13
    Registered User
    Join Date
    03-13-2019
    Location
    Culiacán, Sinaloa
    MS-Off Ver
    v13
    Posts
    1

    Re: Macro: translate text from cell using google translate

    0

    I have a Question: in this macro have a mystaque after Run it:

    Set oElement = IE.document.getElementById("gt-res-content")

    In this line - Error: '424' Object Required

    YOU CAN HELP ME PLEASE.

  14. #14
    Registered User
    Join Date
    10-03-2019
    Location
    morocco
    MS-Off Ver
    2013
    Posts
    1

    Post Re: Macro: translate text from cell using google translate

    i get an error in

    Set oElement = IE.document.getElementById("gt-res-content")

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,743

    Re: Macro: translate text from cell using google translate

    Quote Originally Posted by mouadaje View Post
    i get an error in

    Set oElement = IE.document.getElementById("gt-res-content")
    did you miss my post #12?

  16. #16
    Registered User
    Join Date
    09-20-2020
    Location
    Kazakhstan
    MS-Off Ver
    Office 2010
    Posts
    1

    Re: Macro: translate text from cell using google translate

    How to add language to language list of this macro?

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,743

    Re: Macro: translate text from cell using google translate

    Quote Originally Posted by aseto View Post
    How to add language to language list of this macro?
    you missed my post #12 too? or maybe members are just not reading rules and posts in threads?

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  18. #18
    Registered User
    Join Date
    10-11-2018
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    2

    Re: Macro: translate text from cell using google translate

    Dear,

    Can you help me with a error on this line: Set oElement = IE.document.getElementById ("gt-res content")

    Error: Object required.

  19. #19
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,743

    Re: Macro: translate text from cell using google translate

    Quote Originally Posted by Matjuhnl View Post
    Dear,

    Can you help me with a error on this line: Set oElement = IE.document.getElementById ("gt-res content")

    Error: Object required.
    did you REALLY not see the thread immediately above yours??? the 1 that said DONT post your question on someone else's thread??

  20. #20
    Registered User
    Join Date
    01-05-2021
    Location
    France
    MS-Off Ver
    10
    Posts
    1

    Re: Macro: translate text from cell using google translate

    This macro does not work with Excel version 2011 (Microsoft Officie Family and Small Business 2016)

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Get google translate result from vba?
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 01-29-2021, 07:36 PM
  2. How to catch errors from Google Translate
    By MrsMac in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-29-2014, 09:18 AM
  3. Sample Translate English to Arabic Text Using MS Translate
    By pidyok in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-21-2013, 08:18 AM
  4. need Google Translate in Excel 2003
    By appu_gusai in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2013, 08:27 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1