Converting postcodes to longitudes and latitudes via Mappoint 2006 in VBA

The Poorhouse recently had to convert thousands of UK postcodes into their equivalent latitudes and longitudes. Much as atlases plus rulers are fun, it got tedious quickly. Luckily there was a copy of Microsoft Mappoint Europe 2006 lying around nearby, and it turns out it is pretty much super easy to do using its API.

The program was developed in Microsoft Access to make it easy to feed a decent sized amount of data into the resulting application, but you could do it via any sort of VBA-enabled program at the very least. First thing to do is to include a reference to the Mappoint object library. In the VBA screen, using the Tools -> References menu, ensure you have a tick in the relevant box. In this case it's called "Microsoft MapPoint 13.0 Object Library (Europe)".

Now you have easy access to all the objects you need.

Create and prepare variables to hold the Mappoint objects Application, Map, Location, and FindResults. The names are pretty much self-explanatory.

Dim objApp As New MapPoint.Application
Dim objMap As MapPoint.Map
Dim objLoc As MapPoint.Location
Dim objFR As MapPoint.FindResults

You can use the ActiveMap property of the Mappoint Application to doing your postcode searching on.

Set objMap = objApp.ActiveMap

Now the one-liner that actually does the key work. The Mappoint Map object contains a method called FindAddressResults. Feed it an address and it will return set of Location objects that it considers are matches for the address. This is good, because a Location object is exactly what you need to get information on manipulate a specific place within Mappoint.

The syntax is:

object.FindAddressResults([Street], [City], [OtherCity], [Region], [PostalCode], [Country])

Here we were just interested in finding a location as a result of a postcode, so the first few parameters were irrelevant. To search for, say, the postcode "SW2 3BL", you could therefore theoretically do:

Set objLoc = objMap.FindAddressResults(, , , , "SW2 3BL", geoCountryUnitedKingdom)(1)

Note that this version of Mappoint does not appear to be able to find postcodes in Jersey, Guernsey or the Isle of Man, so there's no point in looking them up.

Hopefully, if successful, you will have been returned a single valid Location, hence the above setting the Location object called "objLoc" to the first object in the collection returned by FindAddressResults. You can't always guarantee this will be the case though, so even though it's a tad more typing you can use an intermediate FindResults step to check if you're going to get a valid result with the above code.

The FindResults object is a collection of Locations (or Pushpins) that has a property called ResultsQuality that, believe it or not, tells you the perceived quality of the results Mappoint has given you for your search. If it is a 1, or the constant "geoFirstResultGood", you are largely good to go and use this for whatever you want. Otherwise you may want to alert your user / program / whatever that you may have found nothing, nothing very precise or an ambiguous multitude of results rather than use the first result as a definitive source.

Here's one way of checking it, rather than risking that everything will be OK as is the case with the above code:

    Set objFR = objMap.FindAddressResults(, , , , "SW2 3BL", geoCountryUnitedKingdom)

        Select Case objFR.ResultsQuality
            Case geoFirstResultGood
                ' go ahead and use it
            Case geoAmbiguousResults
                ' warn ambiguous
            Case geoNoGoodResult
                ' warn no good result found
            Case geoNoResults
                ' warn no results at all found
        End Select

Once you have established you have got a good enough result to use, simply grab it as a Location object, and use the in-built Longitude and Latitude properties to get the relevant figures you need. For example, to display them in an annoying little message-box:

Set objLoc = objMap.FindAddressResults(, , , , "SW2 3BL", geoCountryUnitedKingdom)(1)
Msgbox "Latitude of SW2 3BL is " & objLoc.Latitude & ". Longitude is " & objLoc.Longitude

Not so hard is it! So the final Poor-app simply took an Access recordset of postcodes, looped them through the above process, and recorded the results into a second recordset for later review. Much more fun that Googlemapping 5000 locations one by one.


Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <blockquote> <del>
  • Lines and paragraphs break automatically.
  • You may post code using <code>...</code> (generic) or <?php ... ?> (highlighted PHP) tags.
  • You may use [acidfree:xx] tags to display acidfree videos or images inline.
  • Images can be added to this post.

More information about formatting options

This question is for testing whether you are a human visitor and to prevent automated spam submissions.
7 + 2 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.