I've been playing with a function I found online which uses Google Translate to convert French text to English. It works but has the following problems:
1. Speed varies dramatically, from a few seconds to minutes. Some of this may be the internet connection within the company, as it does fluctuate.
2. If I use it to translate multiple text boxes on a form via clicking one button, it will sometimes translate one, sometimes two, rarely all. If I click the relevant button again, it will usually translate the box(es) it missed the first time.
I found a post on another site where someone was saying that he found using a combination of HTML controls and XML to be faster. My problem with this one is that I'm returning the actual HTML (including an error message saying the URL isn't found), as opposed to the return value I want.
I'm hoping someone is familiar enough with this kind of thing to notice a simple error I'm making.
Public declarations are as follows:
This code is behind On Click event of the translation button:
Original, working but slow/inconsistent code is as follows:
'New' XML code is as follows:
1. Speed varies dramatically, from a few seconds to minutes. Some of this may be the internet connection within the company, as it does fluctuate.
2. If I use it to translate multiple text boxes on a form via clicking one button, it will sometimes translate one, sometimes two, rarely all. If I click the relevant button again, it will usually translate the box(es) it missed the first time.
I found a post on another site where someone was saying that he found using a combination of HTML controls and XML to be faster. My problem with this one is that I'm returning the actual HTML (including an error message saying the URL isn't found), as opposed to the return value I want.
I'm hoping someone is familiar enough with this kind of thing to notice a simple error I'm making.
Public declarations are as follows:
Code:
Const langCode = ("auto,en,fr,es")
Public Enum LanguageCode
InputAuto = 0
InputEnglish = 1
InputFrench = 2
InputSpanish = 3
End Enum
Public Enum LanguageCode2
ReturnEnglish = 1
ReturnFrench = 2
ReturnSpanish = 3
End Enum
Code:
Private Sub btnTranslate_Click()
Dim strRepair As String
Dim strContention As String
Dim strDefect As String
On Error GoTo Err_Point
strRepair = Nz(REPAIR_DESCRIPTION, "")
strContention = Nz(CLAIM_CONTENTION_DESCRIPTION, "")
strDefect = Nz(CLAIM_DEFECT_DESCRIPTION, "")
REPAIR_DESCRIPTION_TRANSLATED = AutoTranslate(strRepair)
CLAIM_CONTENTION_DESCRIPTION_TRANSLATED = AutoTranslate(strContention)
CLAIM_DEFECT_DESCRIPTION_TRANSLATED = AutoTranslate(strDefect)
Exit Sub
Err_Point:
strMsg = Err.Number & Chr(13) & Err.Description
strTitle = "Error during translation"
strResponse = MsgBox(strMsg, vbCritical, strTitle)
End Sub
Code:
Public Function AutoTranslate(ByVal Text As String, Optional LanguageFrom As LanguageCode, Optional LanguageTo As LanguageCode2) As String
'Add references to [Microsoft Internet Controls], [Microsoft Scripting Runtime] and [Microsoft 16.0 Object Library]
On Error GoTo Err_Point
Dim langFrom As String
Dim langTo As String
Dim IE As InternetExplorer
Dim URL As String
Dim liCount As Integer
Dim myArray
Text = Replace(Text, "/", "")
Text = Replace(Text, "\", "")
For liCount = 0 To 31
Text = Replace(Text, Chr(liCount), "")
Next liCount
If IsMissing(LanguageFrom) Then
LanguageFrom = InputFrench
End If
If IsMissing(LanguageTo) Then
LanguageTo = ReturnEnglish
End If
myArray = Split(langCode, ",")
langFrom = myArray(LanguageFrom)
langTo = myArray(LanguageTo)
URL = "[URL]https://translate.google.com/[/URL]#" & langFrom & "/" & langTo & "/" & Text
Set IE = New InternetExplorer
IE.Visible = True
IE.Navigate URL
Do Until IE.ReadyState = 4
DoEvents
Loop
AutoTranslate = IE.Document.getElementByID("result_box").innerText
IE.Quit
Set IE = Nothing
Exit Function
Err_Point:
strMsg = Err.Number & Chr(13) & Err.Description
strTitle = "Error during translation"
strResponse = MsgBox(strMsg, vbCritical, strTitle)
End Function
Code:
Public Function AutoTranslate(ByVal Text As String) As String
'Add references to [Microsoft HTML Controls] and [Microsoft XML 6.0]
On Error GoTo Err_Point
Dim IE As MSXML2.XMLHTTP60
Dim HTMLDoc As MSHTML.HTMLDocument
Dim HTMLBody As MSHTML.HTMLBody
Dim URL As String
Set IE = New MSXML2.XMLHTTP60
URL = "[URL]https://translate.google.com/#fr/en/[/URL]" & Text
IE.Open "GET", URL, True
IE.send
While IE.ReadyState <> 4
DoEvents
Wend
Set HTMLDoc = New MSHTML.HTMLDocument
Set HTMLBody = HTMLDoc.body
HTMLBody.innerHTML = IE.responseText
AutoTranslate = HTMLBody.innerHTML
Exit Function
Err_Point:
strMsg = Err.Number & Chr(13) & Err.Description
strTitle = "Error during translation"
strResponse = MsgBox(strMsg, vbCritical, strTitle)
End Function