Microsoft Excel Webinar

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:

    VB:
     
    Sub googletrans() 
        Dim oHTTP As Object 
        f = ActiveWorkbook.Path & "\gtran.htm" 
        Open f For Output As #1 
        cURL = "[URL="http://www.translate.google.com/translate_t"]http://www.translate.google.com/translate_t[/URL]?" 
        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 
    
    
    Formatting tags added by mark007
    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???

    VB:
     
    Sub googletrans() 
        Dim oHTTP As Object 
        f = ActiveWorkbook.Path & "\gtran.htm" 
        cURL = "[URL="http://www.translate.google.com/translate_t"]http://www.translate.google.com/translate_t[/URL]?" 
        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 = "[URL="http://www.translate.google.com/translate_t"]http://www.translate.google.com/translate_t[/URL]?" 
        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 
    
    
    Formatting tags added by mark007

  3. #3
    If you just want a translation into excel, run this and wait after providing a word to translate :
    VB:
    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 = "[URL="http://translate.google.com/translate_t"]http://translate.google.com/translate_t[/URL]?" & _ 
        "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 
    
    
    Formatting tags added by mark007
    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:

    VB:
     
    Sub googletrans() 
        Dim var 
        Dim oHTTP As Object 
        f = ActiveWorkbook.Path & "\gtran.htm" 
        cURL = "[URL="http://www.translate.google.com/translate_t"]http://www.translate.google.com/translate_t[/URL]?" 
        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 
    
    
    Formatting tags added by mark007
    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
  •