VBA Tutorial P3 – Interacting with a webpage with Selenium

Welcome back to this series! I was expecting you! If you remember from my last post, the one Selenium installation and other stuff, I mention that the interaction is mostly done through HTML elements.

As a person, you can see where to click, but the code can’t see as that, but it can see what we cannot see when looking at a webpage: the code behind that page!

So, HTML means HyperText Markup Language. It is used by web developers to tell our browsers how to display the elements. HTML gives a webpage the main structure, a structure that can be modified afterwards through CSS and JavaScript to make it look better and to add functionalities.

When I think about HTML, I imagine an apartment building. If you want to go to a specific apartment, you need to know where the apartment is placed, right? At what floor and in which direction. If you know the color of the door or the apartment number, that’s even better. So similar to this, the main objective with HTML is to use it to find the element you are looking for as specific as possible (e.g. the button you want to click, the textbox where you want to add some text or the displayed list you want to extract some information).

Note: Please visit the W3Schools to get familiarized about the type of tags that exist -> https://www.w3schools.com/html/html_basic.asp

Webpage elements with Selenium VBA can be targeted by using the syntagma <FindElementBy> or <FindElementsBy> right after specifying the driver. Notice the difference? Element vs Elements. The first syntagma will return one element, while the second will return all the elements that match that condition. More about this later.

You can tag the elements in 5 different ways:

  • ID: driver.FindElementById(“the_id_of_your_element”)
  • Class: driver.FindElementsByClass(“the_class_of_your_element”)
  • Tag: driver.FindElementsByTag(“the_tag_of_your_element”)
  • Xpath: driver.FindElementByXPath(“the_xpath_of_your_element “)
  • Name: driver.FindElementByName(“the_name_of_your_element”)

If you’re wondering what’s the difference between all of those, stay with me! I’m explaining it below!

An <ID> is something unique in a webpage. It’s like a nickname the developer gives to an element from the page. This is useful for both styling that element in a unique way and also for adding some specific functionality to that element. Just like your ID. It is only specific to you.

A <Class> is not that unique. It can be used to a large number of elements. The developer chose to do this because he/she wants to apply the same specific rules to a larger number of elements in the webpage. Like 5 different textboxes or rows in a table. That’s where we use FindElementsBy. A class is like a street with identical houses. While returning all classes, most definitely you only need to target one of them. That’s when you use a number as an index to specify which class you need from that collection. You just use [number] to get that particular occurrence of the class (e.g. driver.FindElementsByClass(“a_class”)[2] gives you the third element in that collection, the count starting with zero).

Note: You can also use the singular form, FindElementBy, but it will return you the first element with that class (driver.FindElementByClass(“a_class”) will return the same result as driver.FindElementsByClass(“a_class”)[0]) .

A <Tag> is an HTML element. If you visited the W3Schools link above, you have seen some tag examples.

Tags are the ones used to build the structure of a website. They can be targeted just as classes, but you will use FindElement(s)ByTag instead of FindElement(s)ByClass. The same rules apply regarding Element vs Elements.

A <XPath> is the exact location of an element. An XPath uses all the HTML tags before the element you are targeting to create the exact location of the element you are looking for. An XPath will look something like this: “/html/body/div[1]/div[3]/form/div[1]/div[1]/div[1]/div/div[2]/input”. Putting into words, here is how it looks like: the element I’m targeting, input, can be found in the second DIV from the only DIV of the first DIV of the first DIV of the first DIV from the only FORM from the third DIV of the first DIV of the BODY from HTML. Crazy, right?!

Note: You probably noticed that any 2 and 1 from the XPath above has been counted as 2 and 1, not as 3 and 2, as I mentioned earlier. That is because in XPath element count starts from one, while in VBA the element count starts from zero (this is applicable for any count in VBA and in other programming languages as well).

Note2: An XPath can be used to target collections, just as Classes, but that XPath is used with a different shape. More about this in other tutorials.

A <Name> is an identifier like ID or Class. The main difference is that a name is mostly used in back-end purposes while IDs and Classes are mostly used in front-end purposes. The targeting is the same as for the Class.

How to decide which targeting way to use?

Well, you just need to look at the structure of the webpage you’re trying automate. To do this, open that website in a browser (I’m going to open Google main page in Chrome). Then right-click on the element you want to interact with (I’m going to interact with the search area) and click on Inspect.

Image 1

You should now see something similar with the below image.

Image 2

According to this image, we can target the element in 4 different ways: by name, by class, by tag and by xpath.

Note: All elements can be targeted by Tag and XPath, but not all elements can be targeted by class, name or id.

Okay, enough theory! Let’s put this into practice.

We are going to continue with the same code used in the last tutorial. Let’s say that we want to make a robot that searches for stuff on Google and get back the first result (name & link).

To make it dynamic, we’re going to write what we want to search for in an Excel sheet and then we want to return the results in the same sheet.

