Translate to Multiple Languages with Power Query - Part 3

I tested the part 3, and same thing :
Thanks if you have any good idea ?

Hi took a VBA code from someone (on the web) and I did a quick fix to manage carriage return (or new line). it could help you

Function GOOGLETRANSLATE(Text As String, source_language As String, target_language As String) As String
Dim URL As String
URL = "Google Translate" & source_language & "&tl=" & target_language & "&hl=en&ie=UTF-8&q=" & CLEANTEXT(Text)
'URL = "Google Translate" & source_language & "&tl=" & target_language & "&hl=en&ie=UTF-8&q=" & Text
Dim XMLHTTPS As Object
Set XMLHTTPS = CreateObject("MSXML2.ServerXMLHTTP")
XMLHTTPS.Open "GET", URL, False
XMLHTTPS.setrequestheader "User-Agent", "Mozilla/5.0 (compatible;MSIE 6.0; WindowsNT 10.0))"
XMLHTTPS.send ""

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

Dim HTMLDc As HTMLDocument
Set HTMLDc = HTML

Dim Class As Object
Set Class = HTMLDc.getElementsByClassName("result-container")(0)
If Not Class Is Nothing Then
'GOOGLETRANSLATE = Class.innerText
GOOGLETRANSLATE = SPECIALREPLACE(Class.innerText)

End If

Set Class = Nothing
Set HTML = Nothing
Set XMLHTTPS = Nothing

End Function

Function CLEANTEXT(ByVal Text As String) As String
Text = Replace(Text, "&", " <advbcommercialand> ")
Text = Replace(Text, "%", " <advbpourcent> ")
Text = Replace(Text, Chr(10), Chr(13) & Chr(10))
Text = Replace(Text, Chr(13), Chr(13) & Chr(10))
Text = Replace(Text, Chr(10) & Chr(10), Chr(10))
Text = Replace(Text, Chr(13) & Chr(10) & Chr(13) & Chr(10), Chr(13) & Chr(10))
Text = Replace(Text, Chr(13) & Chr(10), " <advb1310> ")

CLEANTEXT = Text
End Function


Function SPECIALREPLACE(ByVal Text As String) As String
Text = Replace(Text, "<advbpourcent>", "%")
Text = Replace(Text, "<advbcommercialand>", "&")
Text = Replace(Text, "<advb1310>", Chr(13) & Chr(10))
Text = Replace(Text, "<advb1310 >", Chr(13) & Chr(10))
Text = Replace(Text, "< advb1310>", Chr(13) & Chr(10))
Text = Replace(Text, "< advb1310 >", Chr(13) & Chr(10))
Text = Replace(Text, " ", " ")

SPECIALREPLACE = Text
End Function


It looks a good result :

In the implementations I provided as sample, you need to replace the line breaks with spaces in the source without too many modification. Note that you'll get the translation as single lines.


Then use RemoveLineBreaks identifier afterwards.

Hi thx for your reply the idea is to keep the line break, but with the replace function it will be fine. Thanks