Translating A Spreadsheet

Dazzawm · Jan 8, 2013

Hi, I have a large spreadsheet with a lot of data that is in Spanish. Is there any way or any code etc that would translate the whole spreadsheet to English? Thanks.

The Accountant212 · May 8, 2014

raj249 said:
Hi All,

I am new to excel.Did Just copy paste stuff before.

I downloaded the file ,but It gives me error. Update code to use on 64 bit system.

Can anyone modify code to use on 64 bit.system.

Bye the way I am using win8 64bit and office 2013 64bit.

Thanks all

Hey raj249,

This is a pretty simple fix, all you have to do is add PtrSafe after any "Declare" in your code. You will probably have this code and all you have to do is add the PtrSafe as I did and that should work.

Before:
Private Declare Function WideCharToMultiByte Lib "kernel32" ( _
ByVal CodePage As Long, ByVal dwflags As Long, _
ByVal lpWideCharStr As Long, ByVal cchWideChar As Long, _
ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, _
ByVal lpDefaultChar As Long, ByVal lpUsedDefaultChar As Long) As Long

After:
Private Declare PtrSafe Function WideCharToMultiByte Lib "kernel32" ( _
ByVal CodePage As Long, ByVal dwflags As Long, _
ByVal lpWideCharStr As Long, ByVal cchWideChar As Long, _
ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, _
ByVal lpDefaultChar As Long, ByVal lpUsedDefaultChar As Long) As Long

fdiddy · Jun 16, 2014

hi,i could not get the comma code below to work for me.am i doing something wrong?

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 = auto_detect, _
                          Optional ByVal eTo As eLanguage = english, _
                          Optional ByVal blnPhonetic As Boolean = False) As String
    
    Dim strUrl
    Dim strTransText As String
    Dim strResult As String
    Dim varSplitText As Variant
    Dim lngItem As Long
    Dim delimitChar As String
