I have a list of addresses in excel and want to automate looking them up on the county assessors website and copying the assessed value back to excel

[jflagle]

Ok. Here is the skinny.

I am using excel 2007. i am a mechanical engineer. I have dabble in macros along time ago. fairly sophisticated with arrays and using visual basic. but, that was a long time ago.... i seem to have forgotten most of the details and kind of starting from scratch. i dont know teh vba language at all as i have been reading up on it. i was hoping that someone has done something similar and would be able to share their code and give me some pointers on things to change to get it working for my situation.

I have about 500 to 1000 addresses listed in column A in excel. For each address (e.g. 12312 WINDING), I copy the address from excel and paste it into the website Property Tax Search

the assessed value then pops up on the following screen of indygov.org. i copy the assessed value from this page. then switch back to the excel sheet and paste it into the right spot in column B.

i then copy the next address from column A in excel, go back to the original search web page by hitting back button or the return to search link. and redo the steps above. i did these manually the first time and it took 5 or 6 hours to get through the entire list. i do these on a regular basis.

one thing to note.... sometimes the address does not produce an assesed value. you can see this if you were to enter some random address. it will not return a result. also, in some other cases, the property may have two assessed values. if this is the case it will not return a result either. it displays this on a totally different formatted page that reuqires you to select the property of interest. so, if either of these cases occurs - then this property should just be skipped.


Excel Sheet example below (it does not have all 500 to 1000 addresses). Note - when copying the address over, i manually delete one space between teh address number and street. there are 2 spaces and should only be 1. 2 spaces will yield a bad search on the website. in addition, i truncate the address. I do include the N or S or E or W prefex. i only copy the first part if there are multiple names in teh street. so for example 6465 Potomac would be what i would search for the first one below. notice, i deleted one space and didnt use the square suffix.

any help would be greatly appreciated. i am at a loss on this one and it looks like it would take me hours upon hours if i had to figure this out from step 1.

[TABLE="width: 504"]
<TBODY>[TR]
[TD] Address (column A)[/TD]
[TD]Assessed Value (column B)[/TD]
[/TR]
[TR]
[TD]6465 POTOMAC SQUARE[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1521 N LASALLE ST [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]350 N 13TH AVE [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]530 N HAMILTON AVE [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1226 CENTRAL AVE [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]926 GREER ST [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]769 N WALLACE AVE [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3611 N KENWOOD AVE [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3850 E MICHIGAN ST [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2121 S PENNSYLVANIA ST [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4209 E PLEASANT RUN PKWY S. DR [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1566 CARROLLTON [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]332 W 31ST ST [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1764 MORGAN ST [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1117 N EWING ST [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1400-1406 E WASHINGTON ST [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]666 E WOODRUFF PLACE DR [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]418 N 18TH AVE [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4110 E NAOMI ST [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3108 MANOR CT [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]110 E WASHINGTON ST [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2863 N TALBOTT ST [/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]

[TABLE="width: 531"]
<TBODY>[TR]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

 

[bertie]

I don't see how your bandwidth would affect the code. At each stage we tell the code to wait until the connection is ready.

I have tried adding a one second pause. This will make the code run slower, but see if this helps.

Sub GetAssessmnet()
   Dim ie As Object
   Dim rng As Range              'range to loop through
   Dim strAssessment As String
   Dim url As String             'webpage
   Dim recNumber As Long         'number of records for status bar updates
   Dim recCount As Long          'record being processed
   Dim tmp As String
   
   'set up internet explorer
   url = "http://cms.indygov.org/MyAssessedValue/Search.aspx"
   Set ie = CreateObject("InternetExplorer.Application")
   
   'get the number of records to update status bar
   recNumber = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row - 1 'header row
   
   'loop through the range
   Set rng = Sheets("Sheet1").Range("A2")
   Do Until rng = ""
      recCount = recCount + 1
      Application.StatusBar = "Processing: " & recCount & "/" & recNumber & "...please be patient."
      
      With ie
         .navigate url
         'wait for the page to load
         Do While .Busy
             Application.Wait DateAdd("s", 1, Now)
         Loop
         
         'fix the data
         tmp = Trim(rng.Value)
         tmp = Replace(tmp, "  ", " ")
         
         'enter the address and click the button
         .document.all("ctl00_header_txtPropertyLocation").Value = tmp
         .document.all("ctl00_header_btnView").Click
         
         'wait for the INTERACTIVE page to load
         Do While .Busy
             Application.Wait DateAdd("s", 1, Now)
         Loop
         
         'output
         On Error Resume Next
            strAssessment = .document.all("ctl00_header_txtTotalAssessed2013").Value
         On Error GoTo 0
         If strAssessment = "" Then
            rng.Offset(, 1).Value = "<not found="">"
         Else
            rng.Offset(, 1).Value = strAssessment
         End If
      End With
      
      'get the next value
      Set rng = rng.Offset(1, 0)
      strAssessment = ""
   Loop
   
   'tidy up
   Set rng = Nothing
   Set ie = Nothing
   Application.StatusBar = "Done"
End Sub

As for downloading images search Google for: </not>URLDownloadToFile
As for learning vba,. Hiker's list is as good a place to start as any. See post #2 in this thread:
http://www.mrexcel.com/forum/genera...pplications-training-courses-suggestions.html