google translator vba code stop working

[trickard]

1.Today something happened and not sure why
I have this code a simple one that was provided last year and has worked fine to translate cells from English to Spanish

Public Function getGoogleTranslation(strSource As String, strSourceLang As String, strDestLang As String) As String 'Created by ShredDude
'06 September 2010
Dim strURL As String, x As String
'English = "en"
'Spanish = "es"
strURL = "https: translate dot google dot com/translate_a/t?client=t&text=" & _
Replace(strSource, " ", "%20") & _
"&hl=en&sl=" & strSourceLang & _
"&tl=" & strDestLang & "&multires=1&pc=0&rom=1&sc=1"
With CreateObject("msxml2.xmlhttp")
.Open "get", strURL, False
.send
x = .responseText
End With
getGoogleTranslation = Replace(Replace(Split(x, """,""")(0), "[", ""), """", "")
End Function
The issue I am having now is that today when I opened the program all I see is =value signs, it appears that the code all of a sudden isn't working.....all the workbooks are acting the same
I tried simply putting a simply sentence in a new workbook and that doesn't work either.....would you have any idea?
it worked fine since I got the coding but not now .....any help please would be appreciated
Tony

[mancubus]

welcome to the forum trickard.

please do not pm for asking help.


check this out:
http://jonvonderheyden.net/excel/translate-text-in-excel/

[trickard]

sorry you lost me on the pm statement above....but have tried that code before and didn't find it to work unless I did something wrong
the one I was using worked fine then just stopped.....wherever I had that code it simply showed a =value error.....is there a simple code that will let a person type a sentence in a cell and then have it translated in another cell in same sheet??
Tony

[mancubus]

this is a result of effective googling. :D



Function VBA_Translate(strSource As String, strSourceLang As String, strDestLang As String) As String

'adopted from: http://stackoverflow.com/questions/19098260/translate-text-using-vba
'set a reference to Microsoft Internet Controls from Tools, References in VBE

Dim j As Long
Dim Web_Translation

With CreateObject("InternetExplorer.Application")
.Visible = False
.Navigate "http://translate.google.com/#" & strSourceLang & "/" & strDestLang & "/" & strSource

While .Busy Or .ReadyState <> 4: DoEvents: Wend
Application.Wait (Now + TimeValue("0:00:05"))

Web_Translation = Split(Application.WorksheetFunction.Substitute(.Document.getElementById("result_box").innerHTML, "</SPAN>", ""), "<")
.Quit
End With

VBA_Translate = ""
For j = LBound(Web_Translation) To UBound(Web_Translation)
VBA_Translate = VBA_Translate & Right(Web_Translation(j), Len(Web_Translation(j)) - InStr(Web_Translation(j), ">"))
Next

End Function



TEST:


Sub test()
Dim TestString As String

TestString = "Hello world. Are you somewhere, feeling lonely?"

MsgBox VBA_Translate(TestString, "en", "es")End Sub