Upload File to Web Page Using Vba

Microsoft Net 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.

Download 2021-xi.cipher

Introduction

Internet Explorer classic (IE in the post-obit) was based on ActiveX technology. It was very piece of cake to automate IE for tasks like Webscraping or testing from OLE-aware programming languages similar VBA. But Microsoft volition stop support for IE in the near futurity and wants users to move to newer browsers like Microsoft Border.

Microsoft Border is no longer based on ActiveX technology. Microsoft seems uninterested in creating a drib-in replacement for the IE OLE Object. In that location are libraries that endeavour to fill up this gap using Selenium, see Seleniumbasic as an example. But this requires the installation of a Webdriver, which might not be viable in some environments. The following solution needs no boosted software, autonomously from a Chrome-based browser.

Go on in listen, that all running Border procceses must be terminated before running the code. Otherwise the tabs are opened in the currently running procedure, not the 1 that has been started and subsequent communication between VBA and Edge fails.

CDP Protocol

The lawmaking uses the Chrome Devtools Protocol (CDP) to communicate with the browser. A total documentation of the protocol tin be found here. The lawmaking implements only a very narrow prepare of functions:

Basic functions to set the communication channel
Navigation to a url
Evaluate arbitrary JavaScript expressions in the context of a page and return the issue

But these functions should suffice to do bones Webscraping. The primary code is as follows:

                Sub              runedge()                    Dim              objBrowser              Every bit              clsEdge              Set              objBrowser =              New              clsEdge              Call              objBrowser.start                        Telephone call              objBrowser.attach("              ")                        Call              objBrowser.navigate("              https://google.de")              Phone call              objBrowser.waitCompletion                        Call              objBrowser.jsEval("              alarm(""hi"")")                        Call              objBrowser.jsEval("              certificate.getElementsByName(""q"")[0].value=""automate edge vba""")                        Telephone call              objBrowser.jsEval("              document.getElementsByName(""q"")[0].form.submit()")                        Phone call              objBrowser.waitCompletion                         Phone call              objBrowser.jsEval("              certificate.evaluate("".//h3[text()='Automate Chrome / Edge using VBA - CodeProject']"", document).iterateNext().click()")              Call              objBrowser.waitCompletion              Dim              strVotes              As              String                   strVotes = objBrowser.jsEval("              ctl00_RateArticle_VountCountHist.innerText")          MsgBox ("              end! 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 equally 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 ii means: Either Border starts a small-scale Webserver on a specific port or via pipes. The Webserver lacks whatever security features. Any user on the computer has access to the webserver. This may pose no risks on unmarried user computers or dedicated virtual containers. Merely if the process is run on a terminal server with more than than one user, this is non acceptable. That'due south why the lawmaking uses pipes to communicate with Edge.

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

In order to set the fourth and fifth fds, one must use an undocumented characteristic of the Microsoft Visual C Runtime (MSVCRT): If an awarding is compiled with Microsoft C, than i tin can laissez passer the pipes using the lpReserved2 parameter of the STARTUPINFO structure. Run across "Undocumented CreateProcess" for more details (roll down the folio).

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

              Public              Blazon STDIO_BUFFER     number_of_fds              Every bit              Long              crt_flags(0              To              4)              As              Byte              os_handle(0              To              4)              As              LongPtr              End              Type

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

              Public              Blazon 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 (v bytes) and the pointers (20 bytes).

History

viii^th July, 2021: Initial version
18^thBaronial 2021, added support for 64bit Function
3^rdNov 2021, some pocket-sized improvements

License

About the Author


	Germany

No Biography provided

Y'all must Sign In to apply this message lath.

Connected on GitHub

Member 10086467 5-Apr-22 8:02

Member 10086467

five-Apr-22 8:02

Attaching to some other popup window and clicking a button

Member 15506829 5-Apr-22 1:55

Member 15506829

5-Apr-22 ane:55

Hi, Cheers for this instance. I can attach to webpages navigate and select button, but ane of the buttons opens some other secondary popup window and i need to select "OK" on the new window to go on my automation. Is there whatever way i tin attach this new edge window , click button and proceed in my primary browser. Thank you in Advance
Sign In·View Thread

I take to navigate about:blank page in my code. So I need to know, How to pass html content to blank border browser folio

Rekha Prakash 2022 5-Apr-22 1:22

Rekha Prakash 2022

5-Apr-22 1:22

How to pass html content equally variable string into objBrowser.jsEval function

myTextHTML = "<p>test</p>"
Call objBrowser.jsEval("document.body.innerHTML = 'test'") --> This one works fine if nosotros laissez passer value equally hardcode

myTextHTML = "<p>test</p>"
Call objBrowser.jsEval("certificate.body.innerHTML = myTextHTML") --> This one didn't work out when we pass value as variable

Please can you help me how to work this. The reason is that I need to pass html content dynamically through variabe from another function.

Access tag without id and proper name

fabrizio balzarini 2-Apr-22 8:42

fabrizio balzarini

2-April-22 8:42

Well done! The VBA code works very well, both for ById and ByName references, only I can't reach anchor tags with neither name nor id:

with objBrowser.jsEval("document.querySelector('a[onclick=""mj.js(x,y,z);return fake"")[0].click") can't reach object similar: a onclick="mj.js(x,y,z);render imitation">Displayed text

and with objBrowser.jsEval("document.getElementsByClassName('title')[0].value") I tin can't access the text contained in the beginning grade chemical element with that name.

Is my syntax incorrect or these commands can't exist used? (sorry but I'm 64 and not equally set up as in my 20s)
Thank you.

