CODEWITHSUNDEEP
excelvbaseleniumselenium-webdriver
Amit Shah
Amit Shah

Reputation: 65

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

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

Shipping Bill no

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.

Upvotes: 0

Views: 369

Answers (2)

KunduK
KunduK

Reputation: 33384

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

Upvotes: 1

NickSlash
NickSlash

Reputation: 5100

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

Upvotes: 1

Related Questions