I'm a great believer in making code as compact as possible, whether or not it meets best practice standards as long as it works. This code will cycle through the cells in a range and go to the google translate page for the value in the currently selected cell. All I need to solve my problem is the simplest possible code that will then - as commented out in line 10 below - copy the English translation on the newly generated web page and append it to the currently selected cell. How would I do that?
Text - The text to translate. The value for text must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text. Sourcelanguage - OPTIONAL - 'auto' by default - The two-letter language code of the source language, e.g. 'en' for English or 'ko' for Korean, or 'auto' to auto-detect the language.
Thanks! John
Active2 years, 7 months ago
I am hoping someone here can help me with the google translator API. I have an excel file with about 80k rows, and I am trying to build a macro where it translates everything in a column except for the first row. As an example I have six columns in Microsoft Excel such as the below
Excel Column Headers
Column A = Name
Column B = Address
Column C = Phone Number
Column D = Request
Column E = Google Translated Request
Column F = Notes
and I want to use the google translator API to take what is in column D and translate it to column E without translating what is in the first row as those are my column titles. Is this possible to do within an Excel Macro using the google translate API, and if so how would one do this?
NoobDeveloperNoobDeveloper
2 Answers
In order to translate content from your Excel spreadsheets, your options would be one of the following:
Use built-in Microsoft text translation
Write a VBA script that communicates with the Microsoft Text Translator API
Write a VBA script that communicates with the Google Translation API
Depending on the use case and how confident you are with Microsoft's translation feature, I'd probably use the built in solution as it would likely be the simplest route. Depending on the version of Excel you have, it could even be built into the menus.
If not, you could use VBA script to use the Microsoft Text Translator REST API as suggested by @sysmod in a previous answer. As I'm not entirely familiar with that API, I'd strongly recommend reading the linked documentation.
If you prefer to use the Google Translation API, there is a REST API that you can use as linked above. You'd essentially need to use VBA script to issue a GET or POST request to https://translation.googleapis.com/language/translate/v2.
Keep in mind that in the case of both REST APIs (Microsoft and Google alike), you would need very specific query parameters as shown in their respective linked documentation and you'd also need to authentication your project/application using a subscription key or API key. This is the only way for Microsoft of Google to keep track of how much free quota your application has used.
NicholasNicholas
Built into Excel 2016:https://www.microsoft.com/en-us/translator/excel.aspx
Not Google, but using the free Microsoft Azure API:
sysmodsysmod
Not the answer you're looking for? Browse other questions tagged excelvbaexcel-vbagoogle-apigoogle-translate or ask your own question.