Extracting Data from the final Result Page using Selenium & VBA

Question

I'm working on automating a Website to excel copy -paste work. The website here is a government website, so a little tricky to handle. I have a problem in extracting data from this website : http://nhb.gov.in/IndexPage.aspx?enc=nRXYbvwNFTF218kodGo9fg==

Few project codes for trying out are :

37TTG0000170
28AAP0023256
23MP0006837
23AMP0006839

I have a list of project codes, which should be taken from the excel file, pasted in the website and then clicked on Verify Button.

Then in the next page the the same project code is hyperlinked, which upon clicking leads us to our results page with information about the project, which is to be extracted and pasted into the excel sheet beside the respective project code, such as those tables and data.(That's a later part which can be tweaked).

I have reached the last result page but finding it difficult to extract stuff out of it.

Option Explicit

Public Sub NHBsite()

 Dim bot As WebDriver, Cell As Range
'Dim ws As Worksheet
'Dim rng as Range
 Dim count As Long

Const URL As String = "http://nhb.gov.in/IndexPage.aspx?enc=nRXYbvwNFTF218kodGo9fg=="

Set bot = New ChromeDriver
'Set ws = ThisWorkbook.Worksheets("Data")
'Set rng = ws.Range(ws.Range("A2"), ws.Range("A2").End(xlDown))

'bot.Window.Maximize

'For Each Cell In rng
count = 1

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

bot.Get URL

bot.FindElementById("ctl00_ContentPlaceHolder1_ctl00_txtProjectCode").SendKeys 
Range("A" & count)
bot.FindElementById("ctl00_ContentPlaceHolder1_ctl00_btnSearchProject").Click 
     bot.FindElementById("ctl00_ContentPlaceHolder1_ctl00_gvSerachDetails_ctl02_lblProjectCode").Click

Range("B" & count) = bot.FindElementById("Table5").Text

 count = count + 1

Wend

'bot.Wait 1000
'bot.TakeScreenshot.SaveAs (ThisWorkbook.Path + "/Screenshot_" + Cell.Value + 
".jpg")
 'Next

bot.Quit  

End Sub

QHarr · Accepted Answer · 2020-04-16 15:23:36Z

1

You can use the inbuilt selenium method for working with tables. You will need to have already handled the case where no results found (in that instance you wouldn't be navigating back as wouldn't have left original page); only if there is a results table then you can do

bot.FindElementById("ctl00_ContentPlaceHolder1_ctl00_gvSerachDetails").AsTable.ToExcel ws.Range("where ever you want table to go")

This introduces a new problem. Your data is arranged on a code per row basis but results will come back potentially with more than one row (you need to determine this). One way to handle this is to create a new worksheet in the loop over cell in rng and use the cell.value to name the sheet; then paste the table retrieved into that new sheet. If there can only be one row then instead use FindElementsByCss("#ctl00_ContentPlaceHolder1_ctl00_gvSerachDetails tr:nth-of-type(2) td") and For Each loop that collection and write out column wise. Use a row counter to increment the row to write out to each time you change code.

answered Apr 16, 2020 at 15:23

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

6 Comments

Amit Shah Over a year ago

Sorry Sir, but i didn't understand this method :( There are no cases where we don't have a result, all Project codes yield something or the other. I did understand the Row problem. But can't frame the code, really sorry about it, if only you can explain it in a little more detail.

QHarr Over a year ago

23MP0006837 does not yield a result when pressing search button

Amit Shah Over a year ago

It must have been removed during all this time, last week it was yielding a result. Apart from that, all yield result.

Amit Shah Over a year ago

I have a small issue here before clicking the Search Button, i.e., All project codes are not "IPA Project ", few are "LOI Project " too, is there a way to search around both the buttons ?

Amit Shah Over a year ago

I have edited my code, could you please look into this. I have successfully extracted "table5" with this code, but finding it difficult for other information.

|

Collectives™ on Stack Overflow

Extracting Data from the final Result Page using Selenium & VBA

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related