delimitChar = Chr(34) & "," 'Chr(34) is the character for "
    
    If strText = "" Then
        Translate = ""
        Exit Function
    End If
    
    strText = URLEncode(strText)
    
    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




    varSplitText = Split(Split(strResult, "]],")(0), "[")




    If Not blnPhonetic Then
        For lngItem = 3 To UBound(varSplitText)
            strTransText = strTransText & Split(varSplitText(lngItem), ",")(0)
        Next
    Else
        For lngItem = 3 To UBound(varSplitText)
            strTransText = strTransText & Split(varSplitText(lngItem), "delimitChar")(2)
        Next
    End If
    strResult = Replace(strTransText, """", "")




    Translate = strResult
End Function

Crazy_eng said:
Thank You very much Jon and Cody

This was the perfect piece of code just at the right time judging by the posting dates.

It has been translating beautifully except for one sentce which I was trying to translate (I am posting in the hope this may help out othe users, if they have a similar problem)

The sentence that gave me the problem was this
"I here by declare that I have completed all work as detailed above in accordance with any relevant Technical Manual and/or device documentation"

Translating it into French the translate function returned
"Je déclare ici par ce que je viens de terminer tous les travaux comme indiqué ci-dessus"
Which is only about half of the string.

THE PROBLEM was the the full French translation had a "," in it.
"Je déclare ici par ce que je viens de terminer tous les travaux comme indiqué ci-dessus , conformément à toute Manuel technique pertinente et / ou la documentation de l'appareil"

This meant the function for stripping out the desired part of the string was spliting it at the "," and I only got the translation up to that point
For lngItem = 3 To UBound(varSplitText)
strTransText = strTransText & Split(varSplitText(lngItem), ",")(0)
Next

THE SOLUTION
The returned translated string looks like this "[Translated Text]","[Original Text]"
so change the delimiter to a ", and it will be able to identify the end of the translated string insted of the the first comma

This is what I did
Dim delimitChar as string
delimitChar = Chr(34) & "," 'Chr(34) is the character for "

change the problem line to
For lngItem = 3 To UBound(varSplitText)
strTransText = strTransText & Split(varSplitText(lngItem), delimitChar)(2)
Next

I am just a novice, but this solved the problem for me.
Maybe there is a nicer way to do this?

Could you tell me if this might lead to other problems, with other translations?

Thanks again guys, top piece of code

godsent7 · Jul 9, 2014

First Post!!!
I figured out how to correct the comma issue by changing the parameters mentioned above.
Just copy paste my text!

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 = auto_detect, _
                          Optional ByVal eTo As eLanguage = english, _
                          Optional ByVal blnPhonetic As Boolean = False) As String
    
    Dim strUrl
    Dim strTransText As String
    Dim strResult As String
    Dim varSplitText As Variant
    Dim lngItem As Long
    
    If strText = "" Then
        Translate = ""
        Exit Function
    End If
    
    strText = URLEncode(strText)
    
    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

    varSplitText = Split(Split(strResult, "]],")(0), "[")
Dim delimitChar As String
delimitChar = Chr(34) & "," 'Chr(34) is the character for "
    If Not blnPhonetic Then
        For lngItem = 3 To UBound(varSplitText)
strTransText = strTransText & Split(varSplitText(lngItem), delimitChar)(0)
        Next
    Else
        For lngItem = 3 To UBound(varSplitText)
strTransText = strTransText & Split(varSplitText(lngItem), delimitChar)(2)
        Next
    End If
    strResult = Replace(strTransText, """", "")

    Translate = strResult
End Function

fdiddy · Jul 10, 2014

this is not working for me

strText = URLEncode(strText)

Marked in Yellow is

Code:

Public Function Translate(ByVal strText As String, _
Optional ByVal eFrom As eLanguage = auto_detect, _
Optional ByVal eTo As eLanguage = english, _
Optional ByVal blnPhonetic As Boolean = False) As String

Symon · Sep 15, 2014

Jon von der Heyden said:
Cody also added another argument to the function so that you can choose either the literal translation or the phonetic translation. The default is literal translation.

You can download a sample workbook here.

I will try and get around to updating the blog post - hopefully this year still. Thanks Cody Holland for sharing your discovery with me.

I'm lost, all I am getting is #VALUE, do I need to set up a library reference to something? none of the suggested code or the example workbook in this thread works for me!

shiv1823 · Apr 16, 2015

Dear Friends
The UDF translate function is not functioning. Any reasons?

Harvey12 · Apr 16, 2015

Hi all,

I am having the same issue as Symon, I have opened the example workbook and the phrases are returning the #VALUE error.

Can someone please assist.

Harvey12 · Apr 16, 2015

*bump*

Harvey12 · Apr 24, 2015

*bump*

tdh777 · Jun 21, 2015

Harvey12 said:
Hi all,

I am having the same issue as Symon, I have opened the example workbook and the phrases are returning the #VALUE error.

Can someone please assist.

Hi all.

I tracked down the #value problem.

Google has now introduced a CAPTCHA input screen in response to the translation-request URL being posted. As the translate UDF is not prepared for that, eventually it just times out/fails on Call.Send and thus Excel returns #VALUE. (sorry I couldn't get an image of the whole captcha screen inserted in this reply..)

About this page

Our systems have detected unusual traffic from your computer network. This page checks to see if it's really you sending the requests, and not a robot. Why did this happen?

IP address: 99.999.112.152
Time: 2015-06-22T00:19:00Z
URL: http://translate.google.com/translate_a/t?
client=t&text=i%20go%20to%20school&hl=en&sl=en&tl=fr&multires=1&pl=1&ram=1&sc=1

I'll see if I can work a way round the captcha by using an updated version of the google translate API but obviously they don't want you to be calling the google translate Api directly then this routine is no longer going to work...

Ah ! Further to the above, the following from Google's site :

Important: Google Translate API v2 requires billing information for all accounts before you can start using the service. See instructions below on how to enable billing.]/quote]

BASICALLY IT'S NO LONER A FREE SERVICE... you have to subscribe and then it costs depending on how much text you translate :

Usage fees:

Translation:

$20 per 1 M characters of text, where the charges are adjusted in proportion to the number of characters actually provided. For example, if you were to translate 500K characters, you would be billed $10.

Language Detection:

$20 per 1 M characters of text, where the charges are adjusted in proportion to the number of characters actually provided.

Usage limits:

Google Translate API has a default limit of 2 M chars/day. You can increase this limit up to 50 M chars/day in the Developers Console by following the instructions below.

If you need to translate more than 50 M chars/day, please contact us.

Click to expand...

I can still see a way around it but it involves rewriting the routine to extract the text back from the public translate webpages, rather than using the API.

Cheers,
Warren.

Translating A Spreadsheet

Dazzawm

Well-known Member

The Accountant212

New Member

Excel Facts

fdiddy

New Member

godsent7

New Member

fdiddy

New Member

Symon

New Member

shiv1823

New Member

Harvey12

Board Regular

Harvey12

Board Regular

Harvey12

Board Regular

tdh777

Board Regular

Similar threads

Forum statistics

Share this page

Translating A Spreadsheet

Well-known Member

New Member

Excel Facts

New Member

New Member

New Member

New Member

New Member

Board Regular

Board Regular

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock