Using Google Translate (1 Viewer)

Alc

Registered User.
Local time
Today, 08:14
Joined
Mar 23, 2007
Messages
2,343
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:
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
This code is behind On Click event of the translation button:
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
Original, working but slow/inconsistent code is as follows:
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
'New' XML code is as follows:
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
 

Orthodox Dave

Home Developer
Local time
Today, 13:14
Joined
Apr 13, 2017
Messages
218
Hi there,

I see no-one has answered your question, so I thought I'd at least have a go at giving you something useful (for what it's worth). The obvious solution would have been for Google Translate (or Microsoft) to provide a simple downloadable version so you didn't have to connect via the internet. There are offline versions out there, but how easy they are to connect via VBA is unclear.

I noticed both your AutoTranslate functions (old and new) include "DoEvents", which (I understand) switches control back to the operating system while the code is being processed, instead of making everything wait till the code is finished. In your button sub, you have 3 calls to AutoTranslate - one right after the other. I suspect DoEvents is allowing the 2nd function to be get started while the 1st is still running, which might explain your inconsistent results. Possibly the original code was only designed to translate a single piece of text?

I also notice in your "new" code you have the line:
Dim HTMLBody As MSHTML.HTMLBody

It is important never to use Reserved Words in VBA, which "HTMLBody" obviously is. It can cause all sorts of errors.

Hope there's something there that helps. It would be useful to find out what actually works in the end.
 

Users who are viewing this thread

Top Bottom