stevieccs
11-07-2017, 08:30 AM
Good Morning All,
I've been working on a project and I'm just stuck now!! My objective is to have a functioning user form where I can input the "House Number", and "Zip" in a text box.. Press search.. and.. Wala... results are displayed in a list. select the correct one press copy then reset and repeat.. The results need to display a list of street names with the house numbers in that zip.
The site I'm using is melissadata /lookups/zipnumber" Any suggestions to another would be greatly appreciative seeing a limit of 200 searches per day..
I've found many helpful youtube vids and forms including this one to figure what is needed to get the fundamentals of my project. I am not a coder.. but I'm trying my best to have it do what I need it to do.
What I have now..
Module's
Sub querystringxml()
Dim xmlpage As New MSXML2.XMLHTTP60
Dim HTMLDOC As New MSHTML.HTMLDocument
xmlpage.Open "GET", .melissadata.com /lookups/ zipnumber.asp?number=100&zip=13440&c=10&l=U, False
xmlpage.send
HTMLDOC.body.innerHTML = xmlpage.responseText
processhtmlpage HTMLDOC
End Sub
Option Explicit
Sub processhtmlpage(htmlpage As MSHTML.HTMLDocument)
Dim htmltable As MSHTML.IHTMLElement
Dim htmltables As MSHTML.IHTMLElementCollection
Dim htmlrow As MSHTML.IHTMLElement
Dim htmlcell As MSHTML.IHTMLElement
Dim rownum As Long, colnum As Integer
Set htmltables = htmlpage.getElementsByClassName("Tableresultborder")
For Each htmltable In htmltables
Worksheets.Add
rownum = 1
For Each htmlrow In htmltable.getElementsByTagName("tr")
'debug.Print vbTab & htmlrow.innerText
colnum = 1
For Each htmlcell In htmlrow.Children
Cells(rownum, colnum) = htmlcell.innerText
colnum = colnum + 1
Next htmlcell
rownum = rownum + 1
Next htmlrow
Next htmltable
End Sub
This is my user form that I've made, I've tried linking the code to the search button but nothing works.
I know that the code is incomplete and isn't properly routed in the correct place. I'm just lost any help would be appreciated..
20879
I've been working on a project and I'm just stuck now!! My objective is to have a functioning user form where I can input the "House Number", and "Zip" in a text box.. Press search.. and.. Wala... results are displayed in a list. select the correct one press copy then reset and repeat.. The results need to display a list of street names with the house numbers in that zip.
The site I'm using is melissadata /lookups/zipnumber" Any suggestions to another would be greatly appreciative seeing a limit of 200 searches per day..
I've found many helpful youtube vids and forms including this one to figure what is needed to get the fundamentals of my project. I am not a coder.. but I'm trying my best to have it do what I need it to do.
What I have now..
Module's
Sub querystringxml()
Dim xmlpage As New MSXML2.XMLHTTP60
Dim HTMLDOC As New MSHTML.HTMLDocument
xmlpage.Open "GET", .melissadata.com /lookups/ zipnumber.asp?number=100&zip=13440&c=10&l=U, False
xmlpage.send
HTMLDOC.body.innerHTML = xmlpage.responseText
processhtmlpage HTMLDOC
End Sub
Option Explicit
Sub processhtmlpage(htmlpage As MSHTML.HTMLDocument)
Dim htmltable As MSHTML.IHTMLElement
Dim htmltables As MSHTML.IHTMLElementCollection
Dim htmlrow As MSHTML.IHTMLElement
Dim htmlcell As MSHTML.IHTMLElement
Dim rownum As Long, colnum As Integer
Set htmltables = htmlpage.getElementsByClassName("Tableresultborder")
For Each htmltable In htmltables
Worksheets.Add
rownum = 1
For Each htmlrow In htmltable.getElementsByTagName("tr")
'debug.Print vbTab & htmlrow.innerText
colnum = 1
For Each htmlcell In htmlrow.Children
Cells(rownum, colnum) = htmlcell.innerText
colnum = colnum + 1
Next htmlcell
rownum = rownum + 1
Next htmlrow
Next htmltable
End Sub
This is my user form that I've made, I've tried linking the code to the search button but nothing works.
I know that the code is incomplete and isn't properly routed in the correct place. I'm just lost any help would be appreciated..
20879