I need to perform some actions in an Windows Edge browser (Win11 Pro 64bit & Edge both latest versions) from Excel VBA.
SeleniumVBA seems a simple and perfect solution for my problem and IF things work, I'm done in a few lines of code.
These are the first few lines of my code:
Code:
Sub SeleniumEdgeExample() Dim driver As New WebDriver
driver.StartEdge
The very first time I run this it works, the next times I get a run-time error "Access Denied" on the "driver.StartEdge" line.
The error survives a reboot.
I found that when I delete the file "msedgedriver.exe" in my download folder, the first time I run this the error is gone but the next times it's back again (and the "msedgedriver.exe" is downloaded again in my Download folder.
This way I can't even get started.
Note that I did set a reference to the Selenium DLL correctly ("SeleniumVBA_win64.dll")
Hmm, I just helped a person on Reddit with Selenium, I'd never used it before then. We used the ChromeDriver instead of Edge (is that a must for you?) I doubt that would change the operation of the code. Anyway, the code basically just does:
Code:
Dim cDriver As chromeDriver
Set cDriver = New chromeDriver
cDriver.Get "YOUR URL HERE"
cDriver.Wait 2000 ' this waits 2 seconds
' from here on you can interact with the loaded page like
cDriver.FindElementById("butAgree").Click ' click the Ok button
' code to loop a table
Dim tblRow As WebElement
Dim tblData As WebElement
For Each tblRow In cDriver.FindElementById("ctl00_cph1_dtlDateList").FindElementsByTag("tr")
For Each tblData In tblRow.FindElementsByTag("td")
' do some stuff with the table data
Next tblData
Next tblRow
' all done
cDriver.Close
Set cDriver = Nothing
edit: copy/paste tblData and not Next tblData and a typo
I don't use chrome and if I change "driver.StartEdge" into "driver.StartChrome" Excel crashes.
In 'your' code, I get "User-defined type not defined" on line "Dim cDriver As chromeDriver". I think that's no longer the right call in the latest version??
Thanks, as indicated in my first post, I do have the refence in place (and the fact that it runs the very first time proves that).
I don't have Chrome so Edge should work (but it doesn't...), the driver is downloaded
Okay, this is what I did. Using the Selenium install I referenced above, then followed the instructions here (the marked answer of where to place the edgedriver.exe.
It works exactly as the chrome driver
Code:
Private Sub btnEdgeBrowser_Click()
Dim eDriver As EdgeDriver
Dim tblRow As WebElement
Dim tblData As WebElement
Dim rfqDateURLs As Scripting.Dictionary
Dim rw As Integer
Set eDriver = New EdgeDriver
eDriver.Get "https://www.dibbs.bsm.dla.mil/RFQ/RfqDates.aspx?category=close"
eDriver.Wait 2000
eDriver.FindElementById("butAgree").Click ' try to click the Ok button
eDriver.Wait 2000
Set rfqDateURLs = New Scripting.Dictionary
' get the table rows, loop through them to retrieve the data
rw = 5
For Each tblRow In eDriver.FindElementById("ctl00_cph1_dtlDateList").FindElementsByTag("tr")
' get each row of data from the web page table
For Each tblData In tblRow.FindElementsByTag("td")
' if the date is equal to or great than the date
' specified on sheet2 add it to the dictionary for processing
If Trim(tblData.Text) <> "" Then
' there is a date in the table cell
pagedate = CDate(tblData.Text)
' 10-1-24 add toDate for a possible date range
If pagedate >= Date Then
Sheet1.Cells(rw, 1).Value = pagedate
rw = rw + 1
DoEvents
rfqDateURLs.Add Format(pagedate, "MM-DD-YYYY"), tblData.FindElementByTag("a").Attribute("href")
End If
End If
Next tblData
Next tblRow
eDriver.Close
Set eDriver = Nothing
End Sub
edit: I forgot to include the link to the post about using Edge with selenium
It seems to me that there are two different versions for Selenium out there:
1 the one I used (and the one that 'belongs' to the Wiki pages I shared in my last link. This version is from 2024 and uses "SeleniumVBA_win64.dll" from july 2024
2 the one you point to which is from 2016 (!), I don't see a 64bit DLL there but there is a "Selenium64.tlb" file
As these tow versions appear so very much different, I'll fully uninstall the 'latest' version (my #1 above) and then re-install the 2016 version (#2)
Update: I performed the steps in the last post and now things seems to work OK. Thanks for the patience and help. Very weird that an actively kept much newer version fails, but I guess it's the end result that counts ;-)
Yes indeed, the wrapper 2.0.9 works quite well and is much easier to use. This helps now and when revisiting the code later because it is a much simpler code base.
If I may 'switch' the topic a bit (still "SeleniumVBA")... As said, I can now run Selenium. My first step was to 'auto-login' and I could indeed programmatically provide my credentials.
My end-goal is to select an option from a dropdown menu on the page and then click a button to download a file. After that I can close the browser.
I was working with elementIDs to provide login information, the drop-down menu however doesn't seem to show an ID. I'm not an expert here in any way, but could this be because this dropdown is done in Jva script or something similar? I tested if I could use keyboard shortcuts but the only thing that works is moving the mouse over the dropdown menu (and only then, without click, it opens).
So how do I use Selenium to deal with this problem?
if you know which <li> has the item you want then you can look select the div it is in and then loop the li
Using the last example, it would look something like this
Code:
Dim listItem as WebElement
Dim listItemCount as Integer
listItemCount = 1
For Each listItem In eDriver.FindElementById("divid").FindElementsByTag("li")
If listItemCount = 5 then
listItem.Click
End If
Next listItem
Ok, the script is more complex so I'll get to this again. But while I was testing with your code, I found that I can jump to the final page (where all I need to do is start the download) by going to a special URL. That URL only works after the login completed however.
So my question now is; after I started a session with a URL and logged-in (which now works), how do I switch to another URL in that same session?
Because if I do: "Driver.Start "edge", "special URL" it doesn't work as a NEW window opens and requires me to log in.
Thanks.
For clarity, you tip worked from a coding perspective. However the window where I was logged in was replaced by a window with the special URL and somehow that triggered again the logon prompt.
Can I open the special URL in a new tab instead?
Are you setting the eDriver object to nothing and then using the .Get method to go to the next URL? You don't need to do that (from what I've done anyway).
If you manually login and then copy and paste the URL you're trying to use in your code, does the browser open a new tab?
Thanks. No, I'm not resetting the driver between calls.
Not sue what you mean with your last suggestion, but if I manually login (so in a 'normal' browser window, not the 'special' one opened by Selenium) and then manually open a new tab and past the special URL there, it works.
Okay, every time you reset the driver it is like opening a new browser window.
What I have done is initiate the driver, then I pass that long the way to whatever code needs to use the browser and then close it when the entire process is finished.
In the project I just helped another person out with. You've seen the first step, where it goes and gets the available dates, once it does that it then clicks each date, but I pass in the driver object to the sub routine that reads the page your get what the date is clicked.
The attached zip has the complete xlsm file, you can look at it. It isn't the greatest.
The pseudo code would look like, to get the gist of it
button click:
set eDriver = New EdgeDriver
set dateDictionary = GetDates(eDriver)
For each dicItem in dateDictionary
ClickDate dictItem.Url, eDriver
Next
eDriver.Close
Set eDriver = Nothing
End button click
Function GetDates(eDriver as EdgeDriver) as Dictionary
find the date grid
loop the date grid, add dates that fit range to dictionary
return dictionary
sub ClickDates(dateUrl, eDriver)
eDriver.Get dateUrl
loop datePage Grid - fill desired data from each row into array
if datePage has pagination table then
call recursiveDatePage eDriver
return
Thanks again. I'll take a good look at your code later, but to avoid confusion here's my code. I hope that makes things more clear (I substituted certain fields with <info> obviously).
Code:
Sub MyCode()
Dim Driver As New WebDriver
Dim UserName As WebElement, PW As WebElement, LoginButton As WebElement
'
Driver.Start "edge", "<main URL>"
Driver.get "/"
Driver.Wait 1000
'
'Fill username field
Set UserName = Driver.FindElementById("USERNAME")
UserName.SendKeys "<myusername>"
Driver.Wait 1000
'
'Fill PW field
Set PW = Driver.FindElementById("PASSWORD")
PW.SendKeys "<mypassword>"
Driver.Wait 1000
'
'Find the login button by its ID
Set LoginButton = Driver.FindElementById("<loginbuttonID> ")
LoginButton.Click
Driver.Wait 1000
'
'Goto download page
Driver.get "<special URL>”
So the login works and then I want to jump to the special URL but once that instance opens, the login tab is replaced my the "special URL" and when that happens the login prompt comes back...
Oh okay. I miss read the "*not* resetting".
To my eye you are doing what you are supposed to (of course I'm not a Selenium expert by any means) do you know the mechanism the site uses to be aware that a user is logged in? Perhaps special consideration is required for different site authentication. Whether it uses JWT, Cookies, Session, Auth?
No, I have absolutely no clue how they do it. I think I'm back to continuing from my login (and forgetting about the 'special URL bypass').
I wanted to avoid that as that starts with a double 'mouse-hover' menu before I end up at the section where that 'special URL' brought me.
I'll look into it the coming days and I might share a screen-shot from the site code maybe.
I'm grateful for the help and patience :-) I thought this was easy: login, two actions and pressing a download button... But no
"Looks easy on paper" - the site probably blocks going directly to the URL and thus anyone not respecting the "proper path" to that URL is required to login (regardless of if they have already).
You're welcome, happy to help, even if only to bounce ideas around :-)