Let’s open a blank Excel file so we have a clean slate. In column A, let’s add a header and some dummy data. The header is “To Search” and the data is “vba tutorial, python tutorial, free images, selenium tutorial vba, selenium tutorial python”. Let’s also add the headers for the two columns where we want to return the results, so add “Name” and “Link” in cells B1 and C1.

Image 3

Now let’s add a new module and the code from the last tutorial (ALT + F11 to open the coding window, the add a new module and copy-paste the below code, in case you don’t have the file from the last post. Also, don’t forget to add the Selenium Type Library from Reference tab!).

Sub launch_selenium()
Set driver = New WebDriver
driver.Start "Chrome"
driver.Get "http://www.google.com"
Stop
End Sub

Before we go to the code we actually need for this, there are two things we need to consider:

  1. Adding a delay so the driver waits until the page is fully loaded;
  2. Click on I agree when Google firstly opens.

For 1, it’s pretty simple. Just add driver.Timeouts.ImplicitWait = 5000 ‘ 5 seconds below the driver.Get line. It will add a default wait of 5 seconds before throwing an error if an element is not found.

As for 2, we need to locate that button and click on it. To do this, you can open Google in an Incognito window so it will open that agreement window so we can locate the button.

Image 4

As you can see in the image above, our button is in an ID, which makes it pretty easy to target and click on it. The only line you need is the following:

driver.findElementByID("L2AGLb").Click

We have 5 elements in our list we want to search for. We need to take them one by one, add them in the search bar of Google and click the search button or hit ENTER. So let’s remove the Stop and start adding the code required for doing this (PS: we’re going to use the same principles as in my post regarding how to create a simple vba macro).

'Setting initial workbook (the one with the data)
Set wb = ThisWorkbook
'Setting initial worksheet
Set wsO = wb.Worksheets("Sheet1")
‘Getting the row with last thing to search from column A so we can create a range based on it
LastThingToSearch = wsO.Range(“A” & wsO.Rows.Count).End(xlUp).Row
‘Setting the range
Set rangeToSearch = wsO.Range(“A2:A” & LastThingToSearch)
‘Getting each element in our range
For Each elem in rangeToSearch
	‘Targeting the search bar…
‘Going to the next element
Next elem

Targeting the search bar by name

Since there might be multiple names that are the same, we need one more differentiator to make sure we target the required element, and in this case we’re going to take the title, which in this case it is equal with “Search” (it can be seen in the second image of this page).

Set allQNames = driver.FindElementsByName(“q”)
For Each singleQ In allQNames
    ‘If the Title matches what we want
    If singleQ.Attribute("title") = "Search" Then
    ‘We can do whatever we want with it, in this case, we’re putting it into a variable so we can add the text later
        Set searchBar = singleQ
       ‘Exiting the loop regarding q elements
        Exit For
    End If
Next singleQ 

Targeting the search bar by Tag

We’re going to use the exact same procedure as above because a tag can appear multiple times ‘in a webpage, therefore we need to identify the one we need. Our Tag here is Input.

Set allTags = driver.FindElementsByTag(“input”)
For Each singleTag in allTags
	If singleTag.Attribute(“title”) = “Search” Then
		Set searchBar = singleTag
		Exit For
	End If
Next singleTag

Targeting the search bar by Class

If you notice in the Image 2 above, our element actually has two classes (you can tell there are two because they are separated with a space). We’re going to take the first class as a loop assignment and then use the same Title as a separator (I’m taking the first class <gLFyf> because the second class <gsfi> can also be seen in the div element above our input element, therefore the first class is probably less used than the second class).

Set allReqClasses = driver.FindElementsByClass(“gLFyf”)
For Each singleC in allReqClasses
	If singleC.Attribute(“title”) = “Search” Then
		Set searchBar = singleC
		Exit For
	End If
Next singleC

Targeting the search bar by XPath

To find the XPath of a particular element, go on the code representation of that element, right-click on it, then click on Copy and then click on Copy XPath.

Image 5

As I was mentioning in the theoretical part, to target the XPath we don’t need a loop when targeting one element, so we’re going to use a one-liner:

Set searchBar = driver.FindElementByXPath(“/html/body/div[1]/div[3]/form/div[1]/div[1]/div[1]/div/div[2]/input”)

Until now, the XPath seems the easiest way to target an element, don’t you think? It is, but if there are any additions or subtractions of elements above our targeted element, the XPath won’t work because the XPath mentioned in this tutorial relays on the exact order of the elements.

Also, if you’re wondering why there are so many ways to target an element, it is because we need to find the fastest and most accurate way to do it. For example, if the element you’re searching for is in a <div> tag, it’s going to take lots of time to successfully target it because a website can have hundreds of divs. But that div might have a name or a class attached to it, so instead of going after divs, go after that class or names because there might be a smaller number of them.

Okay, now choose one way of targeting the search bar and let’s go further (I’m going to choose the XPath for the simplicity of code).

