Spreadsheet translation
 

I have a spreadsheet of a foreign company's financial statements. I want to
translate all text into English and keep numbers as is. I know Google has
an AJAX/javascript API that can do the translation... Can anyone think of a
way to automate the task with VBA... Is it possible to write a function that
would instantiate a WebBrowser control within VBA and use it to invoke the
Google API to translate a single cell. Then I could write a procedure to
loop through all cell in the spreadsheet, check if they are text and if so
call my function to translate the cell.

Thanks in advance for any advice.

Joe

Spreadsheet translation
 

I guess the API is a win32 dll. Here is some interesting code. I don't know
how to get to the google API. Do you have a URL?


Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long


Sub GetID()
Set modObjIE = CreateObject("InternetExplorer.Application")
modObjIE.Visible = True
Handle = modObjIE.hwnd


WindowName = "Windows Internet Explorer"
lngProcessID = FindWindow(vbNullString, WindowName)

MsgBox "Handle = " & Handle & ", ID = " & lngProcessID
End Sub

"Joe Nastasi" wrote:

I have a spreadsheet of a foreign company's financial statements. I want to
translate all text into English and keep numbers as is. I know Google has
an AJAX/javascript API that can do the translation... Can anyone think of a
way to automate the task with VBA... Is it possible to write a function that
would instantiate a WebBrowser control within VBA and use it to invoke the
Google API to translate a single cell. Then I could write a procedure to
loop through all cell in the spreadsheet, check if they are text and if so
call my function to translate the cell.

Thanks in advance for any advice.

Joe

Spreadsheet translation
 

No, the API is not a Win32 dll. It is a web service hosted by google. You
send an XML message containing your API request in javascript to the google
server, and it sends back a response also in XML.

Thanks for the code example... I think it could work, but I would have to
read up on the Internet Explorer DOM to figure out how insert the API
request as <script type="text/javascript" into the html of the web page and
how to read the response.

Information of the google translate API can be found he
http://code.google.com/apis/ajaxlanguage/

"joel" wrote in message
...
I guess the API is a win32 dll. Here is some interesting code. I don't
know
how to get to the google API. Do you have a URL?


Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long


Sub GetID()
Set modObjIE = CreateObject("InternetExplorer.Application")
modObjIE.Visible = True
Handle = modObjIE.hwnd


WindowName = "Windows Internet Explorer"
lngProcessID = FindWindow(vbNullString, WindowName)

MsgBox "Handle = " & Handle & ", ID = " & lngProcessID
End Sub

"Joe Nastasi" wrote:

I have a spreadsheet of a foreign company's financial statements. I want
to
translate all text into English and keep numbers as is. I know Google
has
an AJAX/javascript API that can do the translation... Can anyone think of
a
way to automate the task with VBA... Is it possible to write a function
that
would instantiate a WebBrowser control within VBA and use it to invoke
the
Google API to translate a single cell. Then I could write a procedure to
loop through all cell in the spreadsheet, check if they are text and if
so
call my function to translate the cell.

Thanks in advance for any advice.

Joe

Spreadsheet translation
 

If you can setup a webpage with two tex boxes. One box for input and one box
for output and a submit button to perform translation. give each box and
button a unique ID. I can write the code to move the data from the
spreadsheet to the inbox and take the translation from the translation box to
the spreadsheet. this is real simple.

"Joe Nastasi" wrote:

No, the API is not a Win32 dll. It is a web service hosted by google. You
send an XML message containing your API request in javascript to the google
server, and it sends back a response also in XML.

Thanks for the code example... I think it could work, but I would have to
read up on the Internet Explorer DOM to figure out how insert the API
request as <script type="text/javascript" into the html of the web page and
how to read the response.

Information of the google translate API can be found he
http://code.google.com/apis/ajaxlanguage/

"joel" wrote in message
...
I guess the API is a win32 dll. Here is some interesting code. I don't
know
how to get to the google API. Do you have a URL?


Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long


Sub GetID()
Set modObjIE = CreateObject("InternetExplorer.Application")
modObjIE.Visible = True
Handle = modObjIE.hwnd


