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$(strText, Chr$(32), "%20")
    strText = Replace$(strText, Chr$(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", strUrl, False)
        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

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!

Hi Wegwerf,

thanks for your reply, especially for pointing out the other script.

HTML Code: 

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.

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).

Hallo !

In English, Danke !

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

Originally Posted by Marc_excel_tips

Hi Wegwerf,

thanks for your reply, especially for pointing out the other script.

HTML Code: 

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?

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.

Thanks Marc

Thanks Mark
thanks owner of the site

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

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.

i get an error in

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

How to add language to language list of this macro?

Dear,

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

Error: Object required.

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