CODEWITHSUNDEEP
excelvbaseleniumselenium-webdriver
Amit Shah
Amit Shah

Reputation: 65

Unable to loop the Excel data in Chrome using Selenium VBA

I'm finding difficulty in loop functionality, ie. taking the values from excel sheet and performing the task. I have this website: http://dgftebrc.nic.in:8100/BRCQueryTrade/index.jsp wherein I need to input IEC Code:

  1. 0906008051
  2. 0906008069

Later input the

Shipping Bill no :

  1. 3929815
  2. 3953913
  3. 3979509

I mean that for now I have hard coded but I'm not getting it to function in a loop since I have large data and I cannot write it in a hard code fashion, since for 1 IEC number I have around 1000 SB.no, so there can be 100 IEC numbers, hence many Shipping bills (SB.no) no's.

Working with the tabs I have solved but then working with multiple Shipping bills no.'s is causing me problems.

I could manage this much of a code :

Option Explicit

Public Sub multipletabtest()

Dim bot As WebDriver
Dim keys As New Selenium.keys
Dim count As Long

Set bot = New WebDriver
bot.Start "Chrome"
'count = 1
'While (Len(Range("A" & count)) > 0)

bot.Get "http://dgftebrc.nic.in:8100/BRCQueryTrade/index.jsp"

bot.FindElementByXPath("//input[@type='text'][@name='iec']").SendKeys "0906008051"
bot.FindElementByXPath("//input[@type='text'][@name='sno']").SendKeys "3929815"
bot.Wait 10000         'Time to enter the captcha

bot.FindElementByCss("[value='Show Details']").SendKeys keys.Control, keys.Enter          'Take the value from final result sheet
bot.SwitchToNextWindow

ThisWorkbook.Sheets("Sheet1").Range("B1") = bot.FindElementByXPath("//text()[.='Used']/ancestor::td[1]").Text
'Range("B" & count) = bot.FindElementByXPath("//text()[.='Used']/ancestor::td[1]").Text  'To extract the data
'bot.Window.Close


bot.SwitchToPreviousWindow
bot.FindElementByXPath("//input[@type='text'][@name='sno']").Clear

bot.FindElementByXPath("//input[@type='text'][@name='sno']").SendKeys "3953913"
bot.FindElementByCss("[value='Show Details']").SendKeys keys.Control, keys.Enter
bot.SwitchToNextWindow

ThisWorkbook.Sheets("Sheet1").Range("B2") = bot.FindElementByXPath("//text()[.='Used']/ancestor::td[1]").Text
'Range("B" & count) = bot.FindElementByXPath("//text()[.='Used']/ancestor::td[1]").Text


'count = count + 1
'Wend
bot.Quit
End Sub

And since if anyone is wondering why Ctrl+Enter, that's because the Captcha remains the same for other Shipping bill no., so chose this method.

I have tried the while statement as well, but then the extracted data is being copied twice.

See image

Upvotes: 2

Views: 756

Answers (1)

Amit Shah
Amit Shah

Reputation: 65

Option Explicit
Public Sub multipletabtest()

Dim bot As WebDriver
Dim keys As New Selenium.keys
Dim count As Long

Set bot = New WebDriver
bot.Start "Chrome"
bot.Get "http://dgftebrc.nic.in:8100/BRCQueryTrade/index.jsp"

count = 1
While (Len(Range("A" & count)) > 0)


bot.FindElementByXPath("//input[@name='iec']").SendKeys Range("A" & count)
bot.FindElementByXPath("//input[@name='sno']").SendKeys Range("B" & count)

bot.Wait 10000         'Time to enter the Captcha


bot.FindElementByCss("[value='Show Details']").SendKeys keys.Control, keys.Enter
bot.SwitchToNextWindow


If bot.FindElementsByXPath("//tr[2]//td[7]").count > 0 Then
Range("C" & count) = bot.FindElementByXPath("//tr[2]//td[7]").Text

If bot.FindElementsByXPath("//p[contains(text(),'No Data Found.....check the Input Parameters')]").count > 0 Then
Range("C" & count) = "No Data"


End If
End If
bot.Window.Close

bot.SwitchToPreviousWindow
bot.FindElementByXPath("//input[@type='text'][@name='sno']").Clear


count = count + 1
Wend

bot.Quit
End Sub

Upvotes: 1

Related Questions