I have a code in VBA that I've always used to open Internet Explorer, copy the information on an specific website and than paste in a cell. The issue now is that the website now is no longer working with IE.

I'm trying to adjust this code to use that with Edge and / or Chrome (I already installed Selenium) but I'm actually struggling with it.

Can someone help me to adjust that code?

Option Explicit


Sub Test()


    Dim IE As Object
    On Error Resume Next
    Application.DisplayAlerts = False
    
    Sheets("Sheet3").Select
    Range("A1:A1000") = "" ' erase previous data
    Range("A1").Select


    Set IE = CreateObject("InternetExplorer.Application")
        With IE
            .Visible = True
            .Navigate "https://google.com" ' should work for any URL
            Do Until .ReadyState = 4: DoEvents: Loop
        End With


        IE.ExecWB 17, 0 '// SelectAll
        IE.ExecWB 12, 2 '// Copy selection
        ActiveSheet.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False
        Range("A1").Select
        IE.Quit

    Application.DisplayAlerts = True
End Sub

How I switch the IE mentions to a Edge or Chrome one? For instance, change IE to objEdge, etc..

CodePudding user response：

You need to use SeleniumBasic to automate Edge in VBA. SeleniumBasic is a Selenium based browser automation framework for VB.Net, VBA and VBScript.

You can follow the steps below to automate Edge browser with SeleniumBasic:

1. Download the latest version of SeleniumBasic v2.0.9.0 from this link and install it.
2. Download the corresponding version of Edge WebDriver from this link.
3. Find the path of SeleniumBasic which is C:\Users\%username%\AppData\Local\SeleniumBasic in my situation (it might also be in this path C:\Program Files\SeleniumBasic), copy the Edge WebDriver msedgedriver.exe to this path.
4. Rename msedgedriver.exe to edgedriver.exe.
5. Open Excel and prepare to write the VBA code.
6. In the VBA code interface, click Tools > References, add Selenium Type Library reference and click OK to save.
7. Example VBA code (you can change it according to your demands):

   Public Sub Selenium()
       Dim bot As New WebDriver
       bot.Start "edge", "https://www.google.com"
       bot.Get "/"
       bot.Wait 5000
       bot.Quit
   End Sub
   

Reference link: VBA Script to convert from internet explorer to Edge or chrome browser