How to automate data entry with Chrome using Excel macros and Selenium. Watch the video below:
Here’s the complete VBA macro code:
Option Explicit
Dim driver As New WebDriver
Sub Test()
Dim keys As New SeleniumWrapper.keys
driver.Start “chrome”
driver.Get “https://ssg2021.in/CitizenFeedback\”
driver.Window.Maximize
driver.Wait 1000
driver.FindElementById(“State”).Click
driver.FindElementById(“State”).SendKeys Sheet1.Cells(2, 1).Value
driver.Wait 1000
driver.FindElementById(“District”).Click
driver.FindElementById(“District”).SendKeys Sheet1.Cells(2, 2).Value
driver.Wait 1000
driver.SendKeys (keys.Enter)
driver.FindElementByName(“RespondentAge”).SendKeys Sheet1.Cells(2, 3).Value
driver.Wait 1000
driver.FindElementByName(“RespondentName”).SendKeys Sheet1.Cells(2, 4).Value
driver.Wait 1000
driver.FindElementByName(“RespondentMobileNo”).SendKeys Sheet1.Cells(2, 5).Value
driver.Wait 1000
driver.FindElementByName(“RespondentGender”).Click
driver.Wait 1000
driver.FindElementByName(“RespondentGender”).SendKeys Sheet1.Cells(2, 6).Value
driver.Wait 1000
driver.FindElementByClass(“btn-primary”).Click
‘driver.SendKeys (keys.Enter)
driver.FindElementByName(“FQ1”).Click
driver.FindElementByName(“FQ1”).SendKeys Sheet1.Cells(2, 7).Value
driver.Wait 1000
driver.FindElementByName(“FQ2”).Click
driver.FindElementByName(“FQ2”).SendKeys Sheet1.Cells(2, 8).Value
driver.Wait 1000
driver.FindElementByName(“FQ3”).Click
driver.FindElementByName(“FQ3”).SendKeys Sheet1.Cells(2, 9).Value
driver.Wait 1000
driver.FindElementByName(“FQ4”).Click
driver.FindElementByName(“FQ4”).SendKeys Sheet1.Cells(2, 10).Value
driver.Wait 1000
driver.FindElementByName(“FQ5”).Click
driver.FindElementByName(“FQ5”).SendKeys Sheet1.Cells(2, 11).Value
driver.Wait 1000
driver.SendKeys (keys.Enter)
End Sub
In the last video we learnt how to install Selenium and the corresponding correct Chrome version. We also learnt how to download and install the Selenium Wrapper software.
The first thing we have to do is to study the elements of the web page where we will enter the data. For this we have to study the HTML code of the elements to find their name, ID or class to be able to access them correctly. In this special case, we have to access combo-boxes, text boxes, option buttons, command buttons etc. Let’s take the example of the combo-box captioned as State:
We note that the id of the combo-box is “State”. Therefore we use the following line of code to access it:
driver.FindElementById(“State”).Click
Next we fill this element with an appropriate value from our Excel worksheet. This data has been collected during a field survey of the district in the State
driver.FindElementById(“State”).SendKeys Sheet1.Cells(2, 1).Value
In a similar manner we study each element carefully and access it using the appropriate element property as shown in the video. Because data entry into the web form and the web page loading may take some time based on the speed of our internet connection, we use appropriate ‘wait’ times to take care of such factors.
Further Reading: