Click here to Skip to main content
15,867,771 members
Articles / Productivity Apps and Services / Microsoft Office
Tip/Trick

Automate Chrome / Edge using VBA

Rate me:
Please Sign up or sign in to vote.
4.97/5 (26 votes)
3 Nov 2021CPOL3 min read 237.3K   6.7K   28   241
A method to automate Chrome (based) browsers using VBA
Microsoft Internet Explorer was fully scriptable using OLE Automation. This functionality is no longer available with the new Microsoft Edge browser. This tip presents a way to automate Edge and other Chrome based browsers using only VBA.

Introduction

Internet Explorer classic (IE in the following) was based on ActiveX technology. It was very easy to automate IE for tasks like Webscraping or testing from OLE-aware programming languages like VBA. But Microsoft will end support for IE in the near future and wants users to move to newer browsers like Microsoft Edge.

Microsoft Edge is no longer based on ActiveX technology. Microsoft seems uninterested in creating a drop-in replacement for the IE OLE Object. There are libraries that try to fill this gap using Selenium, see Seleniumbasic as an example. But this requires the installation of a Webdriver, which might not be feasible in some environments. The following solution needs no additional software, apart from a Chrome-based browser.

Keep in mind, that all running Edge procceses must be terminated before running the code. Otherwise the tabs are opened in the currently running process, not the one that has been started and subsequent communication between VBA and Edge fails.

CDP Protocol

The code uses the Chrome Devtools Protocol (CDP) to communicate with the browser. A full documentation of the protocol can be found here. The code implements only a very narrow set of functions:

  1. Basic functions to set up the communication channel
  2. Navigation to a url
  3. Evaluate arbitrary JavaScript expressions in the context of a page and return the result

But these functions should suffice to do basic Webscraping. The main code is as follows:

VB.NET
'This is an example of how to use the classes
Sub runedge()

    'Start Browser
    Dim objBrowser As clsEdge
    Set objBrowser = New clsEdge
    Call objBrowser.start
    
    'Attach to any ("") or a specific page
    Call objBrowser.attach("")
    
    'navigate
    Call objBrowser.navigate("https://google.de")
    
    Call objBrowser.waitCompletion
    
    'evaluate javascript
    Call objBrowser.jsEval("alert(""hi"")")
    
    'fill search form (textbox is named q)
    Call objBrowser.jsEval("document.getElementsByName(""q"")[0].value=""automate edge vba""")
    
    'run search
    Call objBrowser.jsEval("document.getElementsByName(""q"")[0].form.submit()")
    
    'wait till search has finished
    Call objBrowser.waitCompletion
    

    'click on codeproject link
    Call objBrowser.jsEval("document.evaluate("".//h3[text()='Automate Chrome / Edge using VBA - CodeProject']"", document).iterateNext().click()")
    
    Call objBrowser.waitCompletion
    
    Dim strVotes As String
'if a javascript expression evaluates to a plain type it is passed back to VBA
    strVotes = objBrowser.jsEval("ctl00_RateArticle_VountCountHist.innerText")
    
    MsgBox ("finish! Vote count is " & strVotes)
    
    objBrowser.closeBrowser

    
End Sub

The class clsEdge implements the CDP protocol. The CDP protocol is a message-based protocol. Messages are encoded as JSON. To generate and parse JSON, the code uses the VBA-JSON library from here.

Low-Level Communication with Pipes

The low-level access to the CDP protocol is avaible by two means: Either Edge starts a small Webserver on a specific port or via pipes. The Webserver lacks any security features. Any user on the computer has access to the webserver. This may pose no risks on single user computers or dedicated virtual containers. But if the process is run on a terminal server with more than one user, this is not acceptable. That's why the code uses pipes to communicate with Edge.

Edge uses the third file descriptor (fd) for reading messages and the fourth fd for writing messages. Passing fds from a parent process to child process is common under Unix, but not under Windows. The WinApi call to create a child process (CreateProcess) allows to setup pipes for the three common fds (stdin, stdout, stderr) using the STARTUPINFO structure, see CreateProcessA function (processthreadsapi.h) and STARTUPINFOA structure (processthreadsapi.h). Other fds cannot be passed to the child process.

In order to set up the fourth and fifth fds, one must use an undocumented feature of the Microsoft Visual C Runtime (MSVCRT): If an application is compiled with Microsoft C, than one can pass the pipes using the lpReserved2 parameter of the STARTUPINFO structure. See "Undocumented CreateProcess" for more details (scroll down the page).

The structure that can be passed in lpReserved2 is defined in the module modExec.

