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

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!!

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?

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!

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.

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!

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.

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....

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

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!

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.

anyone?

Very Nice I am happy for you !

Thanks, it is very helpful

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

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?

+1 for Bluetube