HELP: Macro to conduct web query and return results

[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.

 

[NickHK]

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?placename=Sydney&placetype=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