Translating A Spreadsheet

[Dazzawm]

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.

 

[dermie_72]

You could try this out:

Translate a whole file

You can have a whole file translated by a computer and displayed in an Internet browser. When you choose this kind of translation, the content in your file is sent over the Internet to a service provider.
Note Machine translation is helpful for conveying the basic subject matter of the content and for confirming whether the content is relevant to you. For important or sensitive files, human translation is recommended, because machine translation might not preserve the full meaning and tone of the text.

• In the file that you want translated, click Review, in the Language group, click Translate.
• Click Choose Translation Language.
• Under Choose document translation languages click the Translate from and Translate to languages that you want, and then click OK.
• Click Review, in the Language group, click Translate.
• Click Translate Document. The From and To languages that you selected are listed.
• Click Send.

A browser instance opens with your file in both the original language and translated into the language that you selected.
Note If this is the first time you have used translation services, click OK to install the bilingual dictionaries and enable the translation service through the Research pane. You can also see which bilingual dictionaries and machine translation services you have enabled by clicking the Translation options link in the Research pane.

 

[Dazzawm]

Thanks, but I just cant get it to work. There is no 'OK' to click. I am using 2010 is it different? It only seems to put the word that is in the first cell also and I need the whole sheet translated.

 

[dermie_72]

Sorry those instructions were for microsoft word.
Is there any way you could export it to word, and do it that way?

Good does give a lot of options for translating via a mocrosoft engine. Check it out, before you try converting to a doc and doing it that way.

 

[Jon von der Heyden]

Here is function that uses Google Translator to translate words, phrases and sentences. Needless to say internet connection is required.

Note that this does not translate your entire sheet. Instead you can use this within another sub to do the translation. It's up to you to choose what to translate but it doesn't make sense to translate formulae cells, numbers, errors, booleans etc... You can also use the function as a worksheet function.

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
    English
    Afrikaans
    Albanian
    Arabic
    Armenian
    Azerbaijani
    Basque
    Belarusian
    Bengali
    Bulgarian
    Catalan
    Chinese
    Croatian
    Czech
    Danish
    Dutch
    Esperanto
    Estonian
    Filipino
    Finnish
    French
    Galician
    Georgian
    German
    Greek
    Gujarati
    Haitian_Creole
    Hebrew
    Hindi
    Hungarian
    Icelandic
    Indonesian
    Irish
    Italian
    ***anese
    Kannada
    Korean
    Lao
    Latin
    Latvian
    Lithuanian
    Macedonian
    Malay
    Maltese
    Norwegian
    Persian
    Polish
    Portuguese
    Romanian
    Russian
    Serbian
    Slovak
    Slovenian
    Spanish
    Swahili
    Swedish
    Tamil
    Telugu
    Thai
    Turkish
    Ukrainian
    Urdu
    Vietnamese
    Welsh
    Yiddish
End Enum

Public Function Translate(ByVal strText As String, _
                          Optional ByVal eFrom As eLanguage = Auto_Detect, _
                          Optional ByVal eTo As eLanguage = English) 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

VBA Example:

Sub test()
    MsgBox Translate("Ich bin froh", German, English)
End Sub

Formula Example:
=Translate("I love cheese",5,8)

The numbers refer to the Enum eLanguage above, where the 1st item is 0 (Auto-Detect), 1 is English and so on...

Edit: Note that in Enum J_a_p_anese is starred out. This needs correcting in your code; it's the forum software that is stuffing it up.

 

[Dazzawm]

Thanks Jon for all your hard work, but this is really going over my head! You said its best not to translate an entire sheet but could I select a column and then run a code that will change it to English, then maybe another column?

 

[Jon von der Heyden]

Using the function I posted before; the following code will create a new sheet in your workbook with a Spanish to English translation.

Public Sub TranslateSheet()
    Dim wksForeign As Excel.Worksheet
    Dim wksResults As Excel.Worksheet
    Dim rngCell As Excel.Range

    Set wksForeign = Sheet1
    Set wksResults = ThisWorkbook.Worksheets.Add(After:=wksForeign)
    wksResults.Name = "Translation of " & wksForeign.Name

    Call wksForeign.Cells.Copy(Destination:=wksResults.Range("A1"))
    
    For Each rngCell In wksResults.UsedRange.SpecialCells(xlCellTypeConstants, 2)
        rngCell.Value2 = Translate(rngCell.Value2, Spanish, English)
    Next rngCell
End Sub

WARNING: If your sheet is large (full of text), this may take some time to process since it is using the internet for the translation...

I have translated only constant text values. If that still is too much then limit it to a smaller range (I am using UsedRange).

 

[Dazzawm]

I get a compile error saying Sub or function not defined and highlights as below.

Public Sub TranslateSheet()
    Dim wksForeign As Excel.Worksheet
    Dim wksResults As Excel.Worksheet
    Dim rngCell As Excel.Range

    Set wksForeign = Sheet1
    Set wksResults = ThisWorkbook.Worksheets.Add(After:=wksForeign)
    wksResults.Name = "Translation of " & wksForeign.Name

    Call wksForeign.Cells.Copy(Destination:=wksResults.Range("A1"))
    
    For Each rngCell In wksResults.UsedRange.SpecialCells(xlCellTypeConstants, 2)
        rngCell.Value2 = Translate(rngCell.Value2, Spanish, English)
    Next rngCell
End Sub

 

[Jon von der Heyden]

You still need the function I posted in post #5. Copy that code to a new module and then try the code again.

Also note that my code in post #7 refers to Sheet1 as the sheet to translate. You may need to adjust that. I have used the sheet CodeName but you can also refer to your sheet by Name, e.g. Sheets("Input Sheet")... or whatever it is called...

 

[Dazzawm]

Now I am getting a runtime error and it debugs to Set wksForeign = Sheet1

I did as you said and the data is on the worksheet called 'Sheet1'. I have taken just a small amount of data out of the original workbook to see if it works.

This is what I did.

1. Opened VBA and selected insert>module and pasted all the codes you said so there should be 3 seperated by lines correct?

2. I selected TranslateSheet from the macro list and the above error occurs.