Chrome browser operation from VBA using Selenium (VBA edition) | Sites to improve IT skills/Everything about websites and programs [HTML, CSS, Javascript, PHP, VBA, python]

Here we will explain a VBA program that uses Selenium to operate the chrome browser from VBA. *Automated processing sends traffic to the connection destination. Please be careful when using this, as excessive access may result in abnormal access. First, install chrome and selenium if they are not installed.

Chrome browser operation from VBA using Selenium (installation version)

Now, let’s take a look at some commonly used techniques in VBA.

Browser settings, startup and termination
Retrieve by specifying attributes and selectors
Check if element exists
Fill out forms or send keyboard operations
Open by specifying screen size
take screenshot
How to prevent automatic login from Twitter etc.
Refresh your browser by pressing F5

Browser settings, startup and termination

sub test()
	Dim driver As New Selenium.ChromeDriver 'Browser settings
	driver.Start "chrome" 'start chrome
	driver.Get "https://www.google.com/?hl=ja" 'Open by specifying URL
	driver.Close 'close chrome
	Set driver = Nothing 'reset drive
end sub

This is a program that opens a basic browser, accesses a URL, and closes it.

“driver” is the browser object, so we will perform various processing on it.

The final reset leaves the variable empty (unset).

When exiting, “.Close” is used, but “.quit” can also be used.

.quit is

Close all associated windows using this driver. Same as stop.

.close is

close current window

In addition, how to set up the browser screen.

sub test() 
 Dim driver As New Selenium.ChromeDriver 'Browser settings 
 driver.Start "chrome" 'start chrome

 'If you do not want to display it on the screen, uncomment it.
 'driver.AddArgument "headless"
 'driver.AddArgument "disable-gpu"

 'Screen size
 'driver.AddArgument "window-size=200,400"

 driver.Get "https://www.google.com/?hl=ja" 'Open by specifying URL 
 driver.Close 'close chrome 
 Set driver = Nothing 'reset drive
end sub

Retrieve by specifying attributes and selectors

sub test()
	Dim driver As New Selenium.ChromeDriver 'Browser settings
	driver.Start "chrome" 'start chrome
	driver.Get "https://www.google.com/?hl=ja" 'Open by specifying URL
	Set objIdNameid = driver.FindElementById("id")
	Set objIdNameclass = driver.FindElementsByClass("class").Item("num（０～）")
	Set objIdNamename = driver.FindElementsByName("name").Item("num（１～）")
	Set objIdNamesele = driver.FindElementByCss("selecter")
	driver.Close 'close chrome
	Set driver = Nothing 'reset drive
end sub

“.FindElement~” retrieves the specified item in various ways.

The css selector is written as “#id > div > div > div.class-1” and can be checked using the browser’s debug function.

Check if element exists

sub test()
	Dim driver As New Selenium.ChromeDriver 'Browser settings
	Dim By As New By
	driver.Start "chrome" 'start chrome
	driver.Get "https://www.google.com/?hl=ja" 'Open by specifying URL
	id_judge = driver.IsElementPresent(By.Css(IdNamename))
	if id_judge = True then
		Debug.Print "exists"
	end if
	driver.Close 'close chrome
	Set driver = Nothing 'reset drive
end sub

“IsElementPresent” returns a Boolean (True, False) indicating whether the element exists.

When processing an element, an error will occur if it does not exist, so this is useful for error handling.

You can also use loop processing to wait for a response.

Chromedrive basically waits for the page response, but it does not wait for elements that appear in Javascript etc.

In such cases, you will need to add your own wait processing.

#If VBA7 Then
	Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal ms As LongPtr)
#Else
	Private Declare Sub Sleep Lib "kernel32" (ByVal ms As Long)
#End If
 
sub test()
	id_judge = driver.IsElementPresent(By.Css(IdNamename))
	HT_loop_num = 0
	Do While id_judge = False
		If HT_loop_num > 6 Then
			"error handling"
		End If
		HT_loop_num = HT_loop_num + 1
		Sleep 5000
		DoEvents
		id_judge = driver.IsElementPresent(By.Css(IdNamename))
	Loop
end sub

At the beginning is a declaration for using sleep.

If you do the above, it will loop 6 times until the element appears, and if it does not, it will process an error.

Fill out forms or send keyboard operations

sub test()
	Dim driver As New Selenium.ChromeDriver 'Browser settings
	Dim Keys As New Selenium.Keys 'Use sendkey to send the key to the browser
	driver.Start "chrome" 'start chrome
	driver.Get "https://www.google.com/?hl=ja" 'Open by specifying URL
	Set objIdNameid = driver.FindElementById("id")
	Set objIdNamesubmit = driver.FindElementById("id(submit)")
	objIdNameid.Clear
	objIdNameid.SendKeys "test"
	objIdNameid.SendKeys Keys.Enter
	objIdNamesubmit.submit
	driver.Close 'close chrome
	Set driver = Nothing 'reset drive
