+ Reply to Thread
Results 1 to 20 of 20

Get google translate result from vba?

  1. #1
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,377

    Talking Get google translate result from vba?

    Hi guys,

    I'm trying to auto-translate some stuff in excel via google translate.

    Here's the code I use - in variable "title" there is some Japanese text

    Please Login or Register  to view this content.
    The responsetext I get back shows an error and doesn't give me the translated text.
    Can anyone give me a pointer on how I would be able to get what I want?

    Thanks!

    Jasper

  2. #2
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,377

    Re: Get google translate result from vba?

    OK, -as always- my approach was too simple.
    I needed to parse directly to the API instead of going through "http://translate.google.com" -

    So, in theory the following should work:

    Please Login or Register  to view this content.
    Now, this works fine if I parse something like "fiets" (Dutch for bicycle) - it will return "bicycle".
    However, my japanese text is in the variable 'title' but VBA parses it like a bunch of question marks.

    So instead of "こんなもの!!", it will parse "?????!!" - which of course will return nothing.
    Funny thing is that if I'd say "Range("A1") = title", in excel it will show the Japanese text fine, just in VBA it won't....

    I have the same with Chinese and with special characters of other languages, like "".

    Anyone got any bright ideas on how to throw the actual foreign text to google instead of a bunch of "???" .

    THANKS for helping me!!

  3. #3
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,204

    Re: Get google translate result from vba?

    You might try fiddling with the ChrW and AscW functions.
    (Not really an "idea" but a shot in the dark.)
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,377

    Re: Get google translate result from vba?

    Hi Mike,

    thanks for thinking along.
    I tried getting the ascw codes from the string, which would work.
    But if I try sending a foreign character by use of ChrW, it still returns nothing
    The interesting thing is that the responsetext DOES give me the correct language - so for example, if I use ChrW(29275) - which is "牛" (the Chinese word for cow), the responsetext shows that it recognized Chinese, but still gives me ****...

    Please Login or Register  to view this content.
    responstext is:
    [[["?","?"],[,,,"?"]],,"zh-CN",,,[["?",1,[["?",1000,true,false]],[[0,1]],"?",0,1]],0.011859209,,[["zh-CN"],,[0.011859209]]]

    Weird...

    BUT, first good suggestion - any others?

  5. #5
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,377

    Re: Get google translate result from vba?

    By the way, I tried using " Application.LanguageSettings.LanguageID(msoLanguageIDUI) " to set the language ID -

    1033 is for English, but 1041 would be for Japanese.

    If I could say
    Please Login or Register  to view this content.
    then parse the "title" to google translate, and then set it back to 1033, that would work as well.
    But alas, Application.LanguageSettings.LanguageID(msoLanguageIDUI) is read only.
    Anyone know of a workaround?

    Again - many thanks for any help!

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Get google translate result from vba?

    Jasper,
    I know this may not be relevant or would not make any sense. I was involved in a similar thread a while back. The OP wanted to get the data in Polish language. I came up with MSXML2.XMLHTTP parser. The OP was happy, but found out this method ignored the local language and some one has suggested that, he has to use I.E as it has facility for multi-characters. I did not try it my self, but have a go using an IE browse.

  7. #7
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,377

    Re: Get google translate result from vba?

    Hi AB33,

    thanks for your reply! I appreciate you thinking along!
    I sadly cannot work with IE automation - it will be seriously too slow and I don't want to work with the messiness of IE running in the background.

    I was just reading the following site : http://blog.nkadesign.com/2013/vba-u...e-windows-api/
    Which gave me the feeling the answer is hidden in there. I'll dive more into it tomorrow, but I still have my hopes up - even though having to work with strptr and longptr probably won't be a lot of fun when using MSXML2.XMLHTTP :-)

    I'd love to make it work - I'm sure there are more people who could find use in this, so it's good practice.
    And - wouldn't it be lovely to have an auto-translate function in excel?

    If anyone has any more suggestions then I'd love to hear them!

  8. #8
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Get google translate result from vba?

    Jasper,
    That looks the answer you are looking for.
    It may like the case that of images: Excel does not have its own native function on images, so we have to rely on API.
    Let us know if you get the desired solution.

  9. #9
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,377

    Re: Get google translate result from vba?

    Sadly, so far, it's not yet the answer I was looking for.

    What it does is use the "user32" lib to use the command "MessageBoxw" to display a message in a popup box.
    Sure, it displays the contents of my variable "title" perfectly (when said 'MessageboxW 0, strptr(title), strptr(""), 0') but that's all it does.
    While this might be cool for other applications, I can't do anything with it for this cause.

    I'm now trying to find out if there's a command in the library that would send the "title" to the google translate site, but I don't think that's going to work with the MSXML2.XMLHTTP site approach.
    Which means that I'll probably have to use an approach through one of the windows APIs, and honestly, I'm not good enough a coder for that....

    I will google around more, but sadly -for now- this is a dead end....

  10. #10
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Get google translate result from vba?

    Hello,

    I know you do not want to use IE object bu here is my shot at this. It got 200 words translated to Traditional Chinese in 23.89 seconds

    Main procedure Code:
    Please Login or Register  to view this content.

    Auxiliary Functions
    Please Login or Register  to view this content.
    And this is the file. Japanese.xlsm


    Hope this helps a little

  11. #11
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,377

    Re: Get google translate result from vba?

    Hi Fred,

    Thank you for trying to help! It's much appreciated!
    IE automation is really not an option - the translation has to be way quicker than that because we're talking about several hundred rows of different language text here in a sheet.

    What actually happens is that my code pulls data of a website which may or may not be in a different language.
    I can find the locale of the site and based on that decide whether I need to translate or not, but I really can't do it through IE automation.

    However - I need you guys' bright minds for a bit, cause I think I can relatively easily solve it (but I'm not smart enough )
    For the purpose of trying to get this to work, I'll be using the Chinese character for cow --> 牛 , which has a AscW / ChrW equivalent of 29275

    It will put the character 牛 into A1 if I say
    Please Login or Register  to view this content.
    If I manually paste the following into a browser, it gives me the desired result.
    https://translate.google.com/transla...864&q=牛

    Google however, translates 牛 into it's HEX equivalent of E7 89 9B

    If I sent the url through VBA as follows:
    https://translate.google.com/transla...64&q=%E7%89%9B
    it will come back with the correct result!

    Leaves the (virtual) million dollar question :
    How do I convert the AscW of 29275 to hex of E7 89 9B in VBA?

    If anyone knows how to tackle this - please help, I'll be much obliged!

  12. #12
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,377

    Re: Get google translate result from vba?

    Oh by the way - I verified that it's the correct hex code on http://www.asciitohex.com/
    If you paste 牛 in the "Text (ascii/ansi)" part, it'll come back with the desired result of e7 89 9b in the HEX window.

  13. #13
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,377

    Re: Get google translate result from vba?

    anyone?

  14. #14
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,377

    Re: Get google translate result from vba?

    OK, I solved it. By using javascript to encode the foreign language test to UTF-8 url encoding and then sending it to google translate, I've got a perfectly fine working function! (YAY!)

    Put your text you want translated to English in A1 - run the following code and translation will be in A2.
    Adjust to your personal needs :-)

    Please Login or Register  to view this content.
    Set references to Microsoft XML library (any will do) and Microsoft Script Control

    Have fun guys!
    Please click the * below if this helps

  15. #15
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Get google translate result from vba?

    Very Nice I am happy for you !

  16. #16
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,377

    Re: Get google translate result from vba?

    Thanks Fred! I'm pretty happy with it too!
    (and without IE automation)

  17. #17
    Registered User
    Join Date
    06-29-2013
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Get google translate result from vba?

    Thanks, it is very helpful

  18. #18
    Registered User
    Join Date
    10-05-2018
    Location
    India
    MS-Off Ver
    Office 2003
    Posts
    1

    Re: Get google translate result from vba?

    I need English to Telugu Translate tool via using, above said module not working, please help me and give code

  19. #19
    Registered User
    Join Date
    04-08-2016
    Location
    Wilmore, KY
    MS-Off Ver
    2010
    Posts
    3

    Re: Get google translate result from vba?

    I am stoked that you took the time to figure this out and share it with everyone. I know this post is from 2015 and it is now 5 years later and I am working in Excel Proplus (365) Desktop Version and after adding in the Reference files I am unable to get the code working. I am getting a compile error statement where , gootra As MSXML2.XMLHTTP, section in the declarations section. Is there a modification that needs to be made to make this work correctly in the new Excel Workbooks?

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,412

    Re: Get google translate result from vba?

    Quote Originally Posted by Bluetube View Post
    I am stoked that you took the time to figure this out and share it with everyone. I know this post is from 2015 and it is now 5 years later and I am working in Excel Proplus (365) Desktop Version and after adding in the Reference files I am unable to get the code working. I am getting a compile error statement where , gootra As MSXML2.XMLHTTP, section in the declarations section. Is there a modification that needs to be made to make this work correctly in the new Excel Workbooks?
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to catch errors from Google Translate
    By MrsMac in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-29-2014, 09:18 AM
  2. need Google Translate in Excel 2003
    By appu_gusai in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2013, 08:27 AM
  3. [SOLVED] Translate WEEKNUM result into a date
    By Wil in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-08-2005, 09:05 AM
  4. Replies: 5
    Last Post: 04-04-2005, 05:06 PM

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