Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 5 of 5

Distance and Time Traveled ??

This is a discussion on Distance and Time Traveled ?? within the Products: Pushpin Tool, Single State Mapper forums, part of the Map Forums category; I have about 700 sites across the country that I have in a spreadsheet. I have imported them into MP ...

  1. #1
    chashutch is offline Junior Member White Belt
    Join Date
    Aug 2007

    Distance and Time Traveled ??

    I have about 700 sites across the country that I have in a spreadsheet. I have imported them into MP 2004 and they actually all show up. I would like to know if there is an automated way to determine the distance and time between about 50 of these sites (hubs) and the rest of them based on which hubs are designated to support which sites? I would then like to export all of the info back into excel for analysis.


  2. #2
    davidb is offline Senior Member Blue Belt
    Join Date
    Oct 2003

    Re: Distance and Time Traveled ??

    Hi there

    I think this is likely to be quite straightforward in Excel vba. I’m currently trying to improve my vb programming skills and used the following code for a similar application in the UK. Please note that the points were located via their postcodes and I knew all the postcodes were valid (ie. recognised by MP2006) ahead of running the program. The data was input as 2 columns in an excel worksheet with the following format

    Hub Satellite Locations
    KT17 4BT W1U7BU
    SY22 6AA
    E4 7JA BN1 4JF
    NW1 8HX
    OX29 7DX

    etc,etc (sorry, I can't get this table to come out in the right format. The satellite postcodes W1U7BU, SY22 6AA, BN1 4JF, NW1 8HX, OX29 7DX should all be in the second column).

    It ouputs drive distance, drivetime and straight-line distance to the same worksheet.
    This is the code I used

    Private Sub CommandButton1_Click()
    Dim objApp As New MapPoint.Application
    Dim objMap As MapPoint.Map
    Dim objRoute As MapPoint.Route
    Dim objLoc1 As MapPoint.Location
    Dim objLoc2 As MapPoint.Location

    Set objApp = CreateObject("MapPoint.Application.EU.13")
    objApp.Visible = False

    Set objMap = objApp.NewMap
    Set objRoute = objMap.ActiveRoute
    Worksheets("Excel Worksheet").Cells(1, 3).Value = "Drive Distance (kms)"
    Worksheets("Excel Worksheet").Cells(1, 4).Value = "Drive Time (mins)"
    Worksheets("Excel Worksheet").Cells(1, 5).Value = "Straight Line Distance (kms)"
    NReadRow = 2
    n = 2

    Do While Worksheets("Excel Worksheet").Cells(NReadRow, 2) <> ""

    'Locate the 2 points
    Set objLoc1 = objMap.FindResults(Worksheets("Excel Worksheet").Cells(n, 1)).Item(1)
    Set objLoc2 = objMap.FindResults(Worksheets("Excel Worksheet").Cells(NReadRow, 2)).Item(1)

    'Calculate the route
    objRoute.Waypoints.Add objLoc1
    objRoute.Waypoints.Add objLoc2

    'Drive Distance in kms
    Worksheets("Excel Worksheet").Cells(NReadRow, 3) = objRoute.Distance
    'Drive Time in minutes
    Worksheets("Excel Worksheet").Cells(NReadRow, 4) = objRoute.DrivingTime
    'Straight Line Distance in kms (as a check)
    Worksheets("Excel Worksheet").Cells(NReadRow, 5) = objMap.Distance(objLoc1, objLoc2)

    'Assign the correct hub
    NReadRow = NReadRow + 1
    If Worksheets("Excel Worksheet").Cells(NReadRow, 1).Value <> "" Then
    n = NReadRow
    End If


    End Sub

    As I said I’m only learning so you need to treat the code with real caution; there are bound to be errors. But I hope it gets you started.

    By the way you’d have got better exposure of your post if you’d put it in the MapPoint Discussion Forum.

    Rgds, David
    Last edited by davidb; 08-29-2007 at 07:44 AM. Reason: Table comes out with wrong format
    UK mapping and map analysis services at www.broomanalysis.plus.com

  3. #3
    chashutch is offline Junior Member White Belt
    Join Date
    Aug 2007

    Re: Distance and Time Traveled ??

    Thanks for the reply. (I moved the thread also).

    I will take a shot at putting your code in and seeing what happens.

    Question... I have recently been upgraded to Office 2007. Might there be an issue with using Excel VBA from 2007 then saving down to 97-2003 workbook?

    Thanks again David.

  4. #4
    PROFIT100 Consulting is offline Member Green Belt
    Join Date
    Sep 2009
    Darmstadt, Germany

    Re: Distance and Time Traveled ??

    Itīs quite easy: Use the Excel-Addin IC-DistanceCalc itīs like Milecharter, but the programm is much more powerful. It will fit your needs.
    Send us an email, an you will get the tool and a 3-month unlimited license-key.
    Manfred Suendorf
    mail to: ms@profit100.de

  5. #5
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Blog Entries

    Re: Distance and Time Traveled ??

    You're posting to a 3+ year old thread.

    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~ Upgrade to Get Access to the MapForums Downloads ~

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Distance/Time between Waypoints
    By jasanite in forum MapPoint Desktop Discussion
    Replies: 7
    Last Post: 08-18-2007, 11:16 AM
  2. How do you create a time to distance map?
    By Integraoligist in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 08-01-2007, 03:52 PM
  3. Distance calculation time (MP 2006) - HELP
    By dsia in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 06-04-2007, 02:16 AM
  4. Distance/Drive Time
    By stwilber in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 11-10-2006, 10:13 AM
  5. My calculations on distance and time appear to be wrong
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 11-27-2004, 05:58 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96