Re: Access tag without id and name

ChrisK23 3-Apr-22 5:49

ChrisK23

3-Apr-22 5:49

You lot may try out commands with the JavaScript programmer console in edge. If it works there information technology should work in VBA, too.
Sign In·View Thread

Re: Access tag without id and name

fabrizio balzarini 4-Apr-22 0:48

fabrizio balzarini

4-Apr-22 0:48

Thank you. Solved. I hadn't used the coffee console even so, and it was near a revelation. I solved it, reaching every single object (unique mySearchedSubString) with document.querySelectorAll ("a[onclick*=\" mySearchedSubString\"]"[0].click () Very good. Superb solution.
Sign In·View Thread

Re: Access tag without id and name

fabrizio balzarini 4-Apr-22 4:08

fabrizio balzarini

4-Apr-22 4:08

Can save page to html file ?

singlag 25-Mar-22 9:23

singlag

25-Mar-22 ix:23

I have some existing excel vba lawmaking use internal webbrowser (IE base ?) to relieve page as a html file, then import to worksheet for other processing.
Now, I desire to re-write the code to use Edge, how to utilise this projection to save page to a html ?

sample existing code : (Form > add WebBrowser > navigate to url > save UserForm1.WebBrowser1.Certificate.body.innerHTML to html file)

DataHTML = UserForm1.WebBrowser1.Document.body.innerHTML
strFileHTML = "D:\temp.html"
Set objHTMLFile = objFSO.OpenTextFile(strFileHTML, 2, True, TriStateTrue)
objHTMLFile.WriteLine (DataHTML)
objHTMLFile.Close

And tin can I utilize this project to employ Edge at Class > Webbrowser ?

modified 25-Mar-22 xviii:43pm.

Re: Tin save page to html file ?

fabrizio balzarini 2-Apr-22 22:51

fabrizio balzarini

2-Apr-22 22:51

Dim objBrowser As clsEdge
Prepare objBrowser = New clsEdge
Dim myTextHTML equally string
Call objBrowser.start
Call objBrowser.attach("")
Call objBrowser.navigate("https://world wide web.ebay.com/")
Phone call objBrowser.waitCompletion
myTextHTML = objBrowser.jsEval("document.body.innerHTML")

and in myTextHTML there is all HTMML text in Edge selected folio

Re: Can save page to html file ?

fabrizio balzarini 2-Apr-22 22:55

fabrizio balzarini

2-Apr-22 22:55

and, using your code: DataHTML = myTextHTML strFileHTML = "D:\temp.html" Set up objHTMLFile = objFSO.OpenTextFile(strFileHTML, 2, True, TriStateTrue) objHTMLFile.WriteLine (DataHTML) objHTMLFile.Close
Sign In·View Thread

Re: Can save page to html file ?

singlag three-Apr-22 vii:14

singlag

three-Apr-22 7:14

How to handle switch frame in web folio?

Pandiamca 23-Mar-22 19:08

Pandiamca

23-Mar-22 nineteen:08

