Unable to input data from a list in excel via selenium VBA

Question

I have this website "http://dgftcom.nic.in/licasp/sbdetformnew.asp" , where in i need to enter IEC code - against which i have different Ports to select from(this is where i'm finding difficulty), and then the Shipping bill needs to be entered below(again taken from excel)

IEC Code

0392032449
0392032449

Port
INBOM4(Bombay Air Cargo)
INNGP6 Nagpur

Shipping Bill no

3963373
3954544

I have written the code, but finding it difficulty at selecting those port names.

    Option Explicit
    Public Sub Shippingdetails()   ' Entering IEC code and port number

    Dim bot As WebDriver
    Dim count As Long

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

    bot.Get "http://dgftcom.nic.in/licasp/sbdetformnew.asp"
    bot.FindElementByXPath("//input[@type='text'][@name='D5']").SendKeys Range("A" & count)
    bot.FindElementByXPath("//*[@id='AutoNumber1']/tbody/tr[7]/td[3]/font").AsSelect.SelectByText Range("B" & count)
    bot.FindElementByXPath("//input[@type='text'][@name='T5']").SendKeys Range("C" & count)
    bot.FindElementById("button1").Click


    Range("D" & count) = bot.FindElementByXPath("//html/body/div/center/div/table[4]/tbody/tr[2]/td[7]/font").Text


    'Range("E" & count) = bot.FindElementByCss("table td[8], table + font").Text
    Range("E" & count) = bot.FindElementByXPath("/html/body/div/center/div/table[4]/tbody/tr[2]/td[8]/font/b")

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

Ultimately i need to extract the "File no and date" & "Status". Please help me out.

With above example I have got nothing. No Shipping Bill details available for Shipping Bill Number : 3963373 Please Check Port Code / IEC / SB Number for Correctness. — KunduK
– KunduK, Commented Apr 23, 2020 at 20:56

KunduK · Accepted Answer · 2020-04-23 21:18:15Z

1

Try below xpath to select.

bot.FindElementByXPath("//select[@name='D8']").AsSelect.SelectByText Range("B" & count)

I would suggest use SelectByValue

bot.FindElementByXPath("//select[@name='D8']").AsSelect.SelectByValue "INBLR4"

To handle the error you try something like this.Check the element count >0

If  bot.FindElementsByXPath("//html/body/div/center/div/table[4]/tbody/tr[2]/td[7]/font").Count >0 Then
     Range("D" & count) = bot.FindElementByXPath("//html/body/div/center/div/table[4]/tbody/tr[2]/td[7]/font").Text

 If  bot.FindElementsByXPath("/html/body/div/center/div/table[4]/tbody/tr[2]/td[8]/font/b").Count >0 Then
     Range("E" & count) = bot.FindElementByXPath("/html/body/div/center/div/table[4]/tbody/tr[2]/td[8]/font/b").Text

edited Apr 23, 2020 at 21:18

answered Apr 23, 2020 at 20:48

KunduK

33.4k5 gold badges19 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Amit Shah Over a year ago

Now i'm facing difficulty in 0392032449(IEC) no., INRXLB(port no) , 3953800(SB No.) Here i'm getting "No Shipping info available" and the code get stuck there. It's not going to next cell in excel to search for others. Do i have to use a "if else statment" ?

KunduK Over a year ago

@AmitShah : check if that helps.

Amit Shah Over a year ago

Hey , thanks a lot, I tried the same way, and it worked. Now i'm gonna try via CSS selector methods, so that the whole process is much faster.

NickSlash · Accepted Answer · 2020-04-23 20:58:03Z

You could skip the form and go straight to the result page, browser automation has never worked that well when I've used it.

The following function, when provided with IEC, EDI and BillNumber will return a HTMLDocument containing the result.

The Sub Main below will print the required data to the immediate window. I'm not sure how use XPath with the result of my function so I just hard coded the location.

Public Function GetDetail(ByVal IEC As String, ByVal EDI As String, ByVal ShippingBillNumber As String) As Object
Dim Request As Object: Set Request = CreateObject("MSXML2.serverXMLHTTP")
Dim Result As Object: Set Result = CreateObject("htmlfile")

Request.Open "POST", "http://dgftcom.nic.in/licasp/newsbdet.asp", False
Request.setRequestHeader "content-type", "application/x-www-form-urlencoded"
Request.send "D5=" & IEC & "&D8=" & EDI & "&T5=" & ShippingBillNumber & "&button1=SB-Detail"
Result.body.innerHTML = Request.responseText

Set GetDetail = Result
End Function


Sub Main()
Dim x As Object
Dim Data As Object
Set x = GetDetail("0392032449", "INBOM4", "3963373")
Set Data = x.getElementsByTagName("table")(4).getElementsByTagName("tr")(1).getElementsByTagName("td")
Debug.Print "Date: " & Split(Data(6).innerText, vbNewLine)(1)
Debug.Print "Status:" & Data(7).innerText
End Sub

This is a beautiful attempt to this answer, but i rarely use MSXML method. This is doing the job, but then again, finding difficulty in adding the Xpath or other extractions methods, i'm try tomorrow for sure. Thanks a lot for your support , Good night.

Collectives™ on Stack Overflow

Unable to input data from a list in excel via selenium VBA

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related