VB.NET
Public Type STDIO_BUFFER
    number_of_fds As Long
    crt_flags(0 To 4) As Byte
    os_handle(0 To 4) As LongPtr
End Type

The structure is defined to pass five fds in the os_handle array. The values for the crt_flags array can be obtained from https://github.com/libuv/libuv/blob/v1.x/src/win/process-stdio.c. The fields of the struct must lie contiguously in memory (packed). VBA aligns struct fields to 4 byte boundaries (on 32-bit systems). That's why a second struct with raw types is defined.

VB.NET
Public Type STDIO_BUFFER2
    number_of_fds As Long
    raw_bytes(0 To 24) As Byte
End Type

After populating the STDIO_BUFFER struct, the content is copied using MoveMemory to the STDIO_BUFFER2 struct. The size of 25 bytes is enought to hold crt_flags (5 bytes) and the pointers (20 bytes). 

History

  • 8th July, 2021: Initial version
  • 18th August 2021, added support for 64bit Office
  • 3rd November 2021, some minor improvements

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Germany Germany
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionerror on compile! can't get passed this error; User-Defined type not defined Pin
Member 1593208213-Dec-23 6:28
Member 1593208213-Dec-23 6:28 
AnswerRe: error on compile! can't get passed this error; User-Defined type not defined Pin
ChrisK2313-Dec-23 17:22
ChrisK2313-Dec-23 17:22 
QuestionSpeeding innerText Pin
Member 1614871313-Dec-23 4:31
Member 1614871313-Dec-23 4:31 
AnswerRe: Speeding innerText Pin
ChrisK2313-Dec-23 17:25
ChrisK2313-Dec-23 17:25 
QuestionRunning Edge on a specific website I use daily crashes periodically Pin
R H Dec20229-Dec-23 8:57
R H Dec20229-Dec-23 8:57 
Question.Navigate code fails not sure why! Pin
Member 1593208228-Sep-23 12:48
Member 1593208228-Sep-23 12:48 
QuestionEdge process Termination? Pin
madhatt197011-Jul-23 5:50
madhatt197011-Jul-23 5:50 
AnswerRe: Edge process Termination? Pin
FabrizioBalzarini16-Aug-23 21:58
FabrizioBalzarini16-Aug-23 21:58 
GeneralRe: Edge process Termination? Pin
FabrizioBalzarini18-Aug-23 7:08
FabrizioBalzarini18-Aug-23 7:08 
GeneralRe: Edge process Termination? Pin
fabrizio balzarini27-Aug-23 9:03
fabrizio balzarini27-Aug-23 9:03 
GeneralRe: Edge process Termination? Pin
madhatt197030-Aug-23 6:15
madhatt197030-Aug-23 6:15 
QuestionCheck a checkbox Pin
rahmad danis27-May-23 21:27
rahmad danis27-May-23 21:27 
AnswerRe: Check a checkbox Pin
jane chunger30-Jul-23 16:04
jane chunger30-Jul-23 16:04 
QuestionFails when working with the Linkedin site Pin
rahmad danis23-Apr-23 3:03
rahmad danis23-Apr-23 3:03 
AnswerRe: Fails when working with the Linkedin site Pin
Long Vh1-May-23 19:34
Long Vh1-May-23 19:34 
GeneralRe: Fails when working with the Linkedin site Pin
rahmad danis27-May-23 21:31
rahmad danis27-May-23 21:31 
QuestionThis seems to be for VB.NET, not VBA Pin
David Aronson23-Mar-23 18:14
David Aronson23-Mar-23 18:14 
AnswerRe: This seems to be for VB.NET, not VBA Pin
Long Vh1-May-23 19:35
Long Vh1-May-23 19:35 
QuestionChrome: Error PeekNamedPipe in readProcCDP Pin
AtholH5-Mar-23 1:19
AtholH5-Mar-23 1:19 
AnswerRe: Chrome: Error PeekNamedPipe in readProcCDP Pin
Member 1622347319-Mar-24 23:22
Member 1622347319-Mar-24 23:22 
GeneralRe: Chrome: Error PeekNamedPipe in readProcCDP Pin
Member 1622347326-Mar-24 2:37
Member 1622347326-Mar-24 2:37 
Questionquestion about crt_flags value Pin
User-1581200416-Jan-23 7:00
User-1581200416-Jan-23 7:00 
Questionpossible use Pin
Member 1584643128-Nov-22 1:25
Member 1584643128-Nov-22 1:25 
AnswerRe: possible use Pin
R H Dec20224-Dec-22 20:14
R H Dec20224-Dec-22 20:14 
GeneralRe: possible use Pin
Long Vh1-May-23 19:57
Long Vh1-May-23 19:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.