Solved

Use VBA to enter Search Details in HTML Document and read returned Search

Posted on 2013-05-16
10
835 Views
Last Modified: 2013-05-24
Hello Experts,

I'm in a rather awkward situation in a current application I'm developing.

Basically, how do I use VBA enter a search sting into the following website:

http://www.cro.ie/search/DisqualifiedSearch.aspx

and then get it to search. Then, read the search results and store them in the Database ?

I've seen other forums and have been made to understand that I need to get the "Name" or "ID" of the "Search Text" Control and that of the "Search" button. However, I have parsed the HTML document above and I can't seem to get either of their names.

I have the following code so far:

searchString = "smith"

Set ie = CreateObject("InternetExplorer.application")
ie.Visible = True
ie.navigate ("http://www.cro.ie/search/DisqualifiedSearch.aspx")

Do
    If ie.readyState = 4 Then
        Exit Do
    Else
        DoEvents
    End If
Loop

'Need Code to enter the search string into the Search Text Box
'Need Code to Submit the Search 

'Need Code to read the returned Results

Open in new window


I can probably manage with the storing of the data in the Database, however, I need help with making reference to the "Name" or "ID" of the Search Text Box, Submitting the Results (or Submitting the Form) and Reading returned results

Any help will truly be appreciated.

Many Thanks,

ref-IT
0
Comment
Question by:ref-IT
  • 6
  • 3
10 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39175891
This should get you started:
'Need Code to enter the search string into the Search Text Box
ie.document.all("ctl00$ContentPlaceHolder1$Surname").Value = searchString

'Need Code to Submit the Search
ie.document.all("ctl00$ContentPlaceHolder1$ButtonSearch").Click
Do
    Set tbl = ie.document.all("ctl00_ContentPlaceHolder1_GridView1") ' output table
    If Not tbl Is Nothing Then
        Exit Do
    ElseIf InStr(ie.document.documentelement.innertext, "No matches found") > 0 Then
        Exit Do
    Else
        DoEvents
    End If
Loop

'Need Code to read the returned Results
If Not tbl Is Nothing Then
    For rw = 1 To tbl.Rows.Length - 1 ' skip header
        Debug.Print "found: " & tbl.Rows(rw).Cells(0).innertext
    Next
End If

ie.Quit
Set ie = Nothing

Open in new window

0
 
LVL 2

Author Comment

by:ref-IT
ID: 39177034
Thank you moderator (Modulus_Twelve).

Hey  robert_schutt,

Thank you very much for the code. It totally works and I'm really grateful.

However, the code only works when I step through it, when I run the code it gives me the following error:

424   Object Required

at the following line of code:

Set tbl = ie.Document.all("ctl00_ContentPlaceHolder1_GridView1") 'output table

I made the following changes to resolve the error but it still did not resolve the issue

- Set the DataType of tbl to HTMLTable
- Put the following code before the above statement executes so that the code can wait until the readystate of the submitted document is 4 before the table is read:

Do
    If ie.readyState = 4 Then
        ie.Visible = True
        Exit Do
    Else
        DoEvents
    End If
Loop

However, both the above actions did not work.

Any leads or ideas on how to solve the above error ?

Regards,

ref-IT
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39177053
well yeah, that loop that waits for readystate is quite common but didn't work for me (possibly because of the way the asp.net form is submitted and data retrieved into the page) so I changed it to check for the presence of the table in the output (or 'no matches'). There could be a number of reasons we don't have the same reaction (browser version/cache settings) but maybe the easiest thing to do would be to ignore errors for that part of the code:
'Need Code to enter the search string into the Search Text Box
ie.document.all("ctl00$ContentPlaceHolder1$Surname").Value = searchString

'Need Code to Submit the Search
ie.document.all("ctl00$ContentPlaceHolder1$ButtonSearch").Click
On Error Resume Next
Set tbl = Nothing
Do
    Set tbl = ie.document.all("ctl00_ContentPlaceHolder1_GridView1") ' output table
    If Not tbl Is Nothing Then
        Exit Do
    ElseIf InStr(ie.document.documentelement.innertext, "No matches found") > 0 Then
        Exit Do
    Else
        DoEvents
    End If
Loop
On Error GoTo 0

'Need Code to read the returned Results
If Not tbl Is Nothing Then
    For rw = 1 To tbl.Rows.Length - 1 ' skip header
        Debug.Print "found: " & tbl.Rows(rw).Cells(0).innertext
    Next
