0

I tried seeing what was the problem by making ie.visible(true). It turns out that when I use:

 ie.navigate "https://translate.google.com/#" & ilang & "/" & olang & "/" & cell.Value

it doesn't reload the page when it should. For example, refer to the images:

http://i.imgur.com/udr7Iqo.jpg

http://imgur.com/33HOyNB.jpg

So it enters the new URL in the address bar but doesn't actually navigate to that URL.
Code is here (Subroutine):

Sub Test()
    Dim str As String
    str = Translate(Range("B3:B7"), "auto", "hi")
End Sub

Actual Code:

'A function called translate
Public Function Translate(r As Range, ilang As String, olang As String) As String
    'Declaring all variables
    Dim ie As InternetExplorer
    Dim y As Integer
    Dim ys As String
    Dim urmsg As String
    Dim result As String

    y = 0
    Set ie = New InternetExplorer

    ie.Visible = True

    For Each cell In r
        ie.navigate "https://translate.google.com/#" & ilang & "/" & olang & "/" & cell.Value
        Do While ie.Busy = True Or ie.readyState <> 4: DoEvents: Loop

        y = y + 1
        result = ie.document.getElementById("result_box").innerText
        Sheets("Sheet1").Range("G" & y).Value = result
    Next cell

    urmsg = MsgBox(ys & " Translations Executed...", vbOK, "Prompt")
    ie.Quit
    Translate = " "
End Function

Please tell me what the issue is so that I can solve it. Also, go easy on me if the code is very choppy and has unnecessary lines, I've only studied VBA for a few days.

Thank you.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Aditya Singla
  • 11
  • 1
    You might have a look here to see how translating in Excel can be solved: [1] [How can I use Google Translate API to Translate text in Microsoft Excel](https://stackoverflow.com/questions/41671778/how-can-i-use-google-translate-api-to-translate-text-in-microsoft-excel) or [2] [Translate text using VBA](https://stackoverflow.com/questions/19098260/translate-text-using-vba). Also Google does have a [Translation API](https://cloud.google.com/translate/docs/reference/rest) that you should use instead of scraping the web page (which might not be legal: check Google's terms of use). – Pᴇʜ Jun 29 '17 at 07:32
  • Hi. I didn't know it was illegal to do so, however, I'm not using this code for commercial use, rather I'm just trying to learn the language. Thank you for the reply! @Peh – Aditya Singla Jun 29 '17 at 07:44
  • I did not say it was illegal but you need to check Google's terms of use if it is legal or not. Most websites generally disallow automated web scraping (commercial AND personal use). So you need to check this or use the API instead. – Pᴇʜ Jun 29 '17 at 08:00

0 Answers0