Demand to switch frames for link frame, button frame & content frame, how to handle information technology, please suggest solution. Thank yous
Sign In·View Thread

Would like to know if I tin can switch the objBrowser to a new tab ?

Fellow member 13891660 21-Mar-22 21:51

Fellow member 13891660

21-Mar-22 21:51

It works perfectly for me. Just would like to know if it is possible to switch the browser object to a new tab. My lawmaking will click on a link in main session and a new tab will be opened. I want to get some text in the new tab. Close the new tab and go on with the main session. Is information technology possible? Many thanks in advance! modified 22-Mar-22 3:57am.
Sign In·View Thread

Re: Would like to know if I tin switch the objBrowser to a new tab ?

Member 13891660 22-Mar-22 20:49

Member 13891660

22-Mar-22 xx:49

After studying the protocol, nosotros can bodily employ Target.getTargets and attach the new target and obtain the new session ID. The rest is elementary.
Sign In·View Thread

Re: Would like to know if I tin switch the objBrowser to a new tab ?

Member 15506829 5-Apr-22 1:58

Member 15506829

5-Apr-22 i:58

Opening New Browser when Instance already exists

Member 15560675 21-Mar-22 6:07

Member 15560675

21-Mar-22 6:07

Code works groovy if in that location are no open up browsers running in the background. Withal, I was wondering if anyone has figured out a style to open a NEW browser instance via the code when Edge is already open. Not looking to control the browser that was already opened, merely the new one that is beingness automated past the script.
Sign In·View Thread

Re: Opening New Browser when Instance already exists

ChrisK23 21-Mar-22 10:48

ChrisK23

21-Mar-22 10:48

I think using the command line switch --user-data-dir=<path> may do the trick
Sign In·View Thread

My vote of v

DJDair eighteen-Mar-22 20:10

DJDair

18-Mar-22 xx:10

SOLVED: "Error PeekNamedPipe in readProcCDP Pin"

Aaron "Aari" Seefeldt eighteen-Mar-22 0:23

Aaron "Aari" Seefeldt

18-Mar-22 0:23

Hello all,

for a long time I sat in front of the problem. Here is my workaround:

1) I used the CODE (http://ww3.cad.de/foren/ubb/Forum226/HTML/000794.shtml) and extended it.

Option Explicit                                            '                                            http://ww3.cad.de/foren/ubb/Forum226/HTML/000794.shtml  Private Declare Part CreateToolhelpSnapshot Lib "kernel32" Alias "CreateToolhelp32Snapshot" ( _     ByVal lFlgas As Long, _     ByVal lProcessID Every bit Long) Every bit Long Individual Declare Part TerminateProcess Lib "kernel32" ( _     ByVal hProcess Equally Long, _     ByVal uExitCode As Long) As Long Private Declare Part OpenProcess Lib "kernel32" ( _     ByVal dwDesiredAccess As Long, _     ByVal bInheritHandle As Long, _     ByVal dwProcessId Every bit Long) As Long Private Declare Function ProcessFirst Lib "kernel32" Allonym "Process32First" ( _     ByVal hSnapshot As Long, _     ByRef uProcess Equally PROCESSENTRY32) As Long Private Declare Function ProcessNext Lib "kernel32" Alias "Process32Next" ( _     ByVal hSnapshot Equally Long, _     ByRef uProcess Every bit PROCESSENTRY32) As Long Individual Declare Role CloseHandle Lib "kernel32" ( _     ByVal hObject As Long) As Long  Individual Type PROCESSENTRY32     dwSize Every bit Long     cntUsage As Long     th32ProcessID As Long     th32DefaultHeapID As Long     th32ModuleID As Long     cntThreads Equally Long     th32ParentProcessID As Long     pcPriClassBase As Long     dwflags As Long     szexeFile As Cord * 260 Terminate Type  Private Const PROCESS_TERMINATE = &H1 Private Const GW_HWNDNEXT As Long = 2  Public Sub KillEXE(ByVal strEXEName Every bit Cord)     Dim lngHandle As Long, retVal Equally Long, hTask As Long, lResult As Long     Dim pe32current As PROCESSENTRY32     lngHandle = CreateToolhelpSnapshot(2&, 0&)     If lngHandle <> 0 Then         pe32current.dwSize = Len(pe32current)         retVal = ProcessFirst(lngHandle, pe32current)         Exercise While Not (retVal = 0)             If InStr(1, pe32current.szexeFile, strEXEName, vbTextCompare) > 0 And so                 hTask = OpenProcess(PROCESS_TERMINATE, 0&, pe32current.th32ProcessID)                 lResult = TerminateProcess(hTask, 1&)                 lResult = CloseHandle(hTask)                 Get out Sub             Terminate If             retVal = ProcessNext(lngHandle, pe32current)         Loop         CloseHandle lngHandle     End If End Sub  Public Function IsEXErunning(ByVal strEXEName As String)     Dim lngHandle Equally Long, retVal Equally Long, hTask As Long, lResult As Long     Dim pe32current As PROCESSENTRY32     lngHandle = CreateToolhelpSnapshot(two&, 0&)     If lngHandle <> 0 Then         pe32current.dwSize = Len(pe32current)         retVal = ProcessFirst(lngHandle, pe32current)         Practise While Not (retVal = 0)             If InStr(i, pe32current.szexeFile, strEXEName, vbTextCompare) > 0 Then                 hTask = OpenProcess(PROCESS_TERMINATE, 0&, pe32current.th32ProcessID)                 'lResult = TerminateProcess(hTask, 1&)                 lResult = CloseHandle(hTask)                 IsEXErunning = True                 Exit Function             Cease If             retVal = ProcessNext(lngHandle, pe32current)         Loop         CloseHandle lngHandle     Terminate If End Part  Public Sub test() Dim Response As VbMsgBoxResult     If IsEXErunning("                                            msedge.exe") Then         Response = MsgBox("                                            Achtung, MS Border läuft bereits."                                            & vbCrLf & vbCrLf &                                            "                                            Soll der Browser jetzt geschlossen werden?", vbYesNo)         If Response = vbNo And then             Leave Sub         Else             Telephone call KillEXE("                                            msedge.exe")         Stop If     Terminate If Finish Sub

2) And then earlier the actual phone call: "Fix objBrowser = New clsEdge" we make another query if EDGE is already running:

