CODEWITHSUNDEEP
excelvbaselenium
Joseph John
Joseph John

Reputation: 77

VBA Excel Selenium - want to run for all the data

  1. This is the main VBA Script, i want to run from B2:AA2 to Bn:AAn
  2. It has moooooooor column after Mobile number and moooooore rows after 11
  3. wanted to loop this script till my last entry of the excel sheet, where it automatically sense by itself that till any blank cell, it has to run script for all the enteries
Sub Auto_Web(row_index)
Debug.Print (row_index)
    Dim row As Integer
    row = 2
    Dim ListDD As Selenium.WebElement
    Dim elem As WebElement
    Dim bot As WebDriver
    Set bot = New WebDriver
'    bot.Start "chrome"
'    bot.Get "/"
'    bot.FindElementbyName("username").SendKeys "ID"
'    bot.FindElementbyName("passwd").SendKeys "Password"
'    bot.FindElementByClass("login100-form-btn").Click
      
'#Patient Name
    bot.FindElementbyName("patient_name").SendKeys (Sheet1.Cells(row_index, 2).Value)

'#Patient ID = Sample ID
    bot.FindElementById("patient_id").SendKeys (Sheet1.Cells(row_index, 3).Value)

'#Age-in
    If Sheet1.Cells(row_index, 4) = "Years" Then
        bot.FindElementById("age_year").Click
        bot.FindElementbyName("age").SendKeys (Sheet1.Cells(row_index, 5).Value)
    Else
    If Sheet1.Cells(row_index, 4) = "Months" Then
        bot.FindElementById("age_month").Click
        bot.FindElementbyName("age").SendKeys (Sheet1.Cells(row_index, 5).Value)
    Else
    If Sheet1.Cells(row_index, 4) = "Days" Then
        bot.FindElementById("age_day").Click
        bot.FindElementbyName("age").SendKeys (Sheet1.Cells(row_index, 5).Value)
    End If
    End If
    End If

'#Gender
    If Sheet1.Cells(row_index, 6) = "Male" Then
        Set ListDD = bot.FindElementById("gender")
        ListDD.AsSelect.SelectByText (Sheet1.Cells(row_index, 6).Value)
    Else
    If Sheet1.Cells(row_index, 6) = "Female" Then
        Set ListDD = bot.FindElementById("gender")
        ListDD.AsSelect.SelectByText (Sheet1.Cells(row_index, 6).Value)
    Else
    If Sheet1.Cells(row_index, 6) = "Transgender" Then
        Set ListDD = bot.FindElementById("gender")
        ListDD.AsSelect.SelectByText (Sheet1.Cells(row_index, 6).Value)
    End If
    End If
    End If

'#Mobile Number
        bot.FindElementbyName("contact_number").SendKeys (Sheet1.Cells(row_index, 7).Value)
    bot.FindElementById("btn").Click
    bot.Wait 5000
    
End Sub

Sub main()
' your main runner function which will control the execution
    Dim bot As WebDriver
    Set bot = New WebDriver
    bot.Start "chrome"
    bot.Get "/"
    bot.FindElementbyName("username").SendKeys "ID"
    bot.FindElementbyName("passwd").SendKeys "Password"
    bot.FindElementByClass("login100-form-btn").Click
    
    
    current_row = 2                                     ' initialise the counter to start from second row
    continue_loop = True                                ' flag to decide whether to continue on next row or not
    While continue_loop                                 ' loop will conitnue as long as continue_loop flag is True
        Auto_Web (current_row)                      ' your subroutine is called to do the actual stuff for the current row
        Debug.Print Sheet2.Cells(current_row, 2).Value
        
        If Len(Sheet2.Cells(current_row + 1, 2).Value) = 0 Then 'check if the next row is empty. change the column index to a suitable one in case a column may have empty values for a legal row
            continue_loop = False 'if emptry then rest the flag
        Else
            current_row = current_row + 1 'otherwise increase the counter
        End If
    Wend
    
End Sub

enter image description here

Upvotes: 0

Views: 396

Answers (1)

Roy
Roy

Reputation: 344

Something like this should work. Try to implement it in your way.

Sub AutoMateSite(row_index)
    Debug.Print (row_index)
    'Your rest of the code where you get row_index as input to chose which row to pick data from
End Sub


Sub main()
' your main runner function which will control the execution
    current_row = 2 ' initialise the counter to start from second row
    continue_loop = True ' flag to decide whether to continue on next row or not
    While continue_loop ' loop will conitnue as long as continue_loop flag is True
        AutoMateSite (current_row) ' your subroutine is called to do the actual stuff for the current row
        Debug.Print Sheet2.Cells(current_row, 2).Value
        
        If Len(Sheet2.Cells(current_row + 1, 2).Value) = 0 Then 'check if the next row is empty. change the column index to a suitable one in case a column may have empty values for a legal row
            continue_loop = False 'if emptry then rest the flag
        Else
            current_row = current_row + 1 'otherwise increase the counter
        End If
    Wend
    
End Sub

Upvotes: 1

Related Questions