I've "created" (based on some other blog - I can't remember the address, sorry) a vbs that translated the an excel sheet.
Also, it is better to execute the vbs in console - a bat file:
cscript "path\translate.vbs"
Code for "path\translate.vbs":
Option Explicit
REM We use "Option Explicit" to help us check for coding mistakes
REM the Excel Application
Dim objExcel
REM the path to the excel file
Dim excelPath
REM how many worksheets are in the current excel file
Dim worksheetCount
Dim counter
REM the worksheet we are currently getting data from
Dim currentWorkSheet
REM the number of columns in the current worksheet that have data in them
Dim usedColumnsCount
REM the number of rows in the current worksheet that have data in them
Dim usedRowsCount
Dim row
Dim column
REM the topmost row in the current worksheet that has data in it
Dim top
REM the leftmost row in the current worksheet that has data in it
Dim left
Dim Cells
dim currentCell
REM the current row and column of the current worksheet we are reading
Dim curCol
Dim curRow
REM the value of the current row and column of the current worksheet we are reading
Dim word, tabname
rem tab name
if Wscript.Arguments.Count = 2 then
tabname = Wscript.Arguments(1)
excelPath = Wscript.Arguments(0)
end if
REM where is the Excel file located?
excelPath = "path"
tabname = "sheet"
WScript.Echo "Reading Data from " & excelPath & " " & tabname
REM Create an invisible version of Excel
Set objExcel = CreateObject("Excel.Application")
REM don't display any messages about documents needing to be converted
REM from old Excel file formats
objExcel.DisplayAlerts = 0
REM open the excel document as read-only
REM open (path, confirmconversions, readonly)
objExcel.Workbooks.open excelPath, false, false
REM How many worksheets are in this Excel documents
workSheetCount = objExcel.Worksheets.Count
dim name
Dim objSvrHTTP
dim translateWord
Set objSvrHTTP = CreateObject("Msxml2.XMLHTTP.4.0")
REM Loop through each worksheet
For counter = 1 to workSheetCount
WScript.Echo "-----------------------------------------------"
Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(counter)
currentWorkSheet.Unprotect
name = CStr(currentWorksheet.Name)
WScript.Echo "Reading data from worksheet " & name & vbCRLF
If StrComp(Trim(CStr(name)), Trim(CStr(tabname)), vbTextCompare) = 0 then
REM how many columns are used in the current worksheet
usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count
REM how many rows are used in the current worksheet
usedRowsCount = currentWorkSheet.UsedRange.Rows.Count
REM What is the topmost row in the spreadsheet that has data in it
top = currentWorksheet.UsedRange.Row
REM What is the leftmost column in the spreadsheet that has data in it
left = currentWorksheet.UsedRange.Column
Set Cells = currentWorksheet.Cells
REM Loop through each row in the worksheet
For row = 0 to (usedRowsCount-1)
REM Loop through each column in the worksheet
For column = 0 to usedColumnsCount-1
REM only look at rows that are in the "used" range
curRow = row+top
REM only look at columns that are in the "used" range
curCol = column+left
REM get the value/word that is in the cell
set currentCell = Cells(curRow,curCol)
word = currentCell.Value
REM display the column on the screen
if word <> "" then
'WScript.Echo (word)
'objSvrHTTP.open "GET", "http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&langpair=nl|en&q=" & word, false
objSvrHTTP.open "GET", "http://api.microsofttranslator.com/v2/Http.svc/Translate?appId=your_own_id&from=nl&to=en&text=" & word, false
objSvrHTTP.send
translateWord = objSvrHTTP.responseText
translateWord = Replace(translateWord, "", "")
translateWord = Replace(translateWord, "", "")
REM translateWord3 = Replace(translateWord2, """responseDetails""", "")
REM translateWord4 = Replace(translateWord3, """responseStatus""", "")
REM translateWord5 = Replace(translateWord4, "null", "")
REM translateWord6 = Replace(translateWord5, "200", "")
REM translateWord7 = Replace(translateWord6, "{", "")
REM translateWord8 = Replace(translateWord7, "}", "")
REM translateWord9 = Replace(translateWord8, ":", "")
REM translateWord10 = Replace(translateWord9, ": ", "")
REM translateWord11 = Replace(translateWord10, ",", "")
REM translateWord12 = Replace(translateWord11, ", ", "")
REM translateWord13 = Replace(translateWord12, """", "")
if Trim(CStr(word)) <> Trim(CStr(translateWord)) then
WScript.Echo Trim(CStr(translateWord))
if currentCell.Comment Is Nothing then
currentCell.AddComment()
end if
currentCell.Comment.Text Trim(CStr(translateWord))
currentCell.Comment.Shape.Width = 220
currentCell.Comment.Shape.Height = 50
end if
Wscript.Sleep 500
end if
Next
Next
end if
REM We are done with the current worksheet, release the memory
Set currentWorkSheet = Nothing
Next
objExcel.Workbooks(1).Save
objExcel.Workbooks(1).Close
objExcel.Quit
Set currentWorkSheet = Nothing
REM We are done with the Excel object, release it from memory
Set objExcel = Nothing
No comments:
Post a Comment