-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Translating A Spreadsheet
- Thread starter Dazzawm
- Start date
Further update after investigating the Google Translate V2 functions.
The public facing (free) website allows you to specify a (local) document for translation (including a spreadsheet) and then gives an HTML table back (looking like a spreadsheet) of all the text translated.
Although this *may* require "manual" intervention to activate it, given the fact that it will translate a significant amount of data in one go (rather than cell by cell as was being used in the original routine) it may well be a reasonable trade-off in the short-term before a re-coded version of the routine examining the object model of a browser object is available.
I do not yet know how many entries/characters the "translate a document" is limited to, but it's certainly quite a few... (I have tested over 128 lines in a spreadsheet containing over 6K of text with no problem).
Cheers.
The public facing (free) website allows you to specify a (local) document for translation (including a spreadsheet) and then gives an HTML table back (looking like a spreadsheet) of all the text translated.
Although this *may* require "manual" intervention to activate it, given the fact that it will translate a significant amount of data in one go (rather than cell by cell as was being used in the original routine) it may well be a reasonable trade-off in the short-term before a re-coded version of the routine examining the object model of a browser object is available.
I do not yet know how many entries/characters the "translate a document" is limited to, but it's certainly quite a few... (I have tested over 128 lines in a spreadsheet containing over 6K of text with no problem).
Cheers.
Upvote
0
Excel Facts
Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi TDH,
Thank you very much for looking so much into this that's fantastic! It's good to know we have a true genius on board trying to help us solve this!
Many thanks for your response and I hope you can find a way around this!!
Many thanks
Harvey
Thank you very much for looking so much into this that's fantastic! It's good to know we have a true genius on board trying to help us solve this!
Many thanks for your response and I hope you can find a way around this!!
Many thanks
Harvey
Upvote
0
Haven't forgotten Harvey.. just busy with end of Financial year stuff here in Australia 
Will let you know as soon as I have anything. Meantime, you should consider using the Standard Google Document translator option mentioned in my last post unless you have a spreadsheet that is constantly changing. If you have relatively static data (or historical data that won't change and you only need to translate once) then pop it in a spreadsheet, let google translate it as a document and then simply paste the translation back as extra column(s) in your sheet (or perhaps an extra sheet in your workbook).
Cheers,
Warren.
Will let you know as soon as I have anything. Meantime, you should consider using the Standard Google Document translator option mentioned in my last post unless you have a spreadsheet that is constantly changing. If you have relatively static data (or historical data that won't change and you only need to translate once) then pop it in a spreadsheet, let google translate it as a document and then simply paste the translation back as extra column(s) in your sheet (or perhaps an extra sheet in your workbook).
Cheers,
Warren.
Upvote
0
Hi Warren,
Apologies for rushing you there! Didn't realise you were so busy! I do have an ever changing document being the difficulty of the task!
I am excited to see what you manage to produce soon as I feel like you have the skills and knowledge to complete the file!
Many thanks for your help
Harvey
Apologies for rushing you there! Didn't realise you were so busy! I do have an ever changing document being the difficulty of the task!
I am excited to see what you manage to produce soon as I feel like you have the skills and knowledge to complete the file!
Many thanks for your help
Harvey
Upvote
0
Great thread.
I have tried to do this, managed to get it to work the once but for some reason now it stopped working...
on translation I am getting the following in each cell of the new translaltion sheet.
"OCTYPE html PUBLIC -//W3C//DTD HTML 4.01 Transitional//EN>
<html>
<head>****** http-equiv=content-type content=text/html; charset=utf-8>****** name=viewport content=initial-scale=1><title>http://www.google.com/</title></head>
<body style=font-family: arial"
FYI - I used vba code in 5 and 7 and that's it...
I have tried to do this, managed to get it to work the once but for some reason now it stopped working...
on translation I am getting the following in each cell of the new translaltion sheet.
"OCTYPE html PUBLIC -//W3C//DTD HTML 4.01 Transitional//EN>
<html>
<head>****** http-equiv=content-type content=text/html; charset=utf-8>****** name=viewport content=initial-scale=1><title>http://www.google.com/</title></head>
<body style=font-family: arial"
FYI - I used vba code in 5 and 7 and that's it...
Upvote
0
Hi there i just found this Thread which could serve my issue. Well i need something similar to all this but i need to translate words in a specific column. I have words in english in Column A and i need to translate them in Column B. But lets say i dont want this as a fixed set up. I would like to select whichover column is needed and translate the words in the next on the right. Could you help with something like this?
Upvote
0
// QUICK 'N DIRTY OPTION BY USING MICROSOFT WORD (OFFICE 2013)
Hi I had a similar problem for a specific project. Just including a completely different tactic than the intelligent options discussed above
If you open Word, and copy/paste the Excel column in the Word doc whilst keeping the formatting intact, you can use the Word translation functions, which seems to be more clever/powerful than the ones built-in into Excel. I used the first Translate option ( not a UK version so I have to guess) of which the description reads something like : 'translating your document with an online translation service' The result opens in a browser and leaves all formatting intact. You can copy/paste ( starting at bottom - otherwise other clever systems kick in) the result in Excel, again keeping the formatting intact.
For me it worked fine on a 4 column, 650 row spreadsheet.
Hope this helps anyone, good luck
Upvote
0
Did you every find a resolution to the OCTYPE html PUBLIC -//W3C//DTD HTML 4.01 Transitional//EN> result. That is all I get.
Upvote
0
Hi, I think I found a solution. The link has changed. Here is a new one.
It works for me great!
Hopefully it would not change often.
Code:
strUrl = "https://translate.googleapis.com/translate_a/single?client=gtx&sl={F}&tl={T}&dt=t&q={S}"Hopefully it would not change often.
Upvote
0