Dim Response Equally VbMsgBoxResult     If IsEXErunning("                                            msedge.exe") Then         Response = MsgBox("                                            Achtung, MS Border läuft bereits."                                            & vbCrLf & vbCrLf &                                            "                                            Soll der Browser jetzt geschlossen werden?", vbYesNo)         If Response = vbNo Then             Exit Role         Else             Phone call KillEXE("                                            msedge.exe")         End If     Terminate If                                            '                                            Start Browser     Set objBrowser = New clsEdge

Re: SOLVED: "Error PeekNamedPipe in readProcCDP Pivot"

Member 15595175 nine-Apr-22 16:35

Fellow member 15595175

9-April-22 16:35

Would you please teach me how to use these codes? I notwithstanding have "Mistake PeekNamedPipe in readProcCDP Pin".
Sign In·View Thread

Error PeekNamedPipe in readProcCDP

panwauu 3-Mar-22 10:09

panwauu

3-Mar-22 10:09

I included the files and pasted the sample code. I as well added the reference to the Scripting for Dictionary. When I try to run the sample code I get an error.

Edge is opened without trouble simply than the execution of

Quote:
Call objBrowser.adhere("")

leads to the error

Quote:
Runetime: 901; Error PeekNamedPipe in readProcCDP

Re: Error PeekNamedPipe in readProcCDP

ryanpelor 5-Mar-22 3:15

ryanpelor

5-Mar-22 3:fifteen

you should close the entire edge that already opened then you lot can run the code
Sign In·View Thread

Re: Mistake PeekNamedPipe in readProcCDP

Finnagain 14-Mar-22 23:39

Finnagain

14-Mar-22 23:39

I'g having the same issue. And it occurs even if no edge is ever started!
Sign In·View Thread

Re: Error PeekNamedPipe in readProcCDP

Member 11962071 22-Mar-22 three:forty

Member 11962071

22-Mar-22 3:40

Re: Mistake PeekNamedPipe in readProcCDP

Member 15554076 24-Mar-22 3:03

Member 15554076

24-Mar-22 3:03

I've the same fault - and idea information technology was because I was using office 32-bit, so upgraded to role 64-bit, but the error persists. The Excel VBA codes opens a new Edge window, only then fails with:

Run-fourth dimension                                            '                                            901': Error PeekNamedPipe                                            in                                            readProcCDP