Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: Macro to conduct web query and return results
Hey Guys,
I am only knowledgable on the basics of macros so I have no clue on how to interact with the web. Basically what I require is thus: I have a file of postcodes in Australia which i obtained through: http://www1.auspost.com.au/postcodes/index.asp?sub=2 i downloaded the second option, the postcode booklet database now i need a macro to go through this file and find every different city/suburb/town mentioned then for each one mentioned, send it and the corresponding state/territory (so as to cater for same place names in different states) to the following website: http://www.ga.gov.au/map/names/ obviously the name will go into the "place name" the place type will always be "towns and localities" and the state will correspond with the state in the spreadsheet, although the state part on query is not important.. only on the results found. after the data is put into the page, the site will return results I would like the macro to find the matches and copy them into an excel sheet.. all official results under the "status" field, that is. Why I need this is so I can create a complete list of all latitude and longitude coordinates for every location in Australia so then I can calculate distances between places. Is anybody able to help inform me as to how best go about this, or is there a macro out there that i can slightly modify or tell what I want it to do and will do it? If anybody can help, it would very very much appreciated. Thank you in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to conduct web query and return results
Clinton,
Split it up into stages. First get the web query with a static query to http://www.ga.gov.au/map/names/ working. If you look at the URL after you have submitted a place name, you see something like http://www.ga.gov.au/bin/gazm01?plac...pe=0&state=NSW So you can send that in your query and skip the first step of actual entering the data. See where the data is and decide how to handle multiple results, no result, error, correct, single result. Then you need to loop through all the place names in your list, submit the above (adjusted) URL each time, call .Refresh and process the results '<Warning air-code Dim Cell as range With Worksheets(1).QueryTables(1) 'Assumes you a column of place names called "TownList", with the State in the next column for each cell in worksheets(2).Range("TownList") .Connection = "URL;http://www.ga.gov.au/bin/gazm01?placename=" & Cell.Value" & "&placetype=0&state=" & Cell.Offset(0,1).Value .Refresh BackgroundQuery:=False MsgBox "Lat: " & .Destination.Offset(14,4).Value & vbNewLine & "Long: " & .Destination.Offset(14,5).Value Next End With NickHK "Clinton M James" ... Hey Guys, I am only knowledgable on the basics of macros so I have no clue on how to interact with the web. Basically what I require is thus: I have a file of postcodes in Australia which i obtained through: http://www1.auspost.com.au/postcodes/index.asp?sub=2 i downloaded the second option, the postcode booklet database now i need a macro to go through this file and find every different city/suburb/town mentioned then for each one mentioned, send it and the corresponding state/territory (so as to cater for same place names in different states) to the following website: http://www.ga.gov.au/map/names/ obviously the name will go into the "place name" the place type will always be "towns and localities" and the state will correspond with the state in the spreadsheet, although the state part on query is not important.. only on the results found. after the data is put into the page, the site will return results I would like the macro to find the matches and copy them into an excel sheet.. all official results under the "status" field, that is. Why I need this is so I can create a complete list of all latitude and longitude coordinates for every location in Australia so then I can calculate distances between places. Is anybody able to help inform me as to how best go about this, or is there a macro out there that i can slightly modify or tell what I want it to do and will do it? If anybody can help, it would very very much appreciated. Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to check data from excel list against access query and return value back to excel | Excel Worksheet Functions | |||
How do I conduct linear regression in Excel with more than 16 x's | Excel Worksheet Functions | |||
Solver GUI works fine, but the VBA macro won't return any results | Excel Programming | |||
Macro to write to webpage & query results | Excel Programming | |||
How can I list the results of my macro without overwritng previous results? | Excel Programming |