0

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 attached excel file, pasted in the website and then clicked on Verify Button. See image 1

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).

The next hurdle is: To take the Next Project Code from the sheet , the page needs to be like the first screenshot, which can be achieved via clicking on "Go Back To List". See below image:

Image 2

Once clicked it takes back to the page in which we need to click on "Go back to Search Page", which takes us to main page from where we started and only then we can paste the new project code.

See final result page - Final Result Page

I took a try at the first step: 1)Open the Page 2)Input the Project Code 3)Clicking on Very button

With the below code, but there i get an error when the chrome opens...... ChromeError

Option Explicit

Public Sub NHBsite()

Dim bot As WebDriver, rng As Range, Cell As Range
Dim ws As Worksheet

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
    bot.Get URL
    bot.FindElementById("ctl00_ContentPlaceHolder1_ctl00_txtProjectCode").SendKeys Cell.Value
    bot.FindElementById("ctl00_ContentPlaceHolder1_ctl00_btnSearchProject").Click
    bot.FindElementById("ctl00_ContentPlaceHolder1_ctl00_gvSerachDetails_ctl02_lblProjectCode").Click

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

bot.Quit

End Sub

Please help me on this. I'm sitting daily like 6-7 hours on this , learning from websites as well, going thru YouTube videos and E-books too.

Improve this question
5
  • Do you mean search rather than verify button? Can you make it clear exacty which step in your code is causing this? Commented Apr 16, 2020 at 6:11
  • @QHarr The Verify button in that website does the search work, so used that term itself. Commented Apr 16, 2020 at 6:43
  • @QHarr The code which you gave had to declare Cell variable, apart from that it worked fine, But i need it to go beyond second page too, to reach the final page and extract the data. For now i used the screenshot method, but i want the data to be extracted from the final page. Commented Apr 16, 2020 at 6:45
  • Hiya, you should open a new question for the extracting to sheet. I was answering what I thought you were asking in this question. Commented Apr 16, 2020 at 12:17
  • Extracting is not difficult you just need either error handling or some form of test for when there are not results. When there are results simply grab the results table by its id and you can use the inbuilt FromTable (?) method of the driver. There is an inbuilt method for copying tables to sheet which intellisense should offer you. Commented Apr 16, 2020 at 12:19

1 Answer 1

Reset to default
1

You want to loop the codes and each new code navigate back to the start url. (Not tested)

Option Explicit

Public Sub NHBsite()

    Dim bot As WebDriver, rng As Range
    Dim ws As Worksheet, cell Ad Range

    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
        bot.get URL
        bot.FindElementById("ctl00_ContentPlaceHolder1_ctl00_txtProjectCode").SendKeys Cell.Value
        bot.FindElementById("ctl00_ContentPlaceHolder1_ctl00_btnSearchProject").Click
        bot.Wait 1000
        bot.TakeScreenshot.SaveAs (ThisWorkbook.Path + "/Screenshot_" + Cell.Value + ".jpg")
    Next

    bot.Quit

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Lets take for example the First project code - 37TTG0000170 The final result page looks something like the screenshot which i have just added, from there i need to pick up 2 tables and some other details as well. That is my full and final requirement.
There is no need to click on the "go back to search result " , we can directly go to first page via the URL in loop, right ?
Sir, i have reached the final result page, but not able to extract the results from that page. I have updated the Code above .
you want FindElementById("ctl00_ContentPlaceHolder1_ctl00_gvSerachDetails") to target the results table

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.