Page 1 of 7 123 ... LastLast
Results 1 to 10 of 68

Thread: Translating A Spreadsheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,345
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Translating A Spreadsheet

    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 more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  2. #2
    Board Regular
    Join Date
    Sep 2012
    Location
    Melbourne Australia
    Posts
    1,534
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Translating A Spreadsheet

    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.
    Write a man a macro he is happy for the day....teach a man to write a macro, he'll be happy forever!

  3. #3
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,345
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Translating A Spreadsheet

    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.
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  4. #4
    Board Regular
    Join Date
    Sep 2012
    Location
    Melbourne Australia
    Posts
    1,534
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Translating A Spreadsheet

    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.
    Write a man a macro he is happy for the day....teach a man to write a macro, he'll be happy forever!

  5. #5
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,657
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Translating A Spreadsheet

    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 by Jon von der Heyden; Jan 8th, 2013 at 07:48 AM.
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  6. #6
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,345
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Translating A Spreadsheet

    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?
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  7. #7
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,657
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Translating A Spreadsheet

    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).
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  8. #8
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,345
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Translating A Spreadsheet

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

    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
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  9. #9
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    London, UK
    Posts
    10,657
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Translating A Spreadsheet

    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...
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  10. #10
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,345
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Translating A Spreadsheet

    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.
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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