End If

ie.Quit
Set ie = Nothing

Open in new window

0
 
LVL 2

Author Comment

by:ref-IT
ID: 39180374
Hey  robert_schutt

Thank you so much for your input and expert help. I appreciate a lot.

Your code works just fine, except that ignoring the errors is not giving any outputs at all !!

I'm currently experimenting with the code, trying to put in timer delays instead of waiting for the readystate to be 4. I'm thnking of having a 2 second delay before the application starts reading results and a 2 second delay after the application reads each row. That should give Internet Explorer enough time to settle down so the application can then start reading results comfortably (?)

I'll post the results by this evening or tomorrow morning latest. In the meantime, if there's anything you can add extra to help, I'll appreciate it.

Regards,

ref-IT
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39180395
The problem with waiting a few secs is always that no matter how comfortable the time should be, at some point it breaks because of a momentary slow internet connection for example. And of course although it will probably work, the time it takes is a lot longer which shouldn't be necessary.

Did you notice the added line 7 in my previous post? The initialisation is important or it will fall through immediately.

Otherwise, some debugging (use 'Debug.Print ie.readystate' and maybe ie.busy in the loop) might give a clue as to what's happening and what condition(s) could be used instead to terminate the loop.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39180406
I added some debugging and found that for some time, the readyState remains 4 (that's why it doesn't work as an immediate loop terminator) but then it goes to 3 for a bit so I think it would help to add a loop after the .Click call that waits for readyState <> 4, then use the 'normal' loop that waits for readyState = 4 again, then you should have data. I'll do some more testing.

EDIT: and forget about ie.busy that I mentioned in my previous post, it never goes to True so that's definitely due to the asp.net form being posted differently than what I'm used to.
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 39180518
I've been testing a bit and this works for me consistently:
Option Explicit

Dim ie As Object ' InternetExplorer

Sub doSearch()

    Dim searchString As String
    searchString = "smith"

    Set ie = CreateObject("InternetExplorer.application")
    'ie.Visible = True
    ie.navigate "http://www.cro.ie/search/DisqualifiedSearch.aspx"

    WaitState 4 ' wait for ie to be done with initial navigation

    ' enter the search string into the Search Text Box
    ie.document.all("ctl00$ContentPlaceHolder1$Surname").Value = searchString

    ' Submit the Search
    ie.document.all("ctl00$ContentPlaceHolder1$ButtonSearch").Click

    WaitState 4, False ' wait for ie to _start_ doing something

    WaitState 4 ' wait for ie to be done with processing form post

    ' read the returned Results
    If InStr(ie.document.documentelement.innertext, "No matches found") > 0 Then
        Debug.Print "Nobody here by that name!"
    Else
        Dim tbl As HTMLTable, rw As Integer
        Set tbl = ie.document.all("ctl00_ContentPlaceHolder1_GridView1") ' output table
        For rw = 1 To tbl.Rows.Length - 1 ' skip header
            Debug.Print "found: " & tbl.Rows(rw).Cells(0).innertext
        Next
        Set tbl = Nothing
    End If

    ie.Quit
    Set ie = Nothing

End Sub

Sub WaitState(ByVal iState As Integer, Optional ByVal blnEqual As Boolean = True)
    Do
        If (ie.readyState = iState) = blnEqual Then
            Exit Do
        Else
            DoEvents
        End If
    Loop
End Sub

Open in new window

0
 
LVL 2

Author Closing Comment

by:ref-IT
ID: 39193967
Hey  robert_schutt

My sincere apologies on a delay on getting back to you. I had to divert attention to the delivery of some other Projects that I'm working on and thus, could not give this time.

I ran some tests using the Sleep API that I found on the internet that you can view using the following link:

http://access.mvps.org/access/api/api0021.htm

I substituted the calls to the API in place of the WaitState function.

Then I ran your code together with the WaitState Function.

I'll have to tell you, the WaitState function was much more consistent with results (in-fact it was 100% consistent) as compared to the Sleep API. On 10 attempts of calling the Search String "bb", the Sleep API code returned 8 correct results while the WaitState function returned 100%

Many Many Thanks - you've been Spot On and you deserve all the points

Best Regards,

ref-IT
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39193973
Don't worry about any delays (a couple of days is nothing compared to some other open questions), and we've all been there (having to divert attention I mean).

Thanks for the points, the grade and the fantastic closing comment!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now