I have the following code which is used to translate the text in a worksheet's cells from any language to English. It is called from a For-Each-cell loop. It works most of the time but once in a while the source cell is blanked out because objDiv doesn't return a translation. If I restore the cell and run the loop again, that cell or randomly other cells my be blanked out. Also calling the routine from a loop means that every time it's called the objects need to be recreated which I assume is wasteful of time. Finally, if there is a lot of data to translate, Excel bogs down and eventually doesn't respond. Can someone improve this for me?
Code:
Private Function GoogleTranslate(strInput As String, strFromLang As String, strToLang As String) As String
Dim strURL As String
Dim objHTTP As Object
Dim objHTML As Object
Dim objDivs As Object
Dim objDiv As Variant
strURL = "https://translate.google.com/m?hl=" & strFromLang & _
"&sl=" & strFromLang & _
"&tl=" & strToLang & _
"&ie=UTF-8&prev=_m&q=" & strInput
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
objHTTP.Open "GET", strURL, False
objHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ""
Set objHTML = CreateObject("htmlfile")
With objHTML
.Open
.Write objHTTP.ResponseText
.Close
End With
Set objDivs = objHTML.getElementsByTagName("div")
For Each objDiv In objDivs
If objDiv.className = "t0" Then
GoogleTranslate = objDiv.innerText
Exit For
End If
Next objDiv
Set objHTML = Nothing
Set objHTTP = Nothing
Set objDivs = Nothing
End Function