Using macro's to translate text via the internet

I am English speaking, new to working in Japan. Most of the project documentation is in Japanese. For my own purposes, I need to translate it into English. Most of what I am working with is in the form of columns of data.

I can do this by cutting and pasting each cell's contents into Google by going to

http://www.google.com.au/language_tools

And then paste the result back into Excel.

But there is alot of text to translate and new documents appear almost every day.

I tried a piece of software called "EditGrid", it says that it uses the Google functionality. It kind of does what I want, but not from Japanese only French, German, Spanish ....

Google provides the ability to translate a web page. So what this software does, I guess, is transfer the file to the internet, translates it, and fires it back.

I would not know how to do this ?? I have tried, in my ignorance, saving the Excel file as a web page on my local drive. But Google does not allow this, errors.

Can this be done via Macro ?

I think that I mean, go to a cell containing Japanese, copy contents, open Google's translation utility on the web, paste, hit the translate facility, then copy and paste back...

Below is anm example of the data that I am working with, but often it is dotted around the sheet, more randomly.

SEQ NO Description


1 1 査定依頼書
2 2 販売手数料設定機能
3 3 商談伺作成機能
4 4 商談伺承認機能
5 5 商談管理機能
6 6 支払条件変更機能
7 7 "商談伺書
"
8 8 車両・オプションマスタのI/F
9 9 "シャシ、メーカーオプションマスタの原価、建値再計算機能

"
10 10 見積修正機能
11 11 ロット販売用見積作成機能


Any help much appreciated. Thanks.

This is something I wrote awhile ago. It's not exactly what you want, but it will get you started. It's from english to japanese but only for a word you type in. You'll have to examine the html page of the translation page of google to find the value off japanese to english. Probably ja|en .

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
    WebPage = "http://translate.google.com/translate_t?" & _
              "langpair=en|ja&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.Visible = False
    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

Charlize

thanks for this. It does work, but I guess I really am looking to translate the whole spreadsheet at once. It is kinda slow, but as you say it is a start and I am not ungrateful.

Thanks.