PDA

View Full Version : Google Translation Issue



stanl
06-25-2007, 08:21 AM
This is more a vba than an excel question, but I am running the sub below from Excel:


Sub googletrans()
Dim oHTTP As Object
f = ActiveWorkbook.Path & "\gtran.htm"
Open f For Output As #1
cURL = "http://www.translate.google.com/translate_t?"
cPost = "langpair=en|zh-CN&ie=UTF8&text=apple"
Set oHTTP = CreateObject("Msxml2.XMLHTTP")
oHTTP.Open "POST", cURL & cPost, False
oHTTP.SetRequestHeader "UserAgent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows 98"
oHTTP.Send ""
Print #1, oHTTP.ResponseText
Close #1
End Sub


If you manually go to the google site and ask to translate 'apple' to traditional Chinese you will see 2 Chinese chars. If you open the gtans.htm created by the sub you will see ??

I tried a similar sun using xStandard's freeware xHTTP and got some funcky Unicode. Since responsetext is returned as Unicode, probably the Print statement renders it as ANSI. My question is: I would like to get the same rendering for the translated Chinese chars as I would by manually navigating the site. Anyone? Stan

stanl
06-26-2007, 05:44 AM
If this helps, I have attached code which places the response into a Stream Object for correct translation to Chinese in the local .htm file - also included the xStandard code which does not work at all, yet I believe both reference the same underlying API???


Sub googletrans()
Dim oHTTP As Object
f = ActiveWorkbook.Path & "\gtran.htm"
cURL = "http://www.translate.google.com/translate_t?"
cPost = "langpair=en|zh-CN&text=apple"
Set oHTTP = CreateObject("Msxml2.XMLHTTP")
oHTTP.Open "POST", cURL & cPost, False
oHTTP.SetRequestHeader "UserAgent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)"
oHTTP.Send ""
Set oS = CreateObject("ADODB.Stream")
oS.Open
oS.Position = 0
'oS.Charset = "gb2312" 'Chinese charset in registry
oS.Charset = "utf-8"
oS.WriteText oHTTP.ResponseText
oS.SetEOS
oS.Position = 0
oS.Savetofile f, 2
oS.Close
End Sub

Sub googletrans1() 'uses xStandard HTTP
Dim oHTTP As Object
f = ActiveWorkbook.Path & "\gtran.htm"
cURL = "http://www.translate.google.com/translate_t?"
cPost = "langpair=en|zh-CN&text=apple"
Set oHTTP = CreateObject("XStandard.HTTP")
oHTTP.AddRequestHeader "UserAgent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)"
oHTTP.Post cURL, cPost
Set oS = CreateObject("ADODB.Stream")
oS.Open
oS.Position = 0
'oS.Charset = "gb2312"
oS.Charset = "utf-8"
oS.WriteText oHTTP.ResponseString
oS.SetEOS
oS.Position = 0
oS.Savetofile f, 2
oS.Close
End Sub

Charlize
06-26-2007, 06:04 AM
If you just want a translation into excel, run this and wait after providing a word to translate : Sub Translate_google()
Dim ie As Object
Dim WebPage As String
Dim wb As Workbook
Dim ws As Worksheet
Dim vWord As String
Dim current As Workbook
Dim rng As Range
Dim lrow As Long
Application.ScreenUpdating = False
Set current = ActiveWorkbook
lrow = current.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
vWord = Application.InputBox("Give a word to translate to Chinese", _
"Translate to Chinese")
Set ie = CreateObject("InternetExplorer.Application")

WebPage = "http://translate.google.com/translate_t?" & _
"langpair=en|zh-CN&ie=UTF8&text=" & vWord
ie.Visible = False
ie.Navigate WebPage
'Loop until IE pages is fully loaded
Do Until ie.ReadyState = 4 'READYSTATE_COMPLETE
Loop

ie.ExecWB 17, 0
'Copy to the clipboard
Application.Wait (Now + TimeValue("0:00:1"))
ie.ExecWB 12, 0
Application.Wait (Now + TimeValue("0:00:1"))
'Close Internet Explorer
ie.Quit
Set ie = Nothing
Application.Wait (Now + TimeValue("0:00:1"))
'Create new workbook where we are going to paste the clipboard
Set wb = Workbooks.Add
Set ws = wb.Worksheets.Add
Application.Wait (Now + TimeValue("0:00:3"))
ws.Paste
Application.Wait (Now + TimeValue("0:00:3"))
Set rng = ActiveWorkbook.Sheets(1).Range("D11")
rng.Copy
current.Worksheets(1).Range("A" & lrow + 1).Value = vWord
current.Worksheets(1).Range("B" & lrow + 1).PasteSpecial
Application.DisplayAlerts = False
wb.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("A1").Select
End SubWhen exporting to text, the chinese characters become ??. So the characters display well in excel but I haven't been able to export them.

stanl
06-26-2007, 10:55 AM
I appreciate your taking time; I had forgotten about the ExecWB and the clipboard. I want to stay with HTTP. Jon at xStandard wrote me saying he thinks the issue is with VBA and it's handling of utf-8, rather than their control. However, another source told me the xStandard control uses multibyte and not UNICODE and that is the issue. If you modify my original sub you can get just the translation as a simple html:


Sub googletrans()
Dim var
Dim oHTTP As Object
f = ActiveWorkbook.Path & "\gtran.htm"
cURL = "http://www.translate.google.com/translate_t?"
cPost = "langpair=en|zh-CN&text=large panda bear" 'Chinese
'cPost = "langpair=en|de&text=large panda bear" 'German
Set oHTTP = CreateObject("Msxml2.XMLHTTP")
oHTTP.Open "POST", cURL & cPost, False
oHTTP.SetRequestHeader "UserAgent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)"
oHTTP.Send ""
var = "<HTML><BODY>" & vbCrLf
ctxt = oHTTP.ResponseText
n = InStr(ctxt, "id=result_box") + 22
ctxt = Mid(ctxt, n, 500)
ctxt = Mid(ctxt, 1, InStr(ctxt, "</div") - 1)
var = var & ctxt & vbCrLf & "</BODY></HTML>"
Set oS = CreateObject("ADODB.Stream")
oS.Open
oS.Position = 0
oS.Charset = "utf-8"
oS.WriteText var
oS.SetEOS
oS.Position = 0
oS.Savetofile f, 2
oS.Close
End Sub


this can be paste into Excel as well.

Thanks Again. Stan