end sub

I specify the text area with the ID attribute, clear it, and then insert the text.

After that, press “Enter” on the keyboard to insert a line break.

Then submit.

In many cases, .click is also sufficient for .submit.

objIdNamesubmit.click

Please note that SendKeys is not suitable for inserting long sentences into forms, etc. This is because it takes time to process.

Please also refer to the method of executing javascript as it is overwhelmingly faster.

If text input is difficult with SendKeysSendKeys

Do not display browser operations on screen

sub test()
	Dim driver As New Selenium.ChromeDriver 'Browser settings
	driver.AddArgument "headless" '! here
	driver.Start "chrome" 'start chrome
	driver.Get "https://www.google.com/?hl=ja" 'Open by specifying URL
	driver.Close 'close chrome
	Set driver = Nothing 'reset drive
end sub

Headless mode means that the process runs in the background without being displayed on the screen.

While processing in Chrome, the browser comes to the front.

Background processing is best if you want it to not interfere with other work.

Also, since the browser is not displayed, the load on the terminal is reduced and the processing speed is faster.

Open by specifying screen size

sub test()
	driver.AddArgument "window-size=1500,1000" '! here
	Dim driver As New Selenium.ChromeDriver 'Browser settings
	driver.Start "chrome" 'start chrome
	driver.Get "https://www.google.com/?hl=ja" 'Open by specifying URL
	source = driver.PageSource
	driver.Close 'close chrome
	Set driver = Nothing 'reset drive
end sub

If you set the screen size specification when taking screenshots, you can obtain a uniform size.

take screenshot

sub test()
	driver.AddArgument "window-size=1500,1000"
	Dim driver As New Selenium.ChromeDriver 'Browser settings
	driver.Start "chrome" 'start chrome
	driver.Get "https://www.google.com/?hl=ja" 'Open by specifying URL
	driver.TakeScreenshot.SaveAs "Destination path and file name"
	driver.Close 'close chrome
	Set driver = Nothing 'reset drive
end sub

Take a screenshot with “.TakeScreenshot” and save it with “.SaveAs”.

The save destination also includes the file name.

This feature can also be used for debugging.

This is useful when you want to check the screen that is actually processing when it is running in the background.

How to prevent automatic login from Twitter etc.

When you access the login page on Twitter, etc., if you have already logged in, your user information may remain in the browser and you may be able to log in without permission.

However, if you are able to log in or not, you need to check your login status, which is quite a hassle.

If you can make sure to log in every time, there will be no need to confirm.

Usually the browser user information is

C:/Users/username/AppData/Local/Google/Chrome/User Data

Since it is stored somewhere, if you change the storage location of the child and delete it every time, you can start from the login screen.

In addition, below, we will create a folder called “tmp” at the same level as the Excel file.

sub test() 
	driver.AddArgument "window-size=1500,1000" 
	Dim driver As New Selenium.ChromeDriver 'Browser settings
	PROFILE_PATH = "tmp"
	driver.AddArgument ("user-data-dir=" & PROFILE_PATH)
	driver.Start "chrome" 'start chrome
	driver.Get "https://www.google.com/?hl=ja" 'Open by specifying URL
	driver.Close 'close chrome 
        Set driver = Nothing 'reset drive
	Dim fso As FileSystemObject
	Set fso = New FileSystemObject
	Dim fl As Folder
	Set fl = fso.GetFolder(PROFILE_PATH) ' get folder
	Dim f As File
	For Each f In fl.Files
	    f.Delete (True) ' delete file
	Next
	Set fso = Nothing
end sub

First, set the folder to store the data.

Once the process is complete, the user data will be deleted.

On the other hand, if you want to automatically log in with an account other than the one you usually use, you can store user data in ChromeDrive and access it until the cache is cleared.

Refresh your browser by pressing F5

There may be times when you want to refresh the screen during processing.

sub test() 
	driver.AddArgument "window-size=1500,1000" 
	Dim driver As New Selenium.ChromeDriver 'Browser settings
	driver.Start "chrome" 'start chrome
	driver.Get "https://www.google.com/?hl=ja" 'Open by specifying URL
	driver.Refresh '! here
	source = driver.PageSource driver.Close 'close chrome
	Set driver = Nothing 'reset drive
end sub

I think it can be used when processing is done with javascript and the information is not updated unless it is reloaded.

Various automation is possible using selenium and vba.

There are various other functions, so please try them out.