Selenium + VBA to Control Chrome

Question

I use selenium + vba to launch chrome to open 10 urls listed in cells range("A1:A10"). I am not familiar with selenium, after trying a lot of times I finally come out below clunky codes.

Private selenium As New ChromeDriver

Sub test()
Dim cell As Range
Dim keys As New selenium.keys
Dim pageNo As Integer

    pageNo = 0
    selenium.Start "chrome", "http://www.google.com/"
    For Each cell In Range("A1:A10")
        If pageNo >= 1 Then
            selenium.SendKeys keys.Control & "t" + keys.Control & "t"
            selenium.SwitchToNextWindow
        End If
        selenium.Get cell.Value
        pageNo = pageNo + 1
    Next
End Sub

Several troubles and questions raised:

1. I am confused why I have to send 2 times of "Ctrl+t" keys to successfully open new tab.

2. Why that I have to add selenium.SwitchToNextWindow after opening new tab, or the next url will be opened on the original tab, not new tab?

3. Quite often after opening 3~5 urls, vba pops up "not enough memory" error and stops loading the next url. But if I run the codes line by line in debugging environment, all 10 urls can be successfully opened. Why's that?

4. I googled that if I want chrome not to exit after finishing running macro, I have to declare object selenium outside the sub though I do not quite get the reason. Could anyone help explain me in plain way?

If any experts can help me optimize my codes, that would be much appreciated! My system is Win7 64bit + Excel 2010.

Thanks with best regards.

Answer 1

I am confused why I have to send 2 times of "Ctrl+t" keys to successfully open new tab.

The modifier key should be in the first argument and the key in the second one:

driver.SendKeys Keys.Control, "t"

In your case, a better way would be to open a new window with some Javascript:

Private Keys As New selenium.Keys
Private driver As New selenium.ChromeDriver

Sub test()
  Const JS_NEW_WINDOW = "window.open(arguments[0], name);"

  driver.Get "http://www.google.com/"
  For i = 0 To 9
    If i Then
      driver.ExecuteScript JS_NEW_WINDOW, "http://www.google.com/"
      driver.SwitchToNextWindow
      driver.FindElementById("lst-ib").SendKeys "some text " & i
    End If
  Next
End Sub

Why that I have to add selenium.SwitchToNextWindow after opening new tab, or the next url will be opened on the original tab, not new tab?

The context of the driver remains the same when a new window is opened. You have to explicitly tell the driver that you want to operate on another window.

Quite often after opening 3~5 urls, vba pops up "not enough memory" error and stops loading the next url. But if I run the codes line by line in debugging environment, all 10 urls can be successfully opened. Why's that?

Probably because some of the memory is released once the page is fully loaded. But since you are loading all the pages in a row, it may not leave the browser enough time to manage it's memory. I would try to disable the plugins if any (especially Flash) and add some waiting:

Dim driver As New Selenium.ChromeDriver
driver.SetPreference "plugins.plugins_disabled", Array("Adobe Flash Player")
For i = 0 To 9
  driver.Get "http://www.google.com/"
  Waiter.wait 500  ' waits 500ms
Next

I googled that if I want chrome not to exit after finishing running macro, I have to declare object selenium outside the sub though I do not quite get the reason. Could anyone help explain me in plain way?

The driver is automatically terminated once the variable holding the driver is no longer used. Check this link to get more information about variable and scopes: https://support.microsoft.com/en-gb/kb/141693 . So to keep the driver alive outside of a procedure, it must be declared outside:

Dim driver As Selenium.ChromeDriver

Sub Main
  Set driver = New Selenium.ChromeDriver
  driver.Get "http://www.google.com/"

End Sub