Automating Edge IE Mode using VBA (without third-party software)-CodePudding

I'm creating a post as I've been researching this topic and have found some contradicting answers surrounding the ability to automate Edge IE Mode with VBA without third-party applications (see solutions below).

I currently have an app that accomplishes the following in IE:

Verifies whether or not an instance of IE is already open and navigated to a specific URL;
Creates a new IE instance if the above isn't true;
Scrapes data available from the HTML doc; and
Clicks elements to navigate to other tabs and pages.

Leading up to the deprecation of IE, I'm unsure how to direct my code to specifically communicate with Edge IE mode instead of it attempting to create a brand new instance of Internet Explorer. Does anyone possibly have specific examples that I could reference?

Response 1: https://stackoverflow.com/a/71994505/9791828

In that thread, one of the responses indicates that this automation is possible.

As of 25th April 2022, you can now directly automate Edge IE Mode with VBA without any additional third party-software. The below guidance has been well tested by me and my colleagues after obtaining it from exchanging with our partnered Microsoft Support team.

What You Need to Do

Your Windows version needs to be at least 20H2. You can check your Windows version with this guide here.

Your Windows needs to have the following KBs installed: KB5011487. Or simply update your Windows and this should be automatically installed as well. You can check your Windows update history with this guide here.

Finally install the below registry keys on your Windows and restart:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Internet Explorer\Main] "NotifyDisableIEOptions"=dword:00000002

[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Internet Explorer\Main\EnterpriseMode] "EnableGlobalWindowListInIEMode"=dword:00000001

Per the MS Support team, the above method should work until 2029. Official documentation on this might be coming soon I believe.

After the above steps, VBA shall be able to interact with Edge IE Mode as if it is an Internet Explorer window. Your current code that automates the InternetExplorer.Application object will work with Edge IE mode as well.

Response 2: https://docs.microsoft.com/en-us/answers/questions/829365/vba-automation-with-edge-ie-mode.html

One of the community experts stated that this isn't possible without Selenium and a driver.

If Selenium is not an option, I think you can't automate with Edge IE mode.

The only way to automate with Edge IE mode is using IEDriver. You can't do it with only VBA. You need to:

Define InternetExplorerOptions with additional properties that point to the Microsoft Edge browser.

Start an instance of InternetExplorerDriver and pass it InternetExplorerOptions. IEDriver launches Microsoft Edge and then loads your web content in IE mode.

For more information, you can refer to this doc.

CodePudding user response：

Unfortunately, it seems to be impossible to implement IE mode automation in the Edge browser using VBA so far. Even with SeleniumBasic, since there's no InternetExplorerOptions class or the like available, you cannot run the automation in the IE mode using VBA.

However, for now you don't have to worry about IE automation after the IE retirement. I tried to disable the IE11 browser following Response 1, and it turned out that VBA code still successfully launched IE browser window. You could expect your VBA code will work after the IE retirement.

For more information of IE retirement, please read this article:

I ran into issues with my application which utilizes IE through automation. Will this be fixed? (Updated: April 14, 2022)

As part of the IE 11 application retirement, certain COM automation scenarios were inadvertently broken. These IE COM objects have been restored to their original functionality as of the Windows 11 November 2021 “C” update and the Windows 10 March 2022 “B” update. The COM scenarios will also continue to work after the IE11 desktop application is disabled after June 15, 2022. If you continue to experience issues after taking the update, please contact App Assure for remediation assistance.

CodePudding user response：

My VBA interrogates the HTML from IE directly; so I have used Selenium with Python to pull the equivalent data from Chrome and save it to a text file which my VBA can then access and interpret. VBA can call Python directly.

Step 1: Install the latest stable Python

Step 2: Install the latest stable PyCharm and read how to use it from the same link

Step 3: Open Chrome. Click on three dots near top right. Click on Settings then About Chrome to see the version of your Chrome. Download the corresponding driver here. Save the driver in the PyCharm PATH folder

Step 4: Create a new project and install the packages selenium and webdriver-manager as explained in the PyCharm link above

Step 5: Create a new Python file vba.py

Step 6: Add this code to a new VBA procedure to ensure that it works for you:

Dim iChannel%
Dim complete$, pythonexe$, script$, sFile_Path$, sURL$, textline$

