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
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Import Addresses from Excel using VBA

This is a discussion on Import Addresses from Excel using VBA within the MapPoint Desktop Discussion forums, part of the Map Forums category; How can I add stop time to the code above to import during the script? I have the above script ...

  1. #11
    cable0's Avatar
    cable0 is offline Junior Member White Belt
    Join Date
    Sep 2008
    Location
    ATL
    Posts
    4

    Re: Import Addresses from Excel using VBA

    How can I add stop time to the code above to import during the script?

    I have the above script working , just need to add STOP time to the stops and then I can get back to work!

  2. #12
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Import Addresses from Excel using VBA

    If you paste in your code I can take a look and make suggestions.

    There are some sample solutions in the MapForums Downloads section ($99)

    Import a Route with Stoptimes

    Import a Route with Stoptimes and Depart Times

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

  3. #13
    cable0's Avatar
    cable0 is offline Junior Member White Belt
    Join Date
    Sep 2008
    Location
    ATL
    Posts
    4

    Re: Import Addresses from Excel using VBA

    Here is the code I am using that does not work for the stop time, the importing of addresses does work...

    Code:
     
    Sub AddresstoMapPoint()
    
    Dim nCount As Integer
    Dim i As Integer
        Set App = CreateObject("MapPoint.Application")
        App.Visible = True
        Set objMap = App.NewMap
        Set objRoute = objMap.ActiveRoute
        Row = 2
        While Cells(Row, 1) <> ""
        objRoute.Waypoints.Add objMap.FindAddressResults( _
        Cells(Row, 1), _
        Cells(Row, 2), , _
        Cells(Row, 3), _
        Cells(Row, 4), _
        Cells(Row, 5)).Item(1)
        Row = Row + 1
        
    Wend
     
    Dim myStopTime As Double
    myStopTime = 5# / 60# / 24#
    With oMap.ActiveRoute
        For Each wp In .Waypoints
           wp.StopTime = myStopTime
        Next wp
    End With
        
     
    MsgBox ("Click ok to retun to excel")
     
    End Sub
    Any help would be approciated...

  4. #14
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Import Addresses from Excel using VBA

    This is the way I did it, hope this helps!

    myStopTime is an integer specifying the number of numbers, e.g. could be 1 or 60 or ...

    myItem is an integer specifying which waypoint.

    Code:
    oRoute.Waypoints.Item(myItem).StopTime = myStopTime * geoOneMinute
    Note that you can't set the stoptime for the last item, as it is the end, so you have to loop back over your waypoints to set the stop time for all waypoints EXCEPT the end, make sense?

    Let us know how it goes!

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

  5. #15
    Rosevillerod is offline Junior Member White Belt
    Join Date
    Sep 2011
    Posts
    5

    Re: Import Addresses from Excel using VBA

    This is how I did it, inside a do-loop where each waypoint may have a different stop time, as determined by the individual cell referenced within two nested arrays...

    objMap.ActiveRoute.Waypoints.Item(R2 + 1).StopTime = Val(info(Route(R1, R2), 8) * geoOneMinute

    The important point here is that you use one of the keywords "geoOneMinute" or "geoOneHour".

    Rod

    don't know why but the forum is replacing some text with a smiley...another try:
    = Val(info(Route(R1, R2), 8) * geoOneMinute


    ok, how about this: = Val(info(Route (R1, R2), eight) times geoOneMinute. go ahead, make THAT a smiley!
    Last edited by Eric Frost; 11-26-2011 at 05:45 AM. Reason: Somehow the program automatically replaces some text with a smiley...weird

  6. #16
    cable0's Avatar
    cable0 is offline Junior Member White Belt
    Join Date
    Sep 2008
    Location
    ATL
    Posts
    4

    Re: Import Addresses from Excel using VBA

    Rosevillerod - send you a PM on the way you did it, hopefully you can help me....

  7. #17
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Import Addresses from Excel using VBA

    don't know why but the forum is replacing some text with a smiley...
    There's an option to "Disable smilies in text" under Additional Options below where you type

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

  8. #18
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Import Addresses from Excel using VBA

    I don't really see any difference between the way we did it?

    But remember you can't set the StopTime for the last item as it is the End, so it will give you an error. That's why you would have to loop back through to set the stoptimes after you insert all the waypoints.

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

  9. #19
    Rosevillerod is offline Junior Member White Belt
    Join Date
    Sep 2011
    Posts
    5

    Re: Import Addresses from Excel using VBA

    You're right Eric, no real difference...
    My program assume a common start and end point, as in leave the warehouse in the AM and return at the end of the day, so all inserted waypoints are truly stops that will contain stop time. Once I end the day (put in the return to whse waypoont), I evaluate the time. If it is under my limit, I delete the final waypoint, add another stop, then add the return to whse again and remeasure. That ensures a full day for the driver.

Page 2 of 2 FirstFirst 12

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Cannot Import Excel Files
    By GKing in forum MapPoint Desktop Discussion
    Replies: 12
    Last Post: 09-06-2016, 12:22 AM
  2. order of the addresses in the excel export
    By pileggi in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 01-27-2010, 09:48 AM
  3. Import a list of addresses from Excel to create optimized route
    By bran987 in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 10-10-2009, 08:33 PM
  4. Import on Excel
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 12-01-2003, 12:48 PM
  5. Let's try again. I want to import addresses into ....
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 08-10-2001, 02:45 PM

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 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114