VBA clicking on websearch Result

[pablop]

I am trying to get property tax values for about 200 properties. The info is publicly available on this site:
http://www.hawaiipropertytax.com/Searc/GenericSearch.aspx?mode=PARID

I have a list of Parcel IDs (also known as TMK Value) that identify properties in a spreadsheet. Here are just two for testing
750100600000
640190140000

Now, the first time you run my macro, it will stop because there is a disclosure that shows up. After you accept it once, you will not be prompt again. I assumed that I have accepted that, so I don't have to deal with that step in my macro, but you may have to accept the disclosure once, then resume the macro during testing.

OK. I have a spreadsheet with Parcel IDs. My macro selects the first one, then goes to the site above, inputs the value in the search box and clicks the search button. Then, the results load. I want to click on the first line of results, but I have been unsuccessful. I have used sendkeys before, but this time it is not working. I need help making that click. Assuming I can get pass that, it would take me to another page, where I want to click on the left hand side menu called "Values - 2011 Assessment Year". After that loads, I want to bring into excel the row displaying the "Parcel Summary Totals". Any help is appreciated.

Here is what I have so far.

Sub Tax_Property_Value_Grabber()
Dim ie As Object, lLastRow As Integer, i As Integer, TMKValue As String
Set ie = CreateObject("InternetExplorer.Application")
Sheets("TMK").Select
TMKValue = Range("P4").Value
lLastRow = 200
Start_Row = 4

For i = Start_Row To lLastRow
ie.Visible = True
ie.navigate "http://www.hawaiipropertytax.com/Search/GenericSearch.aspx?mode=PARID"
Do While ie.busy: DoEvents: Loop
Do While ie.ReadyState <> 4: DoEvents: Loop
ie.Document.all.Item("inpParid").Value = TMKValue
ie.Document.all.Item("btSearch").Click
Do While ie.busy: DoEvents: Loop
Do While ie.ReadyState <> 4: DoEvents: Loop

With ie.Visible = True
ie.Document.all.Item("inpParid").Select
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{ENTER}", True
End With

Do While ie.busy: DoEvents: Loop
Do While ie.ReadyState <> 4: DoEvents: Loop

ie.Document.all.Item("Values - 2011 Assessment Year").Click
Do While ie.busy: DoEvents: Loop
Do While ie.ReadyState <> 4: DoEvents: Loop


' Not sure how to just copy the Parcel Summary Totals line only, but I am willing to copy the whole thing in a separate tab and then write something else to clean it up and bring it over

Next
End Sub

 

[hiker95]

pablop,

Welcome to the MrExcel forum.

Using SendKeys like you are trying to do, can be a problem because of timing/cycle of events on the site.

Look into:

Automation of an system issue.
http://www.vbaexpress.com/forum/showthread.php?t=35623
there's a great support forum over there also and feel free to pm me here or there (Cameronsdad is my name over there) if you go that route and have any questions

check out autoit:
http://autoitscript.com/autoit3
it's a free, high level basic based programming language that makes working with all areas of the windows API pretty simple.
including sending directly to any standard control.
Easy to learn BASIC-like syntax
Simulate keystrokes and mouse movements
Manipulate windows and processes
Interact with all standard windows controls
Scripts can be compiled into standalone executables
Create Graphical User Interfaces (GUIs)
COM support
Regular expressions
Directly call external DLL and Windows API functions
Scriptable RunAs functions
Detailed helpfile and large community-based support forums
Compatible with Windows 2000 / XP / 2003 / Vista / 2008 / Windows 7 / 2008 R2
Unicode and x64 support
Digitally signed for peace of mind
Works with Windows Vista's User Account Control (UAC)

 

[Norie]

You don't need to use SendKeys for this sort of thing.

You've already automated IE so you can just use that to submit the form, enter values etc using code.

You can also extract data from any tables etc on the page.

Is it just the totals line you need from 'Values - 2011 Assesment Year'?

 

[Norie]

Here's some code, it's pretty rough but it works.

Sub Tax_Property_Value_Grabber()
Dim ie As Object, lLastRow As Integer, i As Integer, TMKValue As String
Dim rngTMK As Range
Dim doc As Object
Dim lnk As Object
Dim ele As Object
Dim tbl As Object
Dim rw As Object
Dim btn As Object
    Set ie = CreateObject("InternetExplorer.Application")
    Set rngTMK = Sheets("TMK").Range("P4")
    ie.Visible = True
    While rngTMK.Value <> ""
        With ie
            .navigate "http://www.hawaiipropertytax.com/Search/GenericSearch.aspx?mode=PARID"
            Do While .busy: DoEvents: Loop
            Do While .ReadyState <> 4: DoEvents: Loop
            
            Set btn = .document.getelementbyid("btAgree")
            If Not btn Is Nothing Then
                btn.Click
                Do While .busy: DoEvents: Loop
                Do While .ReadyState <> 4: DoEvents: Loop
            End If

            Set doc = ie.document
            doc.all.Item("inpParid").Value = rngTMK.Text
            doc.all.Item("btSearch").Click
            Do While .busy: DoEvents: Loop
            Do While .ReadyState <> 4: DoEvents: Loop
            Set lnk = doc.getelementbyid("rowLink")
            lnk.Click
            Do While .busy: DoEvents: Loop
            Do While .ReadyState <> 4: DoEvents: Loop
            ' find Assesment button
            For Each ele In doc.getelementsbytagname("TR")
                If ele.Title = "Values - 2011 Assessment Year" Then
                    Exit For
                End If
            Next ele
            Set lnk = ele.all(2)
            lnk.Click
            Do While .busy: DoEvents: Loop
            Do While .ReadyState <> 4: DoEvents: Loop
            ' find totals row
            
            For Each ele In doc.getelementsbytagname("TD")
                If ele.innertext = "Parcel Summary Totals" Then
                    Set rw = ele.ParentElement
                    Exit For
                End If
            Next ele
            ' put values on total row on worksheet to the right of parcel ID
            For i = 1 To rw.Cells.Length
                rngTMK.Offset(, i).Value = rw.Cells(i - 1).innertext
            Next i
            
        End With
        Set rngTMK = rngTMK.Offset(1)
    Wend
    Set ie = Nothing
    
End Sub

 

[pablop]

Unbelievable. Thank you so much for your help. I guess I need to know more about HTML to understand how to search and find the right elements in the page. Thank you again.

 

[Norie]

So did that code work OK?

I only tested it with the 2 IDs you posted.

 

[pablop]

It worked great! I only had to do a small modification to handle cases when the TMK was not found. I did an errorhander resume next for those cases. Thank you very much.

 

[Norie]

What happens on the webpage when the TMK isn't found?

Is there some sort of message, eg 'TMK not found'?

Just wondering really, there might be another way to deal with it other than On Error.