If Web element not found visit web page and login again in Vba-Selenium-CodePudding

I've below macro to login web page and exract data from table. But sometimes login is not successful and message appears on webpage - Please try to login again. So I tried adding If loop to check whether web element has been loaded and if not try again login. But to due to presence of If loop, getting below error:

Option Explicit
Private ch          As Selenium.ChromeDriver

Sub TestSelenium()

Dim Lastrow As Variant
Dim ws      As Worksheet
Dim sheetsname As String
Dim tr, td, th As WebElement
Dim c, r, l    As Integer
Lastrow = 1
Set ch = New Selenium.ChromeDriver
' ch.AddArgument "--headless"        ''Hide browser
ch.Start
ch.Get "https://address"

ch.Timeouts.ImplicitWait = 20000        ' 5 seconds

With ch

    With .FindElementById("logInForm")
        .FindElementById("j_username").SendKeys "name"
        .FindElementById("j_password").SendKeys "password@1012"
        .FindElementById("submitButton", timeout:=1000000).Click
        
        'ch.Timeouts.Server = 120000 ' 2 mins
        'ch.Timeouts.ImplicitWait = 50000 ' 5 seconds
    End With
    'Stop '<==  Delete me after inspection
    
   For l = 1 To 5
        If ch.IsElementPresent(FindElementByXPath("/html/body/div[1]/div/div[2]/div/div[4]/div/table/tbody/tr/td/div/table/tbody/tr[1]/td/div/div/div/div/div/div/div[1]/div[3]/div/div[4]/div/div/div/div/div[8]/div/div/div/div[1]/div/table")) Then
        
             '' Print header
            For Each tr In ch.FindElementByXPath("/html/body/div[1]/div/div[2]/div/div[4]/div/table/tbody/tr/td/div/table/tbody/tr[1]/td/div/div/div/div/div/div/div[1]/div[3]/div/div[4]/div/div/div/div/div[8]/div/div/div/div[1]/div/table").FindElementByTag("thead").FindElementsByTag("tr")
                c = 1
                For Each th In tr.FindElementsByTag("th")
                    Sheets("Sheet1").Cells(Lastrow   r, c).Value = th.Text
                    c = c   1
                Next th
                r = r   1
            Next tr
            '' Print table data
            For Each tr In ch.FindElementByXPath("/html/body/div[1]/div/div[2]/div/div[4]/div/table/tbody/tr/td/div/table/tbody/tr[1]/td/div/div/div/div/div/div/div[1]/div[3]/div/div[4]/div/div/div/div/div[8]/div/div/div/div[1]/div/table").FindElementByTag("tbody").FindElementsByTag("tr")
                c = 1
                For Each td In tr.FindElementsByTag("td")
                    Sheets("Sheet1").Cells(Lastrow   r, c).Value = td.Text
                    c = c   1
                Next td
                r = r   1
            Next tr
            
        Else ' if table NOT found
            With .FindElementById("logInForm")
                .FindElementById("j_username").SendKeys "name"
                .FindElementById("j_password").SendKeys "password@1012"
                .FindElementById("submitButton", timeout:=1000000).Click
                
                ch.Timeouts.Server = 120000 ' 2 mins
                ch.Timeouts.ImplicitWait = 50000 ' 5 seconds
            End With
            For Each tr In ch.FindElementByXPath("/html/body/div[1]/div/div[2]/div/div[4]/div/table/tbody/tr/td/div/table/tbody/tr[1]/td/div/div/div/div/div/div/div[1]/div[3]/div/div[4]/div/div/div/div/div[8]/div/div/div/div[1]/div/table").FindElementByTag("thead").FindElementsByTag("tr")
                c = 1
                For Each th In tr.FindElementsByTag("th")
                    Sheets("Sheet1").Cells(Lastrow   r, c).Value = th.Text
                    c = c   1
                Next th
                r = r   1
            Next tr
            ' Print table data
            For Each tr In ch.FindElementByXPath("/html/body/div[1]/div/div[2]/div/div[4]/div/table/tbody/tr/td/div/table/tbody/tr[1]/td/div/div/div/div/div/div/div[1]/div[3]/div/div[4]/div/div/div/div/div[8]/div/div/div/div[1]/div/table").FindElementByTag("tbody").FindElementsByTag("tr")
                c = 1
                For Each td In tr.FindElementsByTag("td")
                    Sheets("Sheet1").Cells(Lastrow   r, c).Value = td.Text
                    c = c   1
                Next td
                r = r   1
            Next tr

        End If
    Next l
    .Quit
End With
End Sub

Any help would be appreciated.

CodePudding user response：

I've made some assumptions, so unlikely that this code will actually work with your target website.

Try to separate your code, make sure you are logged in, and once you are then run your scraping code.

Trying to roll them all together just makes it harder for you.

The idea (untested) below is the "login" aspect.

