• 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

    Hui...

  • 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

YasserKhalil

Well-Known Member
Hello everyone
The following code translates from column A from Arabic to English based on google translate.
Code:
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
Skipper:
            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
    Dim XMLHTTP As New MSXML2.XMLHTTP

    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?
 

YasserKhalil

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 …​
 

YasserKhalil

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
 

Chihiro

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.
 
Top