Results 1 to 27 of 27

Thread: SeleniumVBA

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    SeleniumVBA

    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")

    Thoughts please?

  2. #2
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    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

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA

    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??

  4. #4
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    I just wrote this last week. You need a reference to Selenium in VBA

    I installed this SeleniumBasic-2.0.9.0.exe then you just need whichever driver you're using.

    I'll download the edge web driver and swap it in to see if there is anything that needs changing.

    EDIT: Get the ChromeDriver from here https://googlechromelabs.github.io/chrome-for-testing/
    Attached Images Attached Images  
    Last edited by jdelano; Oct 4th, 2024 at 06:42 AM.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA

    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

  6. #6
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    What tutorial did you follow in your attempt to get this running?

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA


  8. #8
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    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
    Attached Images Attached Images  

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA

    I hear you and I will try to do that.

    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)

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA

    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 ;-)

  11. #11
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    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.

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA

    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?

  13. #13
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    What does the page look like when using Inspect? You can select by id, class, type, also xpath can be helpful in selecting the element.

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA

    See below (sorry, the picture is clear but somehow the results seems blurred?)
    Attached Images Attached Images  

  15. #15
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    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
    Guessing because of such a small screenshot

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA

    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.

  17. #17
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    using the same driver object, just eDriver.Get "the next url you want to go to"

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA

    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?

  19. #19
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    Hmm, that may be a site-specific action.

    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?

  20. #20

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA

    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.

  21. #21
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    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
    Attached Files Attached Files

  22. #22

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA

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

  23. #23
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    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?

  24. #24

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA

    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

  25. #25
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    "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 :-)

  26. #26

    Thread Starter
    Junior Member
    Join Date
    Feb 2024
    Posts
    27

    Re: SeleniumVBA

    I had some help from my son-in-law and we (he!) cracked it :-)

    We used

    Driver.FindElementByLinkText(<"Menutitle">)

    to find the location that would bring us to the download page and 'clicked' that.


    Then on the download page we used

    Driver.FindElementByXPath("//*[@title='<Button name>']")

    to find the download button and 'clicked' that.

  27. #27
    Hyperactive Member
    Join Date
    Jul 2022
    Posts
    402

    Re: SeleniumVBA

    perfect

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width