Option Explicit
Private Driver As Selenium.ChromeDriver

Sub Main()

Set Driver = New Selenium.ChromeDriver

Driver.Start

Driver.Get "url"

If Login(Driver) = False Then
    Debug.Print "failed (multiple times) to login"
Else
    Debug.Print "successful login"
    ' do your stuff here
End If

End Sub

' this assumes that the login form does not exist if successfully logged in
Public Function Login(ByRef Driver As Selenium.ChromeDriver) As Boolean
Static LoginAttempt As Integer
Dim Form As Selenium.WebElement
Set Form = Driver.FindElementById("logInForm", 1000)

If Not Form Is Nothing Then
' the login form exists
    If LoginAttempt > 5 Then
        Debug.Print "login attempt exceeded"
        Login = False
    Else
        LoginAttempt = LoginAttempt   1
    End If
    Driver.FindElementById("j_username").SendKeys "username"
    Driver.FindElementById("j_password").SendKeys "password"
    Driver.FindElementById("submitButton").Click
    ' assume that clicking login button triggers navigation, so wait a little bit
    Application.Wait Now   TimeValue("0:00:5")
    ' re-run the login and return that result
    Login = Login(Driver)
Else
    LoginAttempt = 0
    Login = True
End If

End Function

Update

I cant figure out why the NoSuchElement error occurs as that method of testing has always worked for me (until now).

The following code works for me

Public Function Login(ByRef Driver As Selenium.ChromeDriver) As Boolean
Static LoginAttempt As Integer
Dim By As New Selenium.By

If Driver.IsElementPresent(By.ID("logInForm"), 1000) = True Then
    If LoginAttempt > 5 Then
        Login = False
    Else
        LoginAttempt = LoginAttempt   1
    End If
    Driver.FindElementById("j_username").SendKeys "username"
    Driver.FindElementById("j_password").SendKeys "password"
    Driver.FindElementById("submit").Click
    ' assume that clicking login button triggers navigation, so wait a little bit
    Application.Wait Now   TimeValue("0:00:5")
    ' run again to test if successful
    Login = Login(Driver)
Else
    LoginAttempt = 0
    Login = True
End If

End Function

CodePudding user response：

As I was extracting data and writing to excel, added check using Do Until loop to iterate through whole macro which works:

Option Explicit
Private ch          As Selenium.ChromeDriver

Sub TestSelenium()

Dim Lastrow As Variant
Dim ws      As Worksheet
Dim sheetsname As String
Dim tr, td, th As WebElement
Dim c, r, l As Integer
Lastrow = 1
Set ch = New Selenium.ChromeDriver

Dim i As Integer
i = 1

Application.ScreenUpdating = False
Do Until i = 6 Or Range("A1") <> 0 ' Check with multiple attempts till A1 cell is not empty

    On Error Resume Next
    ch.AddArgument "--headless"        ''Hide browser
    ch.Start
    ch.Get "https://address"
    
    ch.Timeouts.ImplicitWait = 20000        ' 5 seconds

    With ch
    
        With .FindElementById("logInForm")
            .FindElementById("j_username").SendKeys "username"
            .FindElementById("j_password").SendKeys "password"
            .FindElementById("submitButton", timeout:=1000000).Click
            
            'ch.Timeouts.Server = 120000 ' 2 mins
            'ch.Timeouts.ImplicitWait = 50000 ' 5 seconds
        End With
        'Stop '<==  Delete me after inspection
        
                 '' Print header
                For Each tr In ch.FindElementByXPath("/html/body/div[1]/div/div[2]/div/div[4]/div/table/tbody/tr/td/div/table/tbody/tr[1]/td/div/div/div/div/div/div/div[1]/div[3]/div/div[4]/div/div/div/div/div[8]/div/div/div/div[1]/div/table").FindElementByTag("thead").FindElementsByTag("tr")
                    c = 1
                    For Each th In tr.FindElementsByTag("th")
                        Sheets("Sheet1").Cells(Lastrow   r, c).Value = th.Text
                        c = c   1
                    Next th
                    r = r   1
                Next tr
                '' Print table data
                For Each tr In ch.FindElementByXPath("/html/body/div[1]/div/div[2]/div/div[4]/div/table/tbody/tr/td/div/table/tbody/tr[1]/td/div/div/div/div/div/div/div[1]/div[3]/div/div[4]/div/div/div/div/div[8]/div/div/div/div[1]/div/table").FindElementByTag("tbody").FindElementsByTag("tr")
                    c = 1
                    For Each td In tr.FindElementsByTag("td")
                        Sheets("Sheet1").Cells(Lastrow   r, c).Value = td.Text
                        c = c   1
                    Next td
                    r = r   1
                Next tr
        .Quit
    End With
    
    i = i   1
    MsgBox "i is " & i
Loop
Application.ScreenUpdating = True
MsgBox "Done"

End Sub