Solved

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

Posted on 2013-05-16
10
839 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

815 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

8 Experts available now in Live!

Get 1:1 Help Now