How would I go about looking through a list of counties and retrieving all of
the countys' respective zip codes through a web site such as:

http://www.getzips.com

So I would have different territories set up in a reference area (e.g cells
A2:S20 contain a list of counties - column A would containTerritory1,
Territory2, Territory3 -> Territory19 and columns B through S would contain
each territories respective counties). Below (starting in cell A22) I would
like to list out all zip codes, city and state and county names (all data
that is shown from getzips.com) for each of the counties. The script would
look through each county on the getzips.com website and output all zip codes
for every county listed in the reference area.

So if I wanted to lookup "bucks" county "pennsylvania" I could do so by
following:

http://www.getzips.com/CGI-BIN/ziplo...mit=Look+It+Up

What would be the easiest way to go about doing this? I have several
thousand counties that I would need this information for and I know this is
possible with a script... Just not sure how to even begin.

Please help!

Here is a quick one to hit getzips.com and bring back the data for
Alamance county NC.
Sub this()
Dim cty As String, state As String
cty = "Alamance"
state = "NC"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://getzips.com/CGI-BIN/ziplook.exe?What=3&County=" &
_
cty & "&State=" & state & "&Submit=Look+It+Up" _
, Destination:=Range("A1"))
.Name = cty & " " & state
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

You can just adjust the values assigned to the variables with a value
from a range reference. Example:
state=Range("S2").Text

A simple loop could be added to cycle through all of the records in
your sheet and place the data in the approriate place, instead of just
A1 which is what the above does.

If you need help accomplishing this, let me know. I'll try to get
back on here later this evening and have a closer look at what you are
describing.
IntricateFool wrote:
> How would I go about looking through a list of counties and retrieving all of
> the countys' respective zip codes through a web site such as:
>
> http://www.getzips.com
>
> So I would have different territories set up in a reference area (e.g cells
> A2:S20 contain a list of counties - column A would containTerritory1,
> Territory2, Territory3 -> Territory19 and columns B through S would contain
> each territories respective counties). Below (starting in cell A22) I would
> like to list out all zip codes, city and state and county names (all data
> that is shown from getzips.com) for each of the counties. The script would
> look through each county on the getzips.com website and output all zip codes
> for every county listed in the reference area.
>
> So if I wanted to lookup "bucks" county "pennsylvania" I could do so by
> following:
>
> http://www.getzips.com/CGI-BIN/ziplo...mit=Look+It+Up
>
> What would be the easiest way to go about doing this? I have several
> thousand counties that I would need this information for and I know this is
> possible with a script... Just not sure how to even begin.
>
> Please help!

You are awesome! Now if I can just have it loop through a range then i would
be set. any suggestions?

I really would like to learn how to do this, just don't know where to start...

Many Thanks!

Chris

"JW" wrote:

> Here is a quick one to hit getzips.com and bring back the data for
> Alamance county NC.
> Sub this()
> Dim cty As String, state As String
> cty = "Alamance"
> state = "NC"
> With ActiveSheet.QueryTables.Add(Connection:= _
> "URL;http://getzips.com/CGI-BIN/ziplook.exe?What=3&County=" &
> _
> cty & "&State=" & state & "&Submit=Look+It+Up" _
> , Destination:=Range("A1"))
> .Name = cty & " " & state
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .BackgroundQuery = True
> .RefreshStyle = xlInsertDeleteCells
> .SavePassword = False
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .WebSelectionType = xlSpecifiedTables
> .WebFormatting = xlWebFormattingNone
> .WebTables = "3"
> .WebPreFormattedTextToColumns = True
> .WebConsecutiveDelimitersAsOne = True
> .WebSingleBlockTextImport = False
> .WebDisableDateRecognition = False
> .WebDisableRedirections = False
> .Refresh BackgroundQuery:=False
> End With
> End Sub
>
> You can just adjust the values assigned to the variables with a value
> from a range reference. Example:
> state=Range("S2").Text
>
> A simple loop could be added to cycle through all of the records in
> your sheet and place the data in the approriate place, instead of just
> A1 which is what the above does.
>
> If you need help accomplishing this, let me know. I'll try to get
> back on here later this evening and have a closer look at what you are
> describing.
> IntricateFool wrote:
> > How would I go about looking through a list of counties and retrieving all of
> > the countys' respective zip codes through a web site such as:
> >
> > http://www.getzips.com
> >
> > So I would have different territories set up in a reference area (e.g cells
> > A2:S20 contain a list of counties - column A would containTerritory1,
> > Territory2, Territory3 -> Territory19 and columns B through S would contain
> > each territories respective counties). Below (starting in cell A22) I would
> > like to list out all zip codes, city and state and county names (all data
> > that is shown from getzips.com) for each of the counties. The script would
> > look through each county on the getzips.com website and output all zip codes
> > for every county listed in the reference area.
> >
> > So if I wanted to lookup "bucks" county "pennsylvania" I could do so by
> > following:
> >
> > http://www.getzips.com/CGI-BIN/ziplo...mit=Look+It+Up
> >
> > What would be the easiest way to go about doing this? I have several
> > thousand counties that I would need this information for and I know this is
> > possible with a script... Just not sure how to even begin.
> >
> > Please help!
>
>

