Public Sub TranslateSheets()
Const strDirectory As String = "C:\Users\Jon von der Heyden\Desktop\Desktop Files\temp\"
Dim strFileName As String
Dim wksTemp As Excel.Worksheet
Dim rngCell As Excel.Range
strFileName = Dir$(strDirectory & "*.xl*")
Do While Len(strFileName) > 0
With Excel.Workbooks.Open(Filename:=strDirectory & strFileName, ReadOnly:=True)
Set wksTemp = ThisWorkbook.Sheets.Add
wksTemp.Name = .Name
For Each rngCell In .Sheets(1).UsedRange
wksTemp.Range(rngCell.Address).Value = Translate(rngCell.Value, Auto_Detect, English)
Next rngCell
Call .Close(SaveChanges:=False)
strFileName = Dir
End With
Loop
End Sub
Thanks, but I just cant get it to work. There is no 'OK' to click. I am using 2010 is it different? It only seems to put the word that is in the first cell also and I need the whole sheet translated.
Option Explicit
Private Const CP_UTF8 = 65001
Private Declare Function WideCharToMultiByte Lib "Kernel32" ( _
ByVal CodePage As Long, ByVal dwflags As Long, _
ByVal lpWideCharStr As Long, ByVal cchWideChar As Long, _
ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, _
ByVal lpDefaultChar As Long, ByVal lpUsedDefaultChar As Long) As Long
Public Function UTF16To8(ByVal strUTF16 As String) As String
Dim strBuffer As String
Dim lngLength As Long
If Len(strUTF16) Then
lngLength = WideCharToMultiByte(CP_UTF8, 0, StrPtr(strUTF16), -1, 0, 0, 0, 0)
strBuffer = Space$(lngLength)
lngLength = WideCharToMultiByte(CP_UTF8, 0, StrPtr(strUTF16), -1, StrPtr(strBuffer), Len(strBuffer), 0, 0)
strBuffer = StrConv(strBuffer, vbUnicode)
UTF16To8 = Left$(strBuffer, lngLength - 1)
Else
UTF16To8 = ""
End If
End Function
Public Function URLEncode(ByVal strVal As String, _
Optional ByVal blnSpaceAsPlus As Boolean = False, _
Optional ByVal blnUTF8Encode As Boolean = True) As String
Dim strValCopy As String
Dim strLen As Long
Dim varResult As Variant
Dim lngChar As Long
Dim lngCharCode As Integer
Dim strChar As String
Dim strSpace As String
strValCopy = IIf(blnUTF8Encode, UTF16To8(strVal), strVal)
strLen = Len(strValCopy)
If strLen > 0 Then
ReDim varResult(strLen) As String
If blnSpaceAsPlus Then
strSpace = "+"
Else
strSpace = "%20"
End If
For lngChar = 1 To strLen
strChar = Mid$(strValCopy, lngChar, 1)
lngCharCode = Asc(strChar)
Select Case lngCharCode
Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
varResult(lngChar) = strChar
Case 32
varResult(lngChar) = strSpace
Case 0 To 15
varResult(lngChar) = "%0" & Hex(lngCharCode)
Case Else
varResult(lngChar) = "%" & Hex(lngCharCode)
End Select
Next lngChar
URLEncode = Join$(varResult, "")
End If
End Function
Option Explicit
Private Const strSHORTCODES As String = ",en,af,sq,ar,hy,az,eu,be,bn,bg,ca,zh,hr,cs,da,nl,eo,et,tl,fi,fr,gl,ka,de,el,gu,ht,iw,hi,hu,is,id,ga,it,ja,kn,ko,lo,la,lv,lt,mk,ms,mt,no,fa,pl,pt-PT,ro,ru,sr,sk,sl,es,sw,sv,ta,te,th,tr,uk,ur,vi,cy,yi"
Public Enum eLanguage
auto_detect = 0
english = 1
afrikaans = 2
Albanian = 3
Arabic = 4
Armenian = 5
Azerbaijani = 6
Basque = 7
Belarusian = 8
Bengali = 9
Bulgarian = 10
Catalan = 11
Chinese = 12
Croatian = 13
Czech = 14
Danish = 15
Dutch = 16
Esperanto = 17
Estonian = 18
Filipino = 19
Finnish = 20
French = 21
Galician = 22
Georgian = 23
German = 24
Greek = 25
Gujarati = 26
Haitian_Creole = 27
Hebrew = 28
Hindi = 29
Hungarian = 30
Icelandic = 31
Indonesian = 32
Irish = 33
Italian = 34
Japanese = 35
Kannada = 36
Korean = 37
Lao = 38
Latin = 39
Latvian = 40
Lithuanian = 41
Macedonian = 42
Malay = 43
Maltese = 44
Norwegian = 45
Persian = 46
Polish = 47
Portuguese = 48
Romanian = 49
Russian = 50
Serbian = 51
Slovak = 52
Slovenian = 53
Spanish = 54
Swahili = 55
Swedish = 56
Tamil = 57
Telugu = 58
Thai = 59
Turkish = 60
Ukrainian = 61
Urdu = 62
Vietnamese = 63
Welsh = 64
Yiddish = 65
End Enum
Public Function Translate(ByVal strText As String, _
Optional ByVal eFrom As eLanguage = auto_detect, _
Optional ByVal eTo As eLanguage = english, _
Optional ByVal blnPhonetic As Boolean = False) As String
Dim strUrl
Dim strTransText As String
Dim strResult As String
Dim varSplitText As Variant
Dim lngItem As Long
If strText = "" Then
Translate = ""
Exit Function
End If
strText = URLEncode(strText)
strUrl = "http://translate.google.com/translate_a/t?client=t&text={S}&hl=en&sl={F}&tl={T}&multires=1&pc=0&rom=1&sc=1"
strUrl = Replace$(strUrl, "{S}", strText)
strUrl = Replace$(strUrl, "{F}", Split(strSHORTCODES, ",")(eFrom))
strUrl = Replace$(strUrl, "{T}", Split(strSHORTCODES, ",")(eTo))
With CreateObject("MSXML2.XMLHTTP")
Call .Open("get", strUrl, False)
Call .Send
strResult = .responseText
End With
varSplitText = Split(Split(strResult, "]],")(0), "[")
If Not blnPhonetic Then
For lngItem = 3 To UBound(varSplitText)
strTransText = strTransText & Split(varSplitText(lngItem), ",")(0)
Next
Else
For lngItem = 3 To UBound(varSplitText)
strTransText = strTransText & Split(varSplitText(lngItem), ",")(2)
Next
End If
strResult = Replace(strTransText, """", "")
Translate = strResult
End Function