Excel Translate from English Language to Others via VBA Code

[Tarek_CTG]

Dear VBA Expert,
I want to translate some lines from English Language to other languages. Sourcing help from others, I have tried to build up following codes to create a function named GoogleTranslate, but it's not working. When I try to debug the code, nothing error is showing. The code is as follows:

Function GoogleTranslate(strSourceText As String, strSourceLangCode As String, strTargetLangCode As String) As String

'1st - creating a dynamic url

Dim strURL As String
strURL = "Google Translate" & strSourceLangCode & "&tl=" & strTargetLangCode & "&hl=en&ie=UTF-8&q=" & strSourceText

'2nd - do a web server API using XML HTTP Request

Dim XMLHTTP As Object
Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
XMLHTTP.Open "GET", strURL, False
XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (compatible; MSIE 6.0; Windows NT 10.0)"
XMLHTTP.send ""

'3rd - Create HTML Using HTTP Request response text

Dim ObjHTML As Object
Set ObjHTML = CreateObject("HTMLFile")
With ObjHTML
.Open
.write XMLHTTP.responseText
.Close
End With

'4th - Read the translated from HTML File Using HTML Web elements
'Microsoft HTML object Library

Dim HTMLDoc As HTMLDocument
Set HTMLDoc = ObjHTML

Dim ObjClass As Object
Set ObjClass = HTMLDoc.getElementsByClassName("result-container")(0)
If Not ObjClass Is Nothing Then
GoogleTranslate = ObjClass.innerText
End If

'releasing the memory
Set ObjClass = Nothing
Set ObjHTML = Nothing
Set XMLHTTP = Nothing

End Function

Can anyone please solve the issue? Thanks in advance.

 

[John_w]

I want to translate some lines from English Language to other languages.

Have you tried this code?

Google Translate Macro Function - How to rafear to Cell and/or Text in Quotes?

Hello experts, i modify actual script to create a function in Excel to translate from Google Translator with formula. In order to work, you need to add Reference "Microsoft Internet Controls" (Tools>References) I create it referring to a cell range value with syntax...

www.mrexcel.com

That links to the latest code and there are improvements suggested by other posters after it.

 

[Tarek_CTG]

Have you tried this code?

Google Translate Macro Function - How to rafear to Cell and/or Text in Quotes?

Hello experts, i modify actual script to create a function in Excel to translate from Google Translator with formula. In order to work, you need to add Reference "Microsoft Internet Controls" (Tools>References) I create it referring to a cell range value with syntax...

www.mrexcel.com

That links to the latest code and there are improvements suggested by other posters after it.

yes, I tried, what I need is a fixed google translate function in excel, like the function of sum, average etc. So that i can use the function wherever I need in a workbook/sheet.

 

[John_w]

The GoogleTranslate function in that thread is a UDF so you can use it in a cell formula exactly like a normal Excel function. For example:

=GoogleTranslate(A1,"en","es")

translates the text in A1 from English to Spanish.

The language codes are listed at Language support | Cloud Translation | Google Cloud

The code also includes the RegisterGoogleTranslateFunction macro which, if run, adds help for the function arguments to the Excel UI: