Link to home
Create AccountLog in
Avatar of Mudasir Noorani
Mudasir NooraniFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

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
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

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

Avatar of Mudasir Noorani

ASKER

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

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
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
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!