Translating A Spreadsheet

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

Code:
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:
Code:
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.
 
Last edited:

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Using the function I posted before; the following code will create a new sheet in your workbook with a Spanish to English translation.
Code:
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

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows
I get a compile error saying Sub or function not defined and highlights as below.

Rich (BB code):
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,560
Messages
5,548,737
Members
410,869
Latest member
eSoftToolsNSFtoPSTConvert
Top