WindowName = "Windows Internet Explorer"
lngProcessID = FindWindow(vbNullString, WindowName)

MsgBox "Handle = " & Handle & ", ID = " & lngProcessID
End Sub

"Joe Nastasi" wrote:

I have a spreadsheet of a foreign company's financial statements. I want
to
translate all text into English and keep numbers as is. I know Google
has
an AJAX/javascript API that can do the translation... Can anyone think of
a
way to automate the task with VBA... Is it possible to write a function
that
would instantiate a WebBrowser control within VBA and use it to invoke
the
Google API to translate a single cell. Then I could write a procedure to
loop through all cell in the spreadsheet, check if they are text and if
so
call my function to translate the cell.

Thanks in advance for any advice.

Joe

Spreadsheet translation
 

Joel,

That would be GREAT and a huge help!! Assume foreign text is in Sheet1!A1
and I want to put translated result in Sheet2!A1. Here is the html for a
page that does what you request. Thanks in advance for your help.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"

<head
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /
<titleGoogle Translate API</title
</head

<body


<form name="f" id="f" action="#" onsubmit="translate(); return false;"

<textarea name="foreign_text" id="foreign_text" rows="4"
cols="60"</textarea
<br /
<br /

<input type="submit" id="submit_button" value="Translate into English"
onfocus="this.blur();" /
<br /
<br /

<textarea name="translation" id="translation" rows="4" cols="60"
onfocus="this.select();" readonly="true"</textarea</form
<br /

