Excel Hints

Results 1 to 4 of 4

Thread: Google Translation Issue

  1. #1

    Google Translation Issue

    This is more a vba than an excel question, but I am running the sub below from Excel:

    [vba]
    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
    [/vba]

    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

  2. #2
    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???

    [vba]
    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

    [/vba]

  3. #3
    If you just want a translation into excel, run this and wait after providing a word to translate : [vba]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 Sub[/vba]When exporting to text, the chinese characters become ??. So the characters display well in excel but I haven't been able to export them.

  4. #4
    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:

    [vba]
    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
    [/vba]

    this can be paste into Excel as well.

    Thanks Again. Stan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •