+ Reply to Thread
Results 1 to 22 of 22

How do I translate text from one language to another with VBA?

  1. #1
    Registered User
    Join Date
    02-29-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    33

    How do I translate text from one language to another with VBA?

    Hi,

    I have a spreadsheet with thousands of rows of text that I need converted from one language to several other languages.

    Is there a macro I can use that taps into some free service, running down my page and giving a foreign language conversion for each cell?

    If not, is there some other way I can do this free?

    I'd really appreciate any help anyone could offer.

    Thanks so much in advance.

    Ana
    http://www.camp-hunt-shoot.com

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: How do I translate text from one language to another with VBA?

    Depends what language I suppose really as you haven't provided that info.

    You might be able to manipulate the data in this website, though it would be beyond me

    http://www.freetranslation.com/
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: How do I translate text from one language to another with VBA?

    See if this workbook helps.

    It uses Google Translate
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  4. #4
    Registered User
    Join Date
    02-29-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: How do I translate text from one language to another with VBA?

    You're amazing Marcol.

    I'm sure that will help.

    Thank you soooo much. :o)

  5. #5
    Registered User
    Join Date
    02-29-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: How do I translate text from one language to another with VBA?

    Hi everyone.

    This is a great little tool but the problem is that, if you try to use it to translate a sentence with punctuation, it stops at the first punctuation mark.

    What I mean is that, if I try to translate a sentence, it will only translate and return the portion of the sentence before the first comma or period and ignore and not return the portion of the sentence after the first punctuation mark.

    Can anyone look at the code and suggest a fix to make it return the whole sentence, maybe leaving commas and periods where they are?

    Thanks so much in advance.

  6. #6
    Registered User
    Join Date
    02-29-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: How do I translate text from one language to another with VBA?

    Hi,

    I'm so stuck without a fix for this.

    Can anyone suggest a work-around?

    thanks.

    Ana

  7. #7
    Registered User
    Join Date
    08-02-2012
    Location
    nl
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: How do I translate text from one language to another with VBA?

    Quote Originally Posted by anasttin View Post
    Hi,

    I'm so stuck without a fix for this.

    Can anyone suggest a work-around?

    thanks.

    Ana
    change the last line of the macro in.

    getGoogleTranslation = Replace(Replace(Split(x, Chr(34) & "," & Chr(34))(0), "[", ""), """", "")

    Greetings Hans

  8. #8
    Registered User
    Join Date
    09-18-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: How do I translate text from one language to another with VBA?

    Quote Originally Posted by hansjeu View Post
    change the last line of the macro in.

    getGoogleTranslation = Replace(Replace(Split(x, Chr(34) & "," & Chr(34))(0), "[", ""), """", "")

    Greetings Hans
    This entire solution was amazingly useful for me. I have chnged the last line of the macro into what HANSJEU suggested. As a result, the macro is able to go past all punctuations except ".". Any solution for this?

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: How do I translate text from one language to another with VBA?

    The original request was to translate single words.
    Hansjeu offered a modification that handles a sentence.

    Google translate can handle short sentences with some surity, but I doubt it will translate a book!

    Put each sentence in a new row.

  10. #10
    Registered User
    Join Date
    08-19-2004
    Posts
    22

    Re: How do I translate text from one language to another with VBA?

    Hi,
    Marcol, that was a good code. It is very useful. I am wondering wether it allows to hear the sound as well as Google does. Word by word would be still ok.
    Cheers \
    Baha

    Best Regards,
    Baha

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,245

    Re: How do I translate text from one language to another with VBA?

    bahadirakcan,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    Ben Van Johnson

  12. #12
    Registered User
    Join Date
    09-17-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: How do I translate text from one language to another with VBA?

    Quote Originally Posted by Marcol View Post
    See if this workbook helps.

    It uses Google Translate
    Awesome Marcol you are genius..........

  13. #13
    Registered User
    Join Date
    05-28-2013
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: How do I translate text from one language to another with VBA?

    where is the complete VBA code can anyone post it Please

  14. #14
    Registered User
    Join Date
    05-28-2013
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: How do I translate text from one language to another with VBA?

    I find the macro and it's just amazing thank you for sharing this information . only one problem , I tried putting Hebrew text in the text column and was hoping to get the translation in English and French but the only result I got is question marks (?) in all the columns of translation can anyone explain to me why and what I should do

  15. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: How do I translate text from one language to another with VBA?

    @Pilot5000

    See #11 of protonLeah

    Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

    If you are unclear about the request or instruction then send a private message to them asking for clarification. Do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc

    Please start your question and refer to this topic.

    http://www.excelforum.com/excel-prog...html?p=3389505
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  16. #16
    Registered User
    Join Date
    11-19-2013
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    1

    Re: How do I translate text from one language to another with VBA?

    I found this really useful. I only needed to translate a German list of training course titles to English, so I was able to simplify the formula to just
    "=getGoogleTranslation(A2,"de","en")" which worked fine. Thanks very much.

  17. #17
    Registered User
    Join Date
    07-04-2014
    Location
    INDIA
    MS-Off Ver
    2010
    Posts
    1

    Re: How do I translate text from one language to another with VBA?

    hello Everyone
    This is nice article to translate text one language to another language. We have VBA code for translation using MS Access Form.
    This will definitely helpful for you.Here is link for article:http://www.accessguru.net/Articles_M...S%20Access.php


    Best Regards
    AccessGuru

  18. #18
    Registered User
    Join Date
    07-08-2014
    Location
    rouen
    MS-Off Ver
    windows 7
    Posts
    2

    Re: How do I translate text from one language to another with VBA?

    Bonjour,
    Je suis nouveau sur le forum et je tenais à t'envoyer un message juste pour te féliciter pour le fichier "google_translate.xls" que je cherchais en vain.
    Cordialement,
    Stéphane

  19. #19
    Registered User
    Join Date
    10-29-2014
    Location
    hyderabad
    MS-Off Ver
    10
    Posts
    1

    Re: How do I translate text from one language to another with VBA?

    Hi I tried to use the VBA script to convert my excel sheet to english which is in Finnish lang. But for some of the cells I am getting the below error don't know why? and also some also it is not converting the word which encounter the space or !.

    EG.Haka Hei! Haka ilmoittaa viestin lähetyksen yhteydessä
    is giving as "Haka Hello!'

    when I remove space like 'Haka Hei!Haka ilmoittaa viestin lähetyksen yhteydessä' it is working fine.


    "OCTYPE html>
    <html lang=en>
    <meta charset=utf-8>
    <meta name=viewport content=initial-scale=1"


    Please suggest what to do?

  20. #20
    Registered User
    Join Date
    02-28-2016
    Location
    Crete
    MS-Off Ver
    10
    Posts
    3

    Re: How do I translate text from one language to another with VBA?

    Hi to all. Below is a code i found on the net (if source is required please notify to include where i found this code)
    Can anyone alter the code to work for any selected area (like a column, or a cell range). I can only get it to work for the first cell of a column but i need the whole column each time.

    Sub TranslateCell()
    Dim getParam As String, trans As String, translateFrom As String, translateTo As String
    translateFrom = "fr"
    translateTo = "en"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    getParam = ConvertToGet(ActiveCell.Value)
    URL = "https://translate.google.pl/m?hl=" & translateFrom & "&sl=" & translateFrom & "&tl=" & translateTo & "&ie=UTF-8&prev=_m&q=" & getParam
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, "div dir=""ltr""") > 0 Then
    trans = RegexExecute(objHTTP.responseText, "div[^""]*?""ltr"".*?>(.+?)</div>

    ")
    ActiveCell.Value = Clean(trans)
    Else
    MsgBox ("Error")
    End If
    End Sub

    '----Used functions----
    Function ConvertToGet(val As String)
    val = Replace(val, " ", "+")
    val = Replace(val, vbNewLine, "+")
    val = Replace(val, "(", "%28")
    val = Replace(val, ")", "%29")
    ConvertToGet = val
    End Function
    Function Clean(val As String)
    val = Replace(val, "&quot;", """")
    val = Replace(val, "%2C", ",")
    val = Replace(val, "&#39;", "'")
    Clean = val
    End Function
    Public Function RegexExecute(str As String, reg As String, _
    Optional matchIndex As Long, _
    Optional subMatchIndex As Long) As String
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = reg
    regex.Global = Not (matchIndex = 0 And subMatchIndex = 0) 'For efficiency
    If regex.Test(str) Then
    Set matches = regex.Execute(str)
    RegexExecute = matches(matchIndex).SubMatches(subMatchIndex)
    Exit Function
    End If
    ErrHandl:
    RegexExecute = CVErr(xlErrValue)
    End Function

  21. #21
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,020

    Re: How do I translate text from one language to another with VBA?

    Hi, excelakos,

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.



    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  22. #22
    Registered User
    Join Date
    04-26-2017
    Location
    India
    MS-Off Ver
    2003
    Posts
    4

    Re: How do I translate text from one language to another with VBA?

    your code not work in excel 2003

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1