PDA

View Full Version : Challenge... Very difficult coding HTML knowledge required Please Help!!



jmutsche
08-22-2014, 09:04 AM
Hello Experts,
I hope you are intrigued by this. I have recently taken a great interest in data scraping, and am hoping to get some help.
Website http://www.naco.org/counties/pages/citysearch.aspx?zip=84108


piece of source code I believe is needed:


<form action="zip_res.cfm" method="GET">
<input type="text" name="zip" size="7" maxlength="5">
<input type="Submit" value="Search for County">
</form>


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Range("zipcode").Row And Target.Column = Range("zipcode").Column Then
Dim ie As New InternetExplorer

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
'ie.navigate "http://quickfacts.census.gov/cgi-bin/qfd/lookup?place=" & Range("zipcode").Value
ie.navigate "http://www.naco.org/counties/pages/citysearch.aspx?zip=" & Range("zipcode").Value
Do
DoEvents
Dim doc As HTMLDocument
myzip = Range("zipcode").Value

Loop Until ie.readyState = READYSTATE_COMPLETE


zip.Value = myzip
ie.document.getElementsByName("zip").Item.Click






sdd = doc.getElementsByTagName("tbody")(2).innerText


lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Cells(lr + 1, 1) = sdd

End If
End Sub




I am struggling to fill in the form and click the submit button on the webpage. From there I think I can figure out how to loop through the results to get what I want. Can anyone help me. I have tried everything I can think of. I am just doing this for fun, but I need to understand what I am missing. I have tried all different variety's of getelements and nothing is working. I am a bit of a novice to html.

Thanks,

Aussiebear
08-23-2014, 03:44 AM
I take it you recognize tht this is a vba site not HTM.

jmutsche
08-23-2014, 04:45 AM
I take it you recognize tht this is a vba site not HTM.


hmmmm this looks live VB to me

[CODE][Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Range("zipcode").Row And Target.Column = Range("zipcode").Column Then
Dim ie As New InternetExplorer

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
'ie.navigate "http://quickfacts.census.gov/cgi-bin/qfd/lookup?place=" & Range("zipcode").Value
ie.navigate "http://www.naco.org/counties/pages/citysearch.aspx?zip=" & Range("zipcode").Value
Do
DoEvents
Dim doc As HTMLDocument
myzip = Range("zipcode").Value

Loop Until ie.readyState = READYSTATE_COMPLETE


zip.Value = myzip
ie.document.getElementsByName("zip").Item.Click






sdd = doc.getElementsByTagName("tbody")(2).innerText


lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Cells(lr + 1, 1) = sdd

End If
End Sub

/CODE]

westconn1
08-23-2014, 06:02 AM
there are several errors in your code, but i am unable to relate the code in any way to the link above, maybe because one frame is failing to load due to server timeout

what is a valid value for zipcode?



zip.value =
should error unless an object zip has been created elsewhere and is in scope within this procedure, but assuming it is a text box within the web page this can not be so


ie.document.getElementsByName("zip").Item.Click the return value from getelementsbyname should be a collection, so only a single element from the collection should be clicked

jmutsche
08-23-2014, 09:01 AM
there are several errors in your code, but i am unable to relate the code in any way to the link above, maybe because one frame is failing to load due to server timeout

what is a valid value for zipcode?


should error unless an object zip has been created elsewhere and is in scope within this procedure, but assuming it is a text box within the web page this can not be so

the return value from getelementsbyname should be a collection, so only a single element from the collection should be clicked

so should it be getelement instead of elements? I am fairly new to data scraping if you look on that website what my goal is to populate the zipcode value with a zipcode from the excel file and go get the city county and state from that webpage. I included the source code that I thought was used, but I could be totally in the wrong. I really appreciate your help on this.

Thanks!!