Pull Data off the Web

[magelan]

Hi all,

Given a specific website with a modifiable URL

https://tools.usps.com/go/ZipLookupResults...&address2=&city=&state=Select&urbanCode=&postalCode=12345&zip= (https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=2&companyName=&address1=&address2=&city=&state=Select&urbanCode=&postalCode=12345&zip=)

as you can see, you cna modify the getdata for postalcode=xxxxx and put in any zipcode you want. What i need to do is script a program that will run through a list of zipcodes that I have, insert the zipcode into that URL [i think that is the easy part..?] and then pull a specific part of data from the website.

The part that I need is enclosed in a class [i think that makes it easier to pull?] called "std-address" and is that standard line of output that you get from inputting any valid zipcode into the URL. Such as...
<p class="std-address (http://www.vbaexpress.com/forum/)">SCHENECTADY NY</p>


The question is, how do I write the VBA to visit the URL with my given 5 digit zip and then get the city name? Ideally, this would be enclosed in a sub, like..

sub getCityFromZip(zipCode as integer, resultCity as string)
[code to pull up the website with the right zipcode and get the data..

resultCity=the result from above...
end sub

[magelan]

For anyone who is interested [since this seems to not be covered anywhere on the internet and I had to go and figure it outmyself...]



The code I used is like such.




Sub webLookup()
Dim zipCode As Integer
Dim connString As String
zipCode = 12345
connString = "https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode="
connString = connString & "2&companyName=&address1=&address2=&city=&state=Select&urbanCode=&postalCode="
connString = connString & zipCode
connString = connString & "&zip="
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & connString, Destination:=Range("$A$2"))
.Name = "newConn"
.PreserveFormatting = True
.BackgroundQuery = True
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Dim cellvar As Range
Set cellvar = Cells.Find(What:="is...")
Debug.Print cellvar.Offset(1, 0).Value
Sheet1.Cells.Delete
End Sub


This successfully grabs the entire page, finds the "is..." and prints out the line afterwards [which so happens to be the city name] and is easily adaptable to be a modular routine in my code.

[Kenneth Hobs]

I am sure that it has been done many times.

I would probably use a routine similar to this one. http://www.vbaexpress.com/forum/showthread.php?t=33373