‘Getting each element in our range
For Each elem in rangeToSearch
        ‘Going to Google for every element
         driver.Get "http://www.google.com"
	‘Targeting the search bar…
         Set searchBar = driver.FindElementByXPath(“/html/body/div[1]/div[3]/form/div[1]/div[1]/div[1]/div/div[2]/input”)
        ‘We need to put our text into the search bar. This is done with <SendKeys>
        searchBar.SendKeys(elem)

        ‘Now we need to hit ENTER or click the search button. To Hit ENTER we need to add a new ‘variable named Keys at the beginning of our code, right below setting the driver (Set Keys = ‘New Selenium.Keys). To hit ENTER we just need to use the same SendKeys function together ‘with the ENTER part of Keys, like below:

         searchBar.SendKeys (Keys.Enter)

‘Going to the next element
Next elem

Until now we managed to perform one search. Next step is to get the first result and after that, returning it in excel.

After searching a little bit, I noticed that all results are located in the <rso> ID. Also, it seems that both the links and the names of the websites from our results are located in <a> tags. So basically, we need to target the first <a> in the <rso> ID and fetch the text of the H3 element within (which holds the name) and the href attribute, which holds the link.

Set firstResult = driver.FindElementById("rso").FindElementByTag("a")
firstName = firstResult.FindElementByTag("h3").Text
firstLink = firstResult.Attribute("href")

Now all we need is to initialize a row and increment it in the main loop so we can use it to return it into excel.

r = 2
For Each elem In rangeToSearch
    driver.Get "http://www.google.com"
    Set searchBar = driver.FindElementByXPath("/html/body/div[1]/div[3]/form/div[1]/div[1]/div[1]/div/div[2]/input")
    searchBar.SendKeys (elem)
    searchBar.SendKeys (Keys.Enter)   
    Set firstResult = driver.FindElementById("rso").FindElementByTag("a")
    firstName = firstResult.FindElementByTag("h3").Text
    firstLink = firstResult.Attribute("href")
    wsO.Range("B" & r) = firstName
    wsO.Range("C" & r) = firstLink
    r = r + 1
Next elem

We can add a message box in the end so the robot tells us when it has finished running. Oh, and don’t forget to close the driver.

driver.Quit
MsgBox “Finished running!”

That’s it! Thank you for reading. Full code below!

Next -> VBA Tutorial P4 – How to launch an app with VBA?

Other posts in this series -> VBA Tutorial for Beginners


PS: If you want to support this website, you can do it through the Show Your Support page!


Sub launch_selenium()

Set driver = New WebDriver
Set Keys = New Selenium.Keys
driver.Start "Chrome"
driver.Get "http://www.google.com"
driver.Timeouts.ImplicitWait = 5000 ' 5 seconds
driver.FindElementById("L2AGLb").Click

'Setting initial workbook (the one with the data)
Set wb = ThisWorkbook
'Setting initial worksheet
Set wsO = wb.Worksheets("Sheet1")
'Getting the row with last thing to search from column A so we can create a range based on it
LastThingToSearch = wsO.Range("A" & wsO.Rows.Count).End(xlUp).Row
'Setting the range
Set rangeToSearch = wsO.Range("A2:A" & LastThingToSearch)
'Getting each element in our range

r = 2

For Each elem In rangeToSearch

    driver.Get "http://www.google.com"
    
'    'Targeting the search bar by name
'
'    Set allQNames = driver.FindElementsByName("q")
'
'    For Each singleQ In allQNames
'
'        If singleQ.Attribute("title") = "Search" Then
'
'            Set searchBar = singleQ
'
'            Exit For
'
'        End If
'
'    Next singleQ
    
'    'Targeting the search bar by Tag
'    Set allTags = driver.FindElementsByTag("input")
'
'    For Each singleTag In allTags
'
'        If singleTag.Attribute("title") = "Search" Then
'
'            Set searchBar = singleTag
'
'            Exit For
'
'        End If
'
'    Next singleTag
    
'    'Targeting the search bar by Class
'    Set allReqClasses = driver.FindElementsByClass("gLFyf")
'
'    For Each singleC In allReqClasses
'
'        If singleC.Attribute("title") = "Search" Then
'
'            Set searchBar = singleC
'
'            Exit For
'
'        End If
'
'    Next singleC

    'Targeting the search bar by XPath
    Set searchBar = driver.FindElementByXPath("/html/body/div[1]/div[3]/form/div[1]/div[1]/div[1]/div/div[2]/input")

    searchBar.SendKeys (elem)
    searchBar.SendKeys (Keys.Enter)
   
 
    Set firstResult = driver.FindElementById("rso").FindElementByTag("a")
    firstName = firstResult.FindElementByTag("h3").Text
    firstLink = firstResult.Attribute("href")

    wsO.Range("B" & r) = firstName
    wsO.Range("C" & r) = firstLink

    r = r + 1

Next elem

driver.Quit

MsgBox "Finished running!"

End Sub

Leave a Reply