Microsoft Translator VBA code sample

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.

Advertisements

About Patrick O'Beirne, spreadsheet auditor

Patrick provides consultancy and training in spreadsheet development, auditing / testing and model review; and the Excel addin XLtest
This entry was posted in Excel/VBA and tagged , , , , . Bookmark the permalink.

38 Responses to Microsoft Translator VBA code sample

  1. Carl Hansen says:

    Could we get this script in Javascript, please?

  2. James Marlin says:

    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

  3. Stefan Deuling says:

    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 ?

  4. Linsum says:

    Do u have a vba sample code that work with speak method? I tried several times but failed. Thx

  5. Rob says:

    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

  6. Terry says:

    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.

  7. Terry says:

    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.

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

  9. Sheik says:

    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

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

    • Sheik says:

      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

  11. swathi says:

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

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

  13. ganesh says:

    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

      • ganesh says:

        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.

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

    • ganesh says:

      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 🙂

  15. Jean-Thomas says:

    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,

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

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

  18. Jean-Thomas says:

    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,

  19. Pingback: VBA code for Microsoft Text Translator API | Patrick O'Beirne @ sysmod

Comments are closed.