Selenium + VBA to control Chrome

Question

I use selenium + vba to run chrome to open 10 URLs listed in a range of cells ("A1: A10"). I am not familiar with selenium, having tried it many times, I finally went below the clumsy 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 

A few problems and questions raised:

• I am confused why I need to send the "Ctrl + t" keys 2 times in order to successfully open a new tab.

• Why do I need to add selenium.SwitchToNextWindow after opening a new tab, or will the next URL be opened in the original tab and not in the new tab?

• Quite often, after opening 3 ~ 5 URLs, vba gives an "out of memory" error and stops loading the next URL. But if I run the codes line by line in the debugging environment, all 10 URLs can be opened successfully. Why?

• I googled that if I want chrome to not exit after the macro finishes, I must declare the object selement outside the subdirectory, although I did not quite understand the reason. Can someone help explain to me in a simple 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 need to send the "Ctrl + t" keys 2 times in order to successfully open a new tab.

The modifier key must be in the first argument, and the key of the second -

 driver.SendKeys Keys.Control, "t" 

In your case, the best 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 do I need to add selenium.SwitchToNextWindow after opening a new tab, or will the next url be opened on the original tab and not on the new tab?

The driver context remains unchanged when you open a new window. You must explicitly tell the driver that you want to work in another window.

Quite often, after opening 3 ~ 5 URLs, vba gives an "out of memory" error and stops loading the next URL. But if I run the codes line by line in the debugging environment, all 10 URLs can be opened successfully. Why?

Perhaps because part of the memory is freed after the page is fully loaded. But since you load all the pages in a row, this may not leave the browser enough time to manage memory. I would try to disable plugins, if any (especially Flash), and add some expectations:

 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 to not exit after the macro finishes, I have to declare the object selement outside of sub, although I do not quite understand the reason. Can someone help explain to me in a simple way?

The driver automatically terminates after the variable holding the driver is no longer used. Check out this link for more information on variables and areas: https://support.microsoft.com/en-gb/kb/141693 . Therefore, in order to keep the driver out of the procedure, he must be declared outside:

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