Web API

[Kentman]

I'd like to call a web api to geocode some addresses - does anyone know of VBA code or Excel workbook to do this?

 

[DauntingGecko]

Can you explain a bit more what you'd like to do...?

 

[Kentman]

There's a simple api (http://tinygeocoder.com/create-api.php?q=) which takes an address or place as a parameter and pulls back coordinates. I'd like to be able to have a list of places and push them into the api and get the results back in a worksheet so, if I input "Leicester, uk" as the parameter at the URL I would get back the coordinates for Leicester.

I'm OK with VBA but don't know how to go about sending and getting information from the web.

 

[DauntingGecko]

Make a long list of locations (1 per row) and run this code

Option Explicit

Sub GEO_Coder()

    Do While ActiveCell.Value <> ""
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://tinygeocoder.com/create-api.php?q=""" & ActiveCell.Value & """", Destination:=Range(ActiveCell.Offset(0, 1).Address))
            .Name = "create-api.php?q="" & activecell.value & ""_1"
            .Refresh
        End With
        ActiveCell.Offset(1, 0).Select
    Loop

End Sub

Click on the first row before you run. It'll step through each cell (until an empty cell is reached) and will return the value from the site.

 

[Kentman]

DauntingGecko thanks very much that worked great except - there always has to be an except! The results are progressively moved 1 column over so if I have a list of 50 the 50th will be 50 columns to the right.

Could you alter code so that they are in the same column? Much appreciated.

 

[DauntingGecko]

    Do While ActiveCell.Value <> ""
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://tinygeocoder.com/create-api.php?q=""" & ActiveCell.Value & """", _
                     Destination:=Range(ActiveCell.Offset(0, 1).Address))
            .Name = "create-api.php?q="" & activecell.value"
            .RefreshStyle = xlOverwriteCells
            .Refresh
        End With
        ActiveCell.Offset(1, 0).Select
    Loop

 

[Kentman]

Indeed!

Could the result be put into a variable and the contents of the variable then placed into a cell?

 

[DauntingGecko]

* Code amended.

Amazing how one little line causes chaos.

 

[Kentman]

That's brilliant! Thank you.