'set sURL to page sought
sURL = "https://duckduckgo.com/?q=duckduckgo&ia=web"

iChannel = 1
    
'Python Script

script = "" & _
"from selenium import webdriver" & vbCrLf & _
"from selenium.webdriver.chrome.service import Service" & vbCrLf & _
"from webdriver_manager.chrome import ChromeDriverManager" & vbCrLf & _
"import time" & vbCrLf & _
"outputFile = r""" & ActiveWorkbook.path & "\output.txt""" & vbCrLf & _
"completeFile = r""" & ActiveWorkbook.path & "\complete.txt""" & vbCrLf & _
"waitTime = 12" & vbCrLf & _
"driver = webdriver.Chrome(service=Service(ChromeDriverManager().install()))" & vbCrLf & _
"driver.get(""" & sURL & """)" & vbCrLf & _
"driver.delete_all_cookies()" & vbCrLf & _
"driver.maximize_window()" & vbCrLf & _
"driver.implicitly_wait(waitTime)" & vbCrLf & _
"time.sleep(waitTime)" & vbCrLf & _
"pageSource = driver.page_source" & vbCrLf & _
"driver.quit()" & vbCrLf & _
"" & vbCrLf & _
"with open(outputFile, 'w', encoding='utf-8') as f:" & vbCrLf & _
"    f.write(pageSource)" & vbCrLf & _
"f.close()" & vbCrLf & _
"" & vbCrLf & _
"with open(completeFile, 'w') as f2:" & vbCrLf & _
"    f2.write('complete')" & vbCrLf & _
"f2.close()"

'Write the Script into a python file
sFile_Path = "C:\Users\user\PycharmProjects\VbaProject1\Main\vba.py" 'replace with location of your vba.py file
Close #iChannel
Open sFile_Path For Output As #iChannel
Print #iChannel, script
'closing channel forces complete write of script to python file
Close #iChannel

'update indicator of python run completion
Close #iChannel
Open ActiveWorkbook.path & "\complete.txt" For Output As #iChannel
Print #iChannel, "incomplete"
Close #iChannel

pythonexe = "C:\Users\user\PycharmProjects\VbaProject1\venv\Scripts\python.exe"

'Execute Script.py
Call Shell(pythonexe & " " & sFile_Path)

'Wait for python run to complete
Application.Wait (Now   TimeValue("0:00:20"))

complete = "incomplete"
Do While complete = "incomplete"
    'Continue to wait for python run to complete
    Application.Wait (Now   TimeValue("0:00:10"))
    
    'Python run finishes by updating this file with 'complete'
    ' so check whether run complete
    Open ActiveWorkbook.path & "\complete.txt" For Input As #iChannel
    Line Input #iChannel, complete
    
    Close #iChannel
Loop

'Retrieve HTML stored by python
Open ActiveWorkbook.path & sOutputFile For Input As #iChannel
sHTML = ""
Do Until EOF(iChannel)
    Line Input #iChannel, textline
    sHTML = sHTML & textline
Loop

Python code inserted by VBA into vba.py above with additional comments:

from selenium import webdriver
from selenium.webdriver.chrome.service import Service
from webdriver_manager.chrome import ChromeDriverManager
import time
outputFile = r"C:\Users\user\Documents\HP Laptop\Documents\Documents\Jobs\DIT\IDMB\Output.txt"
completeFile = r"C:\Users\user\Documents\HP Laptop\Documents\Documents\Jobs\DIT\IDMB\complete.txt"
waitTime = 12
# point driver to Chrome driver previously saved in PATH folder - Step 3
driver = webdriver.Chrome(service=Service(ChromeDriverManager().install()))
# request HTML page
driver.get("https://duckduckgo.com/?q=duckduckgo&ia=web")
driver.delete_all_cookies()
driver.maximize_window()
# wait for Chrome to fully load page
driver.implicitly_wait(waitTime)
time.sleep(waitTime)
# HTML
pageSource = driver.page_source
# close browser
driver.quit()

# save HTML for subsequent dissection by VBA
with open(outputFile, 'w', encoding='utf-8') as f:
    f.write(pageSource)
f.close()

# mark run as complete 
with open(completeFile, 'w') as f2:
    f2.write('complete')
f2.close()