MicrosoftTranslator.com provides an API that is free up to 2MB per month. MS are moving from the Bing appID to an authorization token for their translator but as this forum post says have not brought the documentation up to date and don’t have VBA examples. So I’ve decided to post my own so people can suggest improvements. MicrosoftTranslator VBA module
Update 18-May-2015: handle international Unicode characters (eg Japanese, Arabic) as UTF-8.
Update 12-Jan-2017: added MIT license, improved debugging, 64-bit compatibility.
Update 20-Jan-17: VBA code posted for MS Cognitive services on Azure.
https://sysmod.wordpress.com/2017/01/20/vba-code-for-microsoft-text-translator-api/
This post is now closed.
Patrick O'Beirne @ sysmod 
Could we get this script in Javascript, please?
Not my day job, but Google for Microsoft Translator javascript code sample gives me
http://msdn.microsoft.com/en-us/library/ff512385.aspx
http://social.microsoft.com/Forums/en-US/translator/thread/a0452462-c713-4a50-a1c5-498e5a47e8f9
Thanks for the example! However the call to GetAccessToken() now returns access_token and Token_type in reverse (Token type is first), using my account – no error conditon. Also, when calling the translate function MicrosoftTranslate(), an error message from the API “”ArgumentException: Signature verification of the incoming token failed.” .
Any Ideas on what is happening? Thanks
Thanks, James.
Yes, they’ve changed the order. So I changed the code to be not dependent on the order. Please download the revised MSTranslator.bas file.
As for the Signature verification error, sorry, I don’t know.
http://msdn.microsoft.com/en-us/library/aa529292.aspx
The code works with my Client_ID and Secret, maybe try to get an updated key from:
https://datamarket.azure.com/dataset/1899a118-d202-492c-aa16-ba21c33c06cb
or post your question on Microsoft Answers
http://social.msdn.microsoft.com/Forums/nl-NL/microsofttranslator/threads
Good luck
Patrick
Thanks Patrick!, it works like a charm now.. Not sure what I was doing wrong yesterday but I am getting the translation back from the API now. Thanks for the quick followup! Kudos!
Hello Patrick, I get an “unknown error” in VBA. Trying the same in VB .net the error reads “wrong parameter”. Error occures at
webRequest.send sRequest
Content of sRequest :
grant_type=client_credentials&client_id=pz8zq1&client_secret=4YqIiAtXBDiu4ptdDwvh9in5Ra1T7ZO8OQ1Q5LsgADE%3D&scope=http://api.microsofttranslator.com
Any idea ?
found the issue, in vb.net you need to create a reference to msxml2.dll to expose the object model, if not you can´t create an object like Dim webRequest As MSXML2.XMLHTTP. Works now for me, thanks for the post.
Apologize for bringing up such an old post but funnily enough, I faced this exact error “wrong parameter” in Excel VBA today. Will really appreciate if you could share your solution for VBA. Thanks!
I don’t know. Is this any help?
https://social.microsoft.com/Forums/en-US/3aaaab72-5b00-4358-b371-99e827ff3a0e/msxml2xmlhttp-error-in-excel-vba-getazuretoken-code-on-w8-64bit-asus-n56vz-with-office-2013-32bit?forum=microsofttranslator
If you get this Wrong Parameter Or UnKnow Parameter Error on the Line webRequest.send sRequest,
Replace “Dim webRequest As Object” with “Dim webRequest As MSXML2.XMLHTTP”
According to
http://stackoverflow.com/questions/308826/msxml2-xmlhttp-send-method-works-with-early-binding-fails-with-late-binding
A solution is to put brackets around the sRequest, or use CVar(sRequest)
Do u have a vba sample code that work with speak method? I tried several times but failed. Thx
No, I have not tried the speak method. I just work with text 🙂
Patrick…..Many thanks for your VBA example. I’ve been using Google Translate code in my VBA macro until this AM when it started throwing a error. Research indicated that they had started requiring some kind of authentication token and wanted REAL money for using it. My application is for me only and very light usage.
I found put about MS Translate, Marketplace Azure, and your code. Now all is running again. Thanks again…..RDK
Is there a reason why when Russian is detected language that the result in Excel function (MicrosoftTranslate) always brings back ????? as the value? I’m currently operating under Excel 2013 64 bit.
I tried manually copy/pasting and it seems to work fine translated when I go into the front end API. I think Russian is language short code is ‘ru’ which should be supported by Microsoft.
See
https://social.msdn.microsoft.com/Forums/en-US/e4c149c4-fefb-48fd-8990-db6a8f0f9045/bingms-translator-documentation-is-a-total-mess?forum=microsofttranslator
If you are looking at the text using debug.print in VBA or using MsgBox it will not convert correctly.
Store it in a cell and it will look OK.
Or display it using the messagebox API and that will display Unicode correctly.
Public Declare Function APIMsgBox Lib “USER32” Alias “MessageBoxW” (Optional ByVal hWnd As Long, Optional ByVal Prompt As Long, Optional ByVal Caption As Long, Optional ByVal BUTTONS As Long) As Long
APIMsgBox Prompt:=StrPtr(sPrompt), Caption:=StrPtr(sTitle), BUTTONS:=lButtons
Is it some other Excel setting that is not allowing me to display the values converted with the function? I wasn’t trying to display the text using VBA but rather just in the cell alone.
For example cell A1 has 東京 (Tokyo)
and the formula in cell B1 has = MicrosoftTranslate(A1)
I tried it with Russian and Korean and it’s the same result.
I see that. I don’t have time to investigate it now. There must be a failed conversion somewhere in the process but it’ll take time to track it down.
Thanks to this post at Stackoverflow:
http://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba
I now have updated the module to incorporate UTF-8 encoding.
Hello
i am using Office 2010 64bit on Win10 64bit
After copying the code into module i get error message and changed
Declare Function to Declare PtrSafe Function
and
hWnd As Long to hWnd As LongPtr
But still get type mismatch error at
StrPtr(UTF16)
in
Public Function UTF16To8(ByVal UTF16 As String) As String
Can you help?
Thanks
Sheik
Sorry, Sheik, I’m not using 64 bit Office.
Is this any help? :
https: // gist.github.com / brucemcpherson / 3414346
Public Function UTF16To8(ByVal UTF16 As String) As String
Dim sBuffer As String
#If VBA7 And Win64 Then
Dim lLength As Longlong
#Else
Dim lLength As Long
#End If
If UTF16 “” Then
lLength = WideCharToMultiByte(CP_UTF8, 0, StrPtr(UTF16), -1, 0, 0, 0, 0)
sBuffer = Space$(CLng(lLength))
lLength = WideCharToMultiByte( _
CP_UTF8, 0, StrPtr(UTF16), -1, StrPtr(sBuffer), Len(sBuffer), 0, 0)
sBuffer = StrConv(sBuffer, vbUnicode)
UTF16To8 = left$(sBuffer, CLng(lLength – 1))
Else
UTF16To8 = “”
End If
End Function
No, the error occurs at StrPtr(..) always, not before..
So i checked again and found to change the declarations additional necessary.
Inserting PtrSafe and changing Long into LongPtr, where StrPtr is used, solved the error problem:
These declarations seem to work:
:
Public Declare PtrSafe Function APIMsgBox Lib “USER32” Alias “MessageBoxW” (Optional ByVal hWnd As LongPtr, Optional ByVal Prompt As LongPtr, Optional ByVal Caption As LongPtr, Optional ByVal BUTTONS As Long) As Long
Private Declare PtrSafe Function WideCharToMultiByte Lib “Kernel32” (ByVal CodePage As Long, ByVal dwflags As Long, ByVal lpWideCharStr As LongPtr, ByVal cchWideChar As Long, ByVal lpMultiByteStr As LongPtr, ByVal cchMultiByte As Long, ByVal lpDefaultChar As Long, ByVal lpUsedDefaultChar As Long) As Long
Thank you very much.
Regards
Sheik
Can you please help why code working for some cells and returning blank for some other cells. If i give 100 cells of data 6 to 7 will give result remaining blank. 😦
I can’t answer that without knowing your data. Please supply an example where blanks are returned.
its not related to the data. when you try to translate a huge number of cells on excel. It simply translates first few, and then gives blanks for Rest… but if you put all contents in one cell. its translating all.
Update Dec 15, 2016:
https://translatorbusiness.uservoice.com/knowledgebase/articles/1078534-microsoft-translator-on-azure
Microsoft Translator has moved to Azure
Microsoft Translator is now available for purchase through Azure.
What you need to know about your subscription
You may subscribe to the Microsoft Translator API in the Azure Portal anytime.
Your current Microsoft Translator API subscription on the Microsoft DataMarket will continue through December 31, 2016, with full subscription functionality.
Service access on Microsoft DataMarket will be limited to the monthly volume plan subscribed, beginning January 1, 2017. As of the new year, you cannot upgrade or downgrade your subscription on the Azure Marketplace, you can only cancel.
Remaining Microsoft Translator API subscriptions will be automatically canceled on April 30, 2017.
yes .. its returning blanks for some cells in excel…
Hi Ganesh
I don’t know what you mean by “huge”
Please supply an example where blanks are returned.
You can email me the file if you prefer, see sysmod.com/feedback.htm
Hi Patrick,
Thanks for asking.
I mailed my excel macro to you.
Say if you have more than 15 cells in an excel macro. I get the translation for only first few (4-5 cells). I get blanks for the rest.
I am making a new MSHttpRequest for every Cell, though my access token remains the same.
It will be great , if you can help on this.
Thanks and Regards,
Ganesh.
Ganesh, please edit your post to remove my email address. I gave a link so that spammers would not be able to scrape it from this blog.
Hi Patrick, I am not getting an option to Edit the comment. am I missing something. Can you please help me. Thanks.
Sorry, I expected there would be an edit link for people’s own posts. It says on my screen in the header:
ganesh says:
January 10, 2017 at 7:44 am (Edit)
I’ve clicked that (Edit) link and made that change. Now I know I can do that I might edit others 🙂
Hello Patrick,
I have a question regarding this amazing macro. Is it possible to use it with the new microsoft azure service ? If yes, where to find the credentials ( client_secret and client_id) and do we need to adapt the code ?
Thanks for your help,
Thanks Jean-Thomas.
[Edit 20-Jan]
I now think that update from Microsoft is simply to say that the free volume plan cannot be changed.I’ve just tried the code from May 2015 which uses Azure and it still works OK.However, the MS Datamarket IS being retired and you need to move to Cognitive Services before April 30.
You should only need to call the functions in the code after defining the credentials in the placeholders. You are free to use and adapt the code, but please retain the comment giving me as the author, eg by a link to this blog. I saw one person claim that they “developed” a VBA macro to translate … the only “development” they did was pasting in this code and removing the two lines with my contact details.
Start here:
https://msdn.microsoft.com/en-us/library/hh454949.aspx
Basic subscriptions, up to 2 million characters a month, are free.
There are some issues reported currently on the datamarket. The API returns error messages with a high rate of translation, although MS specify no rate limit.
Some languages like Irish (ga-IE) and I think Slovenian can only be translated if you provide a blank “from” language code.Although the Detect method can recognise “ga-IE”, the Translate method does not, or list it in the GetLanguagesForTranslate() array. Follow the forum for more.Forum & Documentation:
https://translatorbusiness.uservoice.com/
I’ve updated the module to add an open source license and improve the debugging of error returns.
API calls modified for compatibility with 64-bit Excel. And a tweak in the webRequest.send for early binding.
Thanks for your answer Patrick,
Yes sure, I actually use your code in an excel template to translate product description for an e-commerce website from multiple language to french. It is realy useful for us and be sure that I will never delete the comment giving you as the author.
Yes, we actually use the free plan from datamarket, it is working great now, but I’m not sure it will after the 31th of march 2017 according to Microsoft.
best,
Pingback: VBA code for Microsoft Text Translator API | Patrick O'Beirne @ sysmod
My update for Azure Cognitive Services is at
This thread is now closed.
Pingback: excel - Con el traductor de Google en una macro VBA de Excel
Pingback: excel - Utilizzando Google Translate in una macro VBA di Excel