getting data from web app

D

dstiefe

I was wondering if there was a way to get to data through excel VBA that is:

1) the data is in a "http://www..... .aspx" URL

when I go the URL above (and I didn't type the entire URL)

I get a list of names like this


Brian Ackerley, QKA Vanguard Phoenixville PA
Michelle R. Ackerman, QKA First Merit Bank Akron OH
Thomas R. Ackmann Baden Retirement Plan Services Fort Wayne IN
Lucian B. Acuff Acuff & Associates, Inc. Brentwood TN
Beau C. Adams, QPA, QKA The Benefit Consultants Group Delran NJ


The name is a hyperlink to another HTML page that pops up...but I don't see
an URL address.

is there anyway to write VBA to automate this process somehow?

Thank you
 
J

joel

People say if i can't get the data off the internet, nobody can. Her
is a simple example. Just perform a googled search for "Joel I
explorer vba". I numer of the hnits are people I help either a
microsoft or codecage help sites.

Sub GetZipCodes()


ZIPCODE = InputBox("Enter 5 digit zipcode : ")

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://zip4.usps.com/zip4/citytown_zip.jsp"

'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4 Or _
IE.busy = True

DoEvents
Loop

Set Form = IE.document.getElementsByTagname("Form")

Set zip5 = IE.document.getElementById("zip5")
zip5.Value = ZIPCODE


Set ZipCodebutton = Form(0).onsubmit

Form(0).submit
Do While IE.busy = True
DoEvents
Loop

Set Table = IE.document.getElementsByTagname("Table")
Location = Table(0).Rows(2).innertext
IE.Quit
MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location)


End Su
 
Top