On Sep 24, 5:18 pm, IntricateFool
<IntricateF...@discussions.microsoft.com> wrote:
> You are awesome! Now if I can just have it loop through a range then i would
> be set. any suggestions?
>
> I really would like to learn how to do this, just don't know where to start...
>
> Many Thanks!
>
> Chris


So, are the counties listed on the rows from B - S? Where are the
states listed? Are those in column A? If so, this should work.
This will cycle through all of the cells in column A (containing the
states) and all of the cells in the applicable row (containing the
counties) and place the return in the first available row in column A,
which will of course place the data in A. If this isn't what you
are after, please let me know. Be sure to test this to make sure it
is what you want before running it on your main data.
Sub this()
Dim cty As String, state As String
Dim BotRow As Long
Dim i As Long, j As Integer
For i = 2 To Cells(65536, 1).End(xlUp).Row
For j = 2 To Cells(i, 1).End(xlToRight).Column
state = Cells(i, 1).Text
cty = Cells(i, j).Text
BotRow = Cells(65536, 1).End(xlUp).Row + 1
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://getzips.com/CGI-BIN/ziplook.exe?
What=3&County=" & _
cty & "&State=" & state & "&Submit=Look+It+Up", _
Destination:=Range("A" & BotRow))
.Name = cty & " " & state
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next j
Next i
End Sub

JW:

I was just cleaning out my mailbox and I found this reponse. I never
realized you responded.

Is there any way you could still help me out with this? I have been doing
this manually over the last month.

I played around with the code you provide and can not seem to get it
working. I have placed a state abbreviation in Column a in the worksheet and
then all the corresponding counties in columns B through S but it is not
working? For example - I am trying to do Texas. I typed "tx" in A1 and have
the counties listed out in B1 to N1. The next line - row 2 has some more info
to lookup --- A2 = "tx" and B2 to J2 there are more counties. And so on, and
so on.

What am I doing wrong here? I will definitely keep my eyes on your replies
this time. If there is anyway I can provide you a sample workbook with what I
am referring to, let me know.

Thanks,
Chris

"JW" wrote:

> On Sep 24, 5:18 pm, IntricateFool
> <IntricateF...@discussions.microsoft.com> wrote:
> > You are awesome! Now if I can just have it loop through a range then i would
> > be set. any suggestions?
> >
> > I really would like to learn how to do this, just don't know where to start...
> >
> > Many Thanks!
> >
> > Chris
>
>
> So, are the counties listed on the rows from B - S? Where are the
> states listed? Are those in column A? If so, this should work.
> This will cycle through all of the cells in column A (containing the
> states) and all of the cells in the applicable row (containing the
> counties) and place the return in the first available row in column A,
> which will of course place the data in A. If this isn't what you
> are after, please let me know. Be sure to test this to make sure it
> is what you want before running it on your main data.
> Sub this()
> Dim cty As String, state As String
> Dim BotRow As Long
> Dim i As Long, j As Integer
> For i = 2 To Cells(65536, 1).End(xlUp).Row
> For j = 2 To Cells(i, 1).End(xlToRight).Column
> state = Cells(i, 1).Text
> cty = Cells(i, j).Text
> BotRow = Cells(65536, 1).End(xlUp).Row + 1
> With ActiveSheet.QueryTables.Add(Connection:= _
> "URL;http://getzips.com/CGI-BIN/ziplook.exe?
> What=3&County=" & _
> cty & "&State=" & state & "&Submit=Look+It+Up", _
> Destination:=Range("A" & BotRow))
> .Name = cty & " " & state
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .BackgroundQuery = True
> .RefreshStyle = xlInsertDeleteCells
> .SavePassword = False
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .WebSelectionType = xlSpecifiedTables
> .WebFormatting = xlWebFormattingNone
> .WebTables = "3"
> .WebPreFormattedTextToColumns = True
> .WebConsecutiveDelimitersAsOne = True
> .WebSingleBlockTextImport = False
> .WebDisableDateRecognition = False
> .WebDisableRedirections = False
> .Refresh BackgroundQuery:=False
> End With
> Next j
> Next i
> End Sub
>
>