Hey Guys, still unfortunately not great with VBA, so I'm pinching this translation routine, and i've now got it 'working' so far as I can call it as Translate(cell,0,1) in the worksheet, but it's coming up with an error, saying "Compile Error: Ambiguous name detected: ~"
Source: Translate Text in Excel Jon von der Heyden
I've highlighted what VB takes me to, but i can't see any error - though this is likely because my VBA is lacking in anything but truly obvious solutions.
Any help would be appreciated!!
Thanks
F
Source: Translate Text in Excel Jon von der Heyden
Code:
'By Jon von der Heyden
'http://jonvonderheyden.net/excel/translate-text-in-excel/
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
[COLOR=#ff0000]Public Function Translate(ByVal strText As String, _
Optional ByVal eFrom As eLanguage = auto_detect, _
Optional ByVal eTo As eLanguage = English) As String[/COLOR]
Dim strUrl As String
Dim strResult As String
strText = Replace$(strText, Chr$(32), "%20")
strText = Replace$(strText, Chr$(160), "%20")
strUrl = "[URL]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[/URL]"
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
strResult = Replace$(Mid$(CStr(Split(strResult, ",")(0)), 4), Chr$(34), "")
Translate = strResult
End FunctionI've highlighted what VB takes me to, but i can't see any error - though this is likely because my VBA is lacking in anything but truly obvious solutions.
Any help would be appreciated!!
Thanks
F
