Hi,

I have used MapPoint to tell me the driving distance between the branch and a customer in the past.

My question is, if I have a list of say 300+ postcodes is there any way I can feed this data into MapPoint and it would tell me the distance between each postcode (customer and branch - the branch would all be the same)

Is this possible or would I have to do it one at a time?

This is a pretty easy thing to do with a macro automating MapPoint. There are a couple things related to this in the MapForums Downloads section ($99), but nothing that does exactly what you need.

MapForums Plus - MapForums Plus Downloads at MapForums.com (http://www.mapforums.com/mapforums-plus-downloads/)

http://www.mapforums.com/straight-line-distance-between-zip-codes-10393.html

See the Distance between zip codes tool in:
http://www.mapforums.com/mappoint-power-tools-spreadsheet-8080.html

I'm not so familiar with Richard's tool, but I think MileCharter fits the bill and it looks like there a tons of options. There's a free trial.

MileCharter (https://www.plimus.com/jsp/buynow.jsp?contractId=1730362&referrer=ericwfrost)

hope this helps,
Eric

IC-DistanceAnalysis - Excel Addin
BASIC(1) or Professional(2)
Requires Microsoft® Excel (2003,2007 or 2010(32Bit) and MapPoint.

This ExcelAddIn allows you to calculate distances between locations and driving times. The results are displayed as matrices in an Excel sheet. You can also calculate the average speeds, complete round trip and air distances. You can define vehicle profiles with different speed and road preference settings. The PLUS version enables dedicated perimeter distance and driving time calculations and further analysis tools.

WebHelp (http://wh-en.mappoint-tools.info/GA/src/html/DistanceAnalysis/IC-DistanceAnalysis.htm)

You can download (http://profit100.eu/download/ic-business-tools.html) this Add-In.


If you send us an Email and we will send you an Excelsheet with all Postcodes (geocoded) of USA.


Kind regards
Manfred

Hi,

Would this work for the UK postcodes?

Also, how would I feed the data into the addin, can it be done via excel?

is this "as the crow flys" or driving distance?

Regards

Paul

Yes - Would this work for the UK postcodes?
Yes - Also, how would I feed the data into the addin, can it be done via excel?
Both (you can select) - is this "as the crow flys" or driving distance?

Please read the HelpFile (http://wh-en.mappoint-tools.info/GA/src/html/DistanceAnalysis/IC-DistanceAnalysis.htm)

Kind regards
Manfred

Hi,

Thanks for your quick reply.

Will i need a list of UK Postcodes?

Regards

Paul

If you post a sample file of your postcodes in Excel (from and to) I can mock up the the macro code. Are you familiar with Excel VBA?

Hi Eric

I have attached an example.

I do have experiance of VBA in Excel but it very basic, I understand it but wouldnt be able to write code.

The site (RG224DQ) would be the "from" and all the post codes underneath would be the "to"

Regards

Paul

File now included - i was trying to upload an excel file previously, now zipped

You don´t need VBA.
Calculation time was ~ 5 minutes.

See the result (attachment)

Kind regards
Manfred

Here's the basic code. Except for the straight line distance function DistMiles which I threw in as a bonus, you can see it's very simple.


It just works its way down the spreadsheet until in encounters a blank line.


It creates a Location object - lStart for the origin, and lEnd for the Destination.


It adds these objects to the route's waypoint list, and then calculates the route.


To run the macro, go to View | Macros.



Option Explicit

Sub CalculateDistances()
Dim oApp As MapPoint.Application
Dim oMap As MapPoint.Map
Dim oRoute As MapPoint.Route

Set oApp = CreateObject("MapPoint.Application.EU")
Set oMap = oApp.ActiveMap
Set oRoute = oMap.ActiveRoute

Dim lStart, lEnd As MapPoint.Location
Set lStart = oMap.FindPlaceResults(Cells(1, 2))(1) 'cell B1

Dim row As Integer
row = 4

Do While Cells(row, 1) <> ""

Set lEnd = oMap.FindPlaceResults(Cells(row, 3))(1)
Cells(row, 4) = DistMiles(lStart.Latitude, lStart.Longitude, lEnd.Latitude, lEnd.Longitude)

oRoute.Clear
oRoute.Waypoints.Add lStart, "start"
oRoute.Waypoints.Add lEnd, "end"
oRoute.Calculate
Cells(row, 5) = oRoute.Distance
row = row + 1
Loop
oMap.Saved = True
oApp.Quit
End Sub

You could add lots of bells and whistles such as adding pushpins and drawing lines and circles on the map, let me know if you would like to pursue this and I could help with it.

The straight line DistMiles function is in another module and involves a lot of trigonometry, but you don't have to understand it to use the function :-) I believe that code is from Ed William's Aviation Formulary --
Aviation Formulary V1.46 (http://williams.best.vwh.net/avform.htm)

I did not double-check to see if the output is in miles or kilometers, but you use miles in the UK, correct? If necessary you might need to adjust the output. This could also be done in code
oApp.Units = geoKm
-- or --
oApp.Units = geoMiles
and
1 mile = 1.609344 kilometers

Let me know if you have any questions.

Have fun and Happy Mapping!
Eric

Hi,

I have tried to run the macro in excel but i get this error:
"user-defined type not defined" on line oApp as Mappoint.application

Any ideas what this means?

I am unsure at the moment if I need Miles or Kilometers but now you have supplied the code I will be able to change thanks.

(Ps. Thank you for all your help with this code!)

it appears i have to reference it, which should I select:

Mappoint 18.0 Object Libary (Europe)
Mappoint Control 18.0
Mappoint Utilieies 1.0 Type Libary

This one --

Mappoint 18.0 Object Libary (Europe)

Were you able to open the spreadsheet in the attachment? It's Excel 2007, but there's nothing that specifically requires that version, so I could save down to Excel 2003 easily.

Eric

Hi Eric

This is fantastic!! I have managed to get it to work!

This may be a silly question but I assume it will only work on computers with mappoint installed?

Hi Eric

Out of interest what do you mean by this?

"You could add lots of bells and whistles such as adding pushpins and drawing lines and circles on the map"

Do you mean pushpins for each customer?
What are drawing lines and circles?

Regards

Paul

Yes, that's correct. If you have lat/lons or a postcode database with lat/lon lookup, you could calculate the straight line distance WITHOUT MapPoint, but currently the program is using MapPoint for two things:


Getting Lat/Lon for each postcode
Calculating the driving distance


Eric

Do you mean pushpins for each customer?
What are drawing lines and circles?


Yes, pushpins for both the central location and all the customers. Customer pushpins could be color-coded by driving distance. e.g. all with less then 10 mile driving distance are green, 10-50 miles are yellow, over 50 could be red, etc. Customers could also be color-coded based on some other variable such as prospect or customer type.

Lines could be the route from the center to each customer. Although MapPoint can only depict one route object at a time, the routes can be converted to drawing lines.

Circles could be radii such as a 20, 50, and 100 mile rings around the central location.

I don't know, use your imagination :arabia: and if you're not sure if it can be accomplished, feel free to ask.

Eric

Hi Eric

I do need to report it in kilometers:

How do I do this:
oApp.Units = geoKm
-- or --
oApp.Units = geoMiles
and
1 mile = 1.609344 kilometers


Can you post the revised code for me please?