This project is read-only.

How do you use the Translate macro in a spreadsheet?

Feb 4, 2011 at 3:18 AM

I don't normally use MS Office, but I need to for a project and I need access to Google's Translate function in this project.

I exported the module1.bas file from Google Translate and Web Service Caller.xls and imported it into my spreadsheet but when I call the TRANSLATE function I get the error:

 

Compile Error: User defined type not defined

The problem type is MSXML2.XMLHTTP.

 

How are we supposed to use this in our worksheets?  Can you give instructions?

Mar 15, 2011 at 1:31 AM

Hey Mfriedma,

apologies for the slow response; I don't check this site as regularly as I should.  The project references an external library, so when copying the code to a new project / workbook, you need to add the reference.  To do this, follow the instructions below.

  • Open the workbook you copied the code to
  • Bring up the VBA editor (alt + f11)
  • Click Tools, References
  • Find and tick Microsoft XML, v6.0 (or whatever the latest version available to your machine is)
  • Click OK

You should now be able to use the MSXML2.XMLHTTP object :).

From what I recall, this project also used a couple of other libraries; Microsoft Scripting Runtime and Microsoft VBScript Regular Expressions 5.5.  You can add those in the same way you did the MS XML one above.  If anything else is missing after that, compare the references of your project with those in the original and add additional references to your project as needed.  The references used by a project are helpfully bubbled to the top of the list, so it's easy to see without scrolling through the entire list of libraries.

Hope that's still of use & all works out OK.

Good luck,

JB

Nov 25, 2011 at 12:04 AM

NB: a bug has been reported to me regarding the Google Translate macro - this seems to have arisen within the past month despite no changes having been made to the macro's code.  

The issue is the macro brings back a TOS abuse error if used for more than one call within a time frame of a few minutes.  When I access Google's service via the browser I see the same issue, so I think there's now a limit on the Google side.  I've reported this to the Google Support Forum in the hope that this is a bug which they can resolve, rather than a change to the usage policies.

Google Support Forum Post: http://www.google.com/support/forum/p/translator-toolkit-api/thread?tid=3977cdac974fcf41&hl=en

The error can be reproduced outside of the macro by going to the address below, then refreshing the page (optionally changing the querystring parameters): http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&langpair=en|fr&q=hello