Click here to Skip to main content
15,180,707 members
Articles / Productivity Apps and Services / Microsoft Office
Tip/Trick
Posted 10 Jul 2021

Tagged as

Stats

26.2K views
1.5K downloads
19 bookmarked

Automate Chrome / Edge using VBA

Rate me:
Please Sign up or sign in to vote.
5.00/5 (7 votes)
3 Nov 2021CPOL3 min read
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)

Share

About the Author

ChrisK23
Germany Germany
No Biography provided

Comments and Discussions

 
QuestionOther commands for finding by id, class, tagname.. Pin
Member 1550682922-Jan-22 7:45
MemberMember 1550682922-Jan-22 7:45 
AnswerRe: Other commands for finding by id, class, tagname.. Pin
ChrisK2322-Jan-22 9:08
MemberChrisK2322-Jan-22 9:08 
GeneralRe: Other commands for finding by id, class, tagname.. Pin
Member 1550682923-Jan-22 19:54
MemberMember 1550682923-Jan-22 19:54 
GeneralRe: Other commands for finding by id, class, tagname.. Pin
Sim_9924-Jan-22 11:32
MemberSim_9924-Jan-22 11:32 
QuestionEdge window handle Pin
Michał Kajszczak18-Jan-22 0:13
MemberMichał Kajszczak18-Jan-22 0:13 
AnswerRe: Edge window handle Pin
Sim_9918-Jan-22 1:47
MemberSim_9918-Jan-22 1:47 
QuestionAny possibility to handle a file download? Pin
Sim_9917-Jan-22 10:48
MemberSim_9917-Jan-22 10:48 
AnswerRe: Any possibility to handle a file download? Pin
Sim_9925-Jan-22 1:50
MemberSim_9925-Jan-22 1:50 
QuestionDeserialize with multiple windows Pin
HimaJinJP13-Jan-22 15:13
MemberHimaJinJP13-Jan-22 15:13 
Questionwithout debugging mode Pin
Saravanan Ashok6-Jan-22 22:46
MemberSaravanan Ashok6-Jan-22 22:46 
AnswerRe: without debugging mode Pin
HimaJinJP13-Jan-22 4:24
MemberHimaJinJP13-Jan-22 4:24 
Questiondeserialize for several times Pin
HimaJinJP21-Dec-21 15:23
MemberHimaJinJP21-Dec-21 15:23 
AnswerRe: deserialize for several times Pin
HimaJinJP28-Dec-21 14:39
MemberHimaJinJP28-Dec-21 14:39 
GeneralRe: deserialize for several times Pin
Uriel Wong3-Jan-22 4:20
MemberUriel Wong3-Jan-22 4:20 
GeneralRe: deserialize for several times Pin
HimaJinJP5-Jan-22 11:10
MemberHimaJinJP5-Jan-22 11:10 
GeneralRe: deserialize for several times Pin
Uriel Wong5-Jan-22 11:12
MemberUriel Wong5-Jan-22 11:12 
GeneralRe: deserialize for several times Pin
HimaJinJP5-Jan-22 23:13
MemberHimaJinJP5-Jan-22 23:13 
GeneralRe: deserialize for several times Pin
Uriel Wong6-Jan-22 4:08
MemberUriel Wong6-Jan-22 4:08 
GeneralRe: deserialize for several times Pin
HimaJinJP6-Jan-22 17:50
MemberHimaJinJP6-Jan-22 17:50 
GeneralRe: deserialize for several times Pin
Uriel Wong6-Jan-22 18:48
MemberUriel Wong6-Jan-22 18:48 
GeneralRe: deserialize for several times Pin
HimaJinJP11-Jan-22 18:01
MemberHimaJinJP11-Jan-22 18:01 
QuestionRuntime.enable Timeout on Secured Workstation Pin
Uriel Wong19-Dec-21 4:58
MemberUriel Wong19-Dec-21 4:58 
QuestionExcel VBA? Pin
Conrad Black18-Dec-21 5:24
MemberConrad Black18-Dec-21 5:24 
AnswerRe: Excel VBA? Pin
ChrisK2319-Dec-21 19:34
MemberChrisK2319-Dec-21 19:34 
QuestionHow can we know whether 'jsEval' has succeeded or failed? Pin
HimaJinJP17-Dec-21 5:39
MemberHimaJinJP17-Dec-21 5:39 

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.