Translate from google using XMLHTTP

[YasserKhalil]

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
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?

 

[Chihiro]

You generally can't. Google API has blacklisted most public proxy to prevent just such use back in 2016/2017.

If you have private proxy that may still work. I'm pretty sure that I had given you the same explanation a while back.

Edit:
Here it is.
https://chandoo.org/forum/threads/scraping-caused-ip-banning.36748/#post-220502

 

[YasserKhalil]

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]

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]

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]

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.

 

[Chihiro]

Here’s the link.

Google Analytics | Google for Developers

developers.google.com

10 per sec and 50k per day. Any more will require payment.

 

[YasserKhalil]

Thank you very much Chihiro