Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I find all zip codes from the web?
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...t=Lo ok+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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I find all zip codes from the web?
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...t=Lo ok+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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I find all zip codes from the web?
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...t=Lo ok+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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I find all zip codes from the web?
On Sep 24, 5:18 pm, IntricateFool
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:D. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I find all zip codes from the web?
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 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:D. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find all item codes for all items under one category | Excel Discussion (Misc queries) | |||
How can I find the alpha numeric codes in excel please,Thank you. | Excel Discussion (Misc queries) | |||
Cntrl find - cannot locate zip codes that start with zero. Possibl | Excel Discussion (Misc queries) | |||
find top 25 codes | Excel Worksheet Functions | |||
How to find out country-dependent Format Codes | Excel Programming |