<script type="text/javascript" src="http://www.google.com/jsapi"</script
<script type="text/javascript"
google.load("language", "1");
function translate() {var
originaltext=document.forms["f"].foreign_text.value;
google.language.translate(originaltext, "", "en", function(result) {
document.forms["f"].translation.value = (result.error)?("Error:
"+result.error.message):result.translation; }); }
</script

</body
</html




"joel" wrote in message
...
If you can setup a webpage with two tex boxes. One box for input and one
box
for output and a submit button to perform translation. give each box and
button a unique ID. I can write the code to move the data from the
spreadsheet to the inbox and take the translation from the translation box
to
the spreadsheet. this is real simple.

"Joe Nastasi" wrote:

No, the API is not a Win32 dll. It is a web service hosted by google.
You
send an XML message containing your API request in javascript to the
google
server, and it sends back a response also in XML.

Thanks for the code example... I think it could work, but I would have to
read up on the Internet Explorer DOM to figure out how insert the API
request as <script type="text/javascript" into the html of the web page
and
how to read the response.

Information of the google translate API can be found he
http://code.google.com/apis/ajaxlanguage/

"joel" wrote in message
...
I guess the API is a win32 dll. Here is some interesting code. I don't
know
how to get to the google API. Do you have a URL?


Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long


Sub GetID()
Set modObjIE = CreateObject("InternetExplorer.Application")
modObjIE.Visible = True
Handle = modObjIE.hwnd


WindowName = "Windows Internet Explorer"
lngProcessID = FindWindow(vbNullString, WindowName)

MsgBox "Handle = " & Handle & ", ID = " & lngProcessID
End Sub

"Joe Nastasi" wrote:

I have a spreadsheet of a foreign company's financial statements. I
want
to
translate all text into English and keep numbers as is. I know Google
has
an AJAX/javascript API that can do the translation... Can anyone think
of
a
way to automate the task with VBA... Is it possible to write a
function
that
would instantiate a WebBrowser control within VBA and use it to invoke
the
Google API to translate a single cell. Then I could write a procedure
to
loop through all cell in the spreadsheet, check if they are text and
if
so
call my function to translate the cell.

Thanks in advance for any advice.

Joe

Spreadsheet translation
 

See if this works.. I'm not getting any translations. Not sure why. Yo are
better at the java scripts. I put the code into a html file using a text
editor. I put the results in the row below where the text was found in the
spreadsheet. the text data may be html. If so put the data into innerhtml
instead of innertext.

Change the URL and see if it works

Sub translate()

Dim objIE As Object
Dim strServAcct As String

URL = "c:\temp\working\translation.html"
'Open Internet Explorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate URL

Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop

Set ForeignCells = Range("A1:B1")
For Each cell In ForeignCells
Set ForeignText = IE.document.getElementById("foreign_text")
Set submit = IE.document.getElementById("submit_button")

ForeignText.innertext = cell.Value
submit.Select
submit.Click
Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop
Set Translation = IE.document.getElementById("Translation")
Translation.innertext = cell.Offset(1, 0).Value
Next cell
End Sub



"Joe Nastasi" wrote:

Joel,

That would be GREAT and a huge help!! Assume foreign text is in Sheet1!A1
and I want to put translated result in Sheet2!A1. Here is the html for a
page that does what you request. Thanks in advance for your help.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"

<head
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /
<titleGoogle Translate API</title
</head

<body


<form name="f" id="f" action="#" onsubmit="translate(); return false;"

<textarea name="foreign_text" id="foreign_text" rows="4"
cols="60"</textarea
<br /
<br /

<input type="submit" id="submit_button" value="Translate into English"
onfocus="this.blur();" /
<br /
<br /

<textarea name="translation" id="translation" rows="4" cols="60"
onfocus="this.select();" readonly="true"</textarea</form
<br /

<script type="text/javascript" src="http://www.google.com/jsapi"</script
<script type="text/javascript"
google.load("language", "1");
function translate() {var
originaltext=document.forms["f"].foreign_text.value;
google.language.translate(originaltext, "", "en", function(result) {
document.forms["f"].translation.value = (result.error)?("Error:
"+result.error.message):result.translation; }); }
</script

</body
</html




"joel" wrote in message
...
If you can setup a webpage with two tex boxes. One box for input and one
box
for output and a submit button to perform translation. give each box and
button a unique ID. I can write the code to move the data from the
spreadsheet to the inbox and take the translation from the translation box
to
the spreadsheet. this is real simple.

"Joe Nastasi" wrote:

No, the API is not a Win32 dll. It is a web service hosted by google.
You
send an XML message containing your API request in javascript to the
google
server, and it sends back a response also in XML.

Thanks for the code example... I think it could work, but I would have to
read up on the Internet Explorer DOM to figure out how insert the API
request as <script type="text/javascript" into the html of the web page
and
how to read the response.

Information of the google translate API can be found he
http://code.google.com/apis/ajaxlanguage/

"joel" wrote in message
...
I guess the API is a win32 dll. Here is some interesting code. I don't
know
how to get to the google API. Do you have a URL?


Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long


Sub GetID()
Set modObjIE = CreateObject("InternetExplorer.Application")
modObjIE.Visible = True
Handle = modObjIE.hwnd


WindowName = "Windows Internet Explorer"
lngProcessID = FindWindow(vbNullString, WindowName)

MsgBox "Handle = " & Handle & ", ID = " & lngProcessID
End Sub

"Joe Nastasi" wrote:

I have a spreadsheet of a foreign company's financial statements. I
want
to
translate all text into English and keep numbers as is. I know Google
has
an AJAX/javascript API that can do the translation... Can anyone think
of
a
way to automate the task with VBA... Is it possible to write a
function
that
would instantiate a WebBrowser control within VBA and use it to invoke
the
Google API to translate a single cell. Then I could write a procedure
to
loop through all cell in the spreadsheet, check if they are text and
if
so
call my function to translate the cell.

Thanks in advance for any advice.

Joe

Spreadsheet translation
 

JoeL,

I am getting the same problem, and I am not sure why... The translation
shows up in IE, but when the code tries to access it through the "innerText"
element it does not show up... I tried stepping through the code with the
debugger and viewed all local objects and there is an object there called
Translation.... and the innerText element has the correct translation... but
when I do MsgBox(Translation.innerText) the message box is blank. Very
wierd.

Anyway, thanks for your help... I will give this a rest for a day or two and
maybe some ideas will come to me later.

Thanks!

"joel" wrote in message
...
See if this works.. I'm not getting any translations. Not sure why. Yo
are
better at the java scripts. I put the code into a html file using a text
editor. I put the results in the row below where the text was found in
the
spreadsheet. the text data may be html. If so put the data into
innerhtml
instead of innertext.

Change the URL and see if it works

Sub translate()

Dim objIE As Object
Dim strServAcct As String

URL = "c:\temp\working\translation.html"
'Open Internet Explorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate URL

Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop

Set ForeignCells = Range("A1:B1")
For Each cell In ForeignCells
Set ForeignText = IE.document.getElementById("foreign_text")
Set submit = IE.document.getElementById("submit_button")

ForeignText.innertext = cell.Value
submit.Select
submit.Click
Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop
Set Translation = IE.document.getElementById("Translation")
Translation.innertext = cell.Offset(1, 0).Value
Next cell
End Sub



"Joe Nastasi" wrote:

Joel,

That would be GREAT and a huge help!! Assume foreign text is in
Sheet1!A1
and I want to put translated result in Sheet2!A1. Here is the html for a
page that does what you request. Thanks in advance for your help.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"

<head
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /
<titleGoogle Translate API</title
</head

<body


<form name="f" id="f" action="#" onsubmit="translate(); return false;"

<textarea name="foreign_text" id="foreign_text" rows="4"
cols="60"</textarea
<br /
<br /

<input type="submit" id="submit_button" value="Translate into English"
onfocus="this.blur();" /
<br /
<br /

<textarea name="translation" id="translation" rows="4" cols="60"
onfocus="this.select();" readonly="true"</textarea</form
<br /

<script type="text/javascript"
src="http://www.google.com/jsapi"</script
<script type="text/javascript"
google.load("language", "1");
function translate() {var
originaltext=document.forms["f"].foreign_text.value;
google.language.translate(originaltext, "", "en", function(result) {
document.forms["f"].translation.value = (result.error)?("Error:
"+result.error.message):result.translation; }); }
</script

</body
</html




"joel" wrote in message
...
If you can setup a webpage with two tex boxes. One box for input and
one
box
for output and a submit button to perform translation. give each box
and
button a unique ID. I can write the code to move the data from the
spreadsheet to the inbox and take the translation from the translation
box
to
the spreadsheet. this is real simple.

"Joe Nastasi" wrote:

No, the API is not a Win32 dll. It is a web service hosted by google.
You
send an XML message containing your API request in javascript to the
google
server, and it sends back a response also in XML.

Thanks for the code example... I think it could work, but I would have
to
read up on the Internet Explorer DOM to figure out how insert the API
request as <script type="text/javascript" into the html of the web
page
and
how to read the response.

Information of the google translate API can be found he
http://code.google.com/apis/ajaxlanguage/

"joel" wrote in message
...
I guess the API is a win32 dll. Here is some interesting code. I
don't
know
how to get to the google API. Do you have a URL?


Public Declare Function FindWindow Lib "user32" Alias "FindWindowA"
_
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long


Sub GetID()
Set modObjIE = CreateObject("InternetExplorer.Application")
modObjIE.Visible = True
Handle = modObjIE.hwnd


WindowName = "Windows Internet Explorer"
lngProcessID = FindWindow(vbNullString, WindowName)

MsgBox "Handle = " & Handle & ", ID = " & lngProcessID
End Sub

"Joe Nastasi" wrote:

I have a spreadsheet of a foreign company's financial statements.
I
want
to
translate all text into English and keep numbers as is. I know
Google
has
an AJAX/javascript API that can do the translation... Can anyone
think
of
a
way to automate the task with VBA... Is it possible to write a
function
that
would instantiate a WebBrowser control within VBA and use it to
invoke
the
Google API to translate a single cell. Then I could write a
procedure
to
loop through all cell in the spreadsheet, check if they are text
and
if
so
call my function to translate the cell.

Thanks in advance for any advice.

Joe

Spreadsheet translation
 

I copied the foreign text code to the translation when I wrote the code. Oops!

from
Translation.innertext = cell.Offset(1, 0).Value
to
cell.Offset(1, 0).Value = Translation.innertext


"Joe Nastasi" wrote:

JoeL,

I am getting the same problem, and I am not sure why... The translation
shows up in IE, but when the code tries to access it through the "innerText"
element it does not show up... I tried stepping through the code with the
debugger and viewed all local objects and there is an object there called
Translation.... and the innerText element has the correct translation... but
when I do MsgBox(Translation.innerText) the message box is blank. Very
wierd.

Anyway, thanks for your help... I will give this a rest for a day or two and
maybe some ideas will come to me later.

Thanks!

"joel" wrote in message
...
See if this works.. I'm not getting any translations. Not sure why. Yo
are
better at the java scripts. I put the code into a html file using a text
editor. I put the results in the row below where the text was found in
the
spreadsheet. the text data may be html. If so put the data into
innerhtml
instead of innertext.

Change the URL and see if it works

Sub translate()

Dim objIE As Object
Dim strServAcct As String

URL = "c:\temp\working\translation.html"
'Open Internet Explorer
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate URL

Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop

Set ForeignCells = Range("A1:B1")
For Each cell In ForeignCells
Set ForeignText = IE.document.getElementById("foreign_text")
Set submit = IE.document.getElementById("submit_button")

ForeignText.innertext = cell.Value
submit.Select
submit.Click
Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop
Set Translation = IE.document.getElementById("Translation")
Translation.innertext = cell.Offset(1, 0).Value
Next cell
End Sub



"Joe Nastasi" wrote:

Joel,

That would be GREAT and a huge help!! Assume foreign text is in
Sheet1!A1
and I want to put translated result in Sheet2!A1. Here is the html for a
page that does what you request. Thanks in advance for your help.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"

<head
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /
<titleGoogle Translate API</title
</head

<body


<form name="f" id="f" action="#" onsubmit="translate(); return false;"

<textarea name="foreign_text" id="foreign_text" rows="4"
cols="60"</textarea
<br /
<br /

<input type="submit" id="submit_button" value="Translate into English"
onfocus="this.blur();" /
<br /
<br /

<textarea name="translation" id="translation" rows="4" cols="60"
onfocus="this.select();" readonly="true"</textarea</form
<br /

<script type="text/javascript"
src="http://www.google.com/jsapi"</script
<script type="text/javascript"
google.load("language", "1");
function translate() {var
originaltext=document.forms["f"].foreign_text.value;
google.language.translate(originaltext, "", "en", function(result) {
document.forms["f"].translation.value = (result.error)?("Error:
"+result.error.message):result.translation; }); }
</script

</body
</html




"joel" wrote in message
...
If you can setup a webpage with two tex boxes. One box for input and
one
box
for output and a submit button to perform translation. give each box
and
button a unique ID. I can write the code to move the data from the
spreadsheet to the inbox and take the translation from the translation
box
to
the spreadsheet. this is real simple.

"Joe Nastasi" wrote:

No, the API is not a Win32 dll. It is a web service hosted by google.
You
send an XML message containing your API request in javascript to the
google
server, and it sends back a response also in XML.

Thanks for the code example... I think it could work, but I would have
to
read up on the Internet Explorer DOM to figure out how insert the API
request as <script type="text/javascript" into the html of the web
page
and
how to read the response.

Information of the google translate API can be found he
http://code.google.com/apis/ajaxlanguage/

"joel" wrote in message
...
I guess the API is a win32 dll. Here is some interesting code. I
don't
know
how to get to the google API. Do you have a URL?


Public Declare Function FindWindow Lib "user32" Alias "FindWindowA"
_
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long


Sub GetID()
Set modObjIE = CreateObject("InternetExplorer.Application")
modObjIE.Visible = True
Handle = modObjIE.hwnd


WindowName = "Windows Internet Explorer"
lngProcessID = FindWindow(vbNullString, WindowName)

MsgBox "Handle = " & Handle & ", ID = " & lngProcessID
End Sub

"Joe Nastasi" wrote:

I have a spreadsheet of a foreign company's financial statements.
I
want
to
translate all text into English and keep numbers as is. I know
Google
has
an AJAX/javascript API that can do the translation... Can anyone
think
of
a
way to automate the task with VBA... Is it possible to write a
function
that
would instantiate a WebBrowser control within VBA and use it to
invoke
the
Google API to translate a single cell. Then I could write a
procedure
to
loop through all cell in the spreadsheet, check if they are text
and
if
so
call my function to translate the cell.

Thanks in advance for any advice.

Joe