• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Translate from google using XMLHTTP


Well-Known Member
Hello everyone
The following code translates from column A from Arabic to English based on google translate.
Sub Test()
    Dim b As Boolean, lngFrom As String, lngTo As String, strText As String, strURL As String, strResponse As String, i As Long

    lngFrom = "ar"
    lngTo = "en"

    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Not IsEmpty(Cells(i, 1)) Then
            strText = Application.EncodeURL(Range("A" & i).Value)
            strURL = "https://translate.google.com/translate_a/t?client=s&text={S}&hl=en&sl={F}&tl={T}&multires=1&pc=0&rom=1&sc=1"

            strURL = Replace$(strURL, "{S}", strText)
            strURL = Replace$(strURL, "{F}", lngFrom)
            strURL = Replace$(strURL, "{T}", lngTo)
            strResponse = sendGETRequest(strURL)
            If InStr(strResponse, "Sometimes you may see this page if you are using advanced terms that robots") Then GoTo Skipper
            Range("B" & i).Value = Replace(strResponse, """", ""): b = True
            If b = False Then Exit For
        End If
    Next i

    If b Then MsgBox "Done...", vbInformation Else MsgBox "It Seems IP Blocked", vbExclamation
End Sub

Function sendGETRequest(URL As String) As String

    XMLHTTP.Open "GET", URL, False
    XMLHTTP.send ("")
    sendGETRequest = XMLHTTP.responseText
End Function
The code is working but after a while the IP is blocked .. How can I use Proxies in this code so as to prevent the IP block?


Well-Known Member
Thank you very much.
Is there a workaround to avoid IP Block...? For example: If I used Application.Wait after each loop, would this be useful or not?

Marc L

Excel Ninja
Hi !​
Instead of a request try just piloting IE or leave Google for another translator like Bing for example​
but I do not know if it has the same requests # limitation …​


Well-Known Member
I already did that using IE but this approach is too slow comparing to using XMLHTTP ...
I don't know about Bing's limitation too ..
Hope there is a solution for that


Excel Ninja
Just limit your query to below threshold set by google. I can’t remember off top of my head how much that was, but it’s fairly generous.