I am trying to automate a program that looks up the property tax returns for specific parcels of land. The website I am using to do so is here.
In this example, the parcel ID that I am looking up is 648-30-013.
When I search for that parcel, I arrive at a page with a link that I need to click. The html for the link is below:
<a href='javascript:SubmitThisForm("General.asp", "64830013");'>648-30-013</a>
I've tried using the following code to click the link, but have not been successful. The section I am having trouble with is the last section 'click on parcel ID link. The code works fine when stepped through, but when execucted the objCollection variable does not populate.
'Declare Variables
' Counter variable
Dim i As Integer
' Internet explorer variables
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object
Dim lookupURL As String
' Excel variables
Dim currentParcel As String
Dim year As Integer
Dim jobs As String
Dim completedLoops
Dim totalLoops
' Input variables
'These are the counter variables that loop through your lookup data; where to start and end and how long to loop for
completedLoops = 0
totalLoops = 1
lookupURL = "http://fiscalofficer.cuyahogacounty.us/AuditorApps/real-property/REPI/default.asp"
'Set value of current lookup based on a starting value and # of loops completed
currentParcel = "648-30-013"
Application.StatusBar = "Executing " & completedLoops & " of " & totalLoops & " loops"
' Establish Internet Exploere Instance
' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
' Navigate to website
IE.Navigate lookupURL
' Wait while IE loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
' Click on 'By Parcel #' tab
' Create a list of all the <div> tags on the page
Set objCollection = IE.document.getElementsByTagName("div")
' Loop through all <div> tags to find the one with the correct name and input current search term into tag
i = 0
Do While i < objCollection.Length
If objCollection(i).ID = "tabTabdhtmlgoodies_tabView2_1" Then
Set objElement = objCollection(i)
objElement.Click
Exit Do
End If
i = i + 1
Loop
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
' Input parcel ID
Set objCollection = IE.document.getElementsByTagName("input")
i = 0
Do While i < objCollection.Length
If objCollection(i).Name = "parcelNum" Then
objCollection(i).Value = currentParcel
Exit Do
End If
i = i + 1
Loop
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
' Click on search button
Set objCollection = IE.document.getElementsByTagName("input")
i = 0
Do While i < objCollection.Length
If objCollection(i).Name = "b_2" Then
Set objElement = objCollection(i)
objElement.Click
Exit Do
End If
i = i + 1
Loop
Application.Wait DateAdd("s", 5, Now)
' Click on parcel ID link
Set objCollection = IE.document.getElementsByTagName("a")
Debug.Print objCollection.Length
i = 0
Do While i < objCollection.Length
If objCollection(i).innerText = currentParcel Then
Debug.Print objCollection(i).innerText
Set objElement = objCollection(i)
objElement.Click
Exit Do
End If
i = i + 1
Loop
Please let me know if there are better ways to click on this link.
This worked for me.
If you're doing any amount of web scraping then it's much cleaner to abstract as much of the "guts" as you can into utility methods which you can re-use and which help de-clutter your main code, so you can more easily focus on the "what" versus the "how".
Sub TT()
Dim i As Integer
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object
Dim lookupURL As String, div As Object, el As Object
Dim currentParcel As String
Dim year As Integer
Dim jobs As String
Dim completedLoops
Dim totalLoops
completedLoops = 0
totalLoops = 1
lookupURL = "http://fiscalofficer.cuyahogacounty.us/AuditorApps/real-property/REPI/default.asp"
currentParcel = "648-30-013"
Application.StatusBar = "Executing " & completedLoops & " of " & totalLoops & " loops"
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate lookupURL ' Navigate to website
WaitFor IE
Set div = IE.document.getelementById("tabTabdhtmlgoodies_tabView2_1")
If Not div Is Nothing Then
div.Click
WaitFor IE
' Input parcel ID
If PopulateInputByName(IE, "parcelNum", currentParcel) Then
Application.Wait DateAdd("s", 2, Now)
Set el = GetNamedInput(IE, "b_2")
If Not el Is Nothing Then
el.Click
Application.Wait DateAdd("s", 5, Now)
Set el = GetLinkByText(IE, currentParcel)
If Not el Is Nothing Then el.Click 'done!
Else
MsgBox "Input 'b_2' not found!"
End If
Else
MsgBox "ParcelNum input not found!"
End If
Else
MsgBox "Tab div not found!"
End If
End Sub
'*** Begin utility functions ***
'utility: wait until page has loaded
Sub WaitFor(IE As Object)
Do While IE.ReadyState < 4 Or IE.Busy
DoEvents
Loop
End Sub
'utility: find a named input and set its value: return True if this succeeded
Function PopulateInputByName(IE, theName, theValue) As Boolean
Dim el As Object, rv As Boolean
Set el = GetNamedInput(IE, theName)
If Not el Is Nothing Then
el.Value = theValue
rv = True
End If
PopulateInputByName = rv
End Function
'utility: find a named input
Function GetNamedInput(IE, theName) As Object
Dim objColl As Object, el As Object, rv As Object
Set objColl = IE.document.getElementsByTagName("input")
For Each el In objColl
If el.Name = theName Then
Set rv = el
Exit For
End If
Next
Set GetNamedInput = rv
End Function
'utility: click link with specific text
Function GetLinkByText(IE, theText) As Object
Dim objColl As Object, el As Object, rv As Object
Set objColl = IE.document.getElementsByTagName("a")
For Each el In objColl
If el.innerText = theText Then
Set rv = el
Exit For
End If
Next
Set GetLinkByText = rv
End Function