PDA

View Full Version : Import Addresses from Excel using VBA



Sidereus
09-27-2011, 11:54 AM
Hi everyone,

Quick question and I will apologize in advance if it has been posted before but I was not able to find it in the forums using a search.

My problem relates to getting my addresses into the Mappoint route planner in the same order that they are in my excel file.

Now from the reading I have done, I do understand that this is not a Mappoint feature and I also understand why Mappoint loads them in a different order.

So where was my thought.

If you open the route planner and add addresses 1 and a time, they automatically get listed in the order they are entered.

Based on this, I simply want to write a simple VBA macro to run from excel that launches Mappoint and basically adds my address information line by line as it appears in excel.

No need to format the excel file or be worried about checking the sequence as the file is generated as formatted in chronological order already.

Also, there will not be any blanks between rows, and a blank cell will indicate the end of the data.

Basic idea I have is that this import function will need to be done in a loop until it gets to a row with no data at which point it ends.

While I have a basic programming knowledge, I am not familiar wit the VBA code / function names from Mappoint to get this to work.

Any help would be appreciated.

(And no, I am not interested in purchasing Routewriter as I feel this should not be a complicated task for the simple use I need it for)

Eric Frost
09-27-2011, 01:08 PM
If you don't want to do the programming yourself and looking to hire someone it's probably going to cost you more than RouteWriter :-) I think those tools are pretty inexpensive for what they do.

There are some MapPoint / Excel programming examples at Working With Excel and MapPoint (http://www.mp2kmag.com/excel/) and also in the newsletter tutorials MP2K Update Archives (http://www.mp2kmag.com/update/)

Also, full source code (mostly Excel VBA) is included with the tools in the MapForums Downloads section ($99). MapForums Plus - MapForums Plus Downloads at MapForums.com (http://www.mapforums.com/mapforums-plus-downloads/)

There are a few that come to mind that would probably be the closest to doing what you want --

http://www.mapforums.com/import-route-stoptimes-8292.html
http://www.mapforums.com/import-route-stoptimes-depart-times-16464.html
http://www.mapforums.com/mappoint-power-tools-spreadsheet-8080.html

That said, if you post the code you have so far and let us know where you are encountering difficulties, we can help you right here as well!

Eric

Sidereus
09-27-2011, 10:56 PM
Simple & Free.


Sub AddresstoMapPoint()

Set App = CreateObject("MapPoint.Application.NA.16")
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

MsgBox ("Click ok to retun to excel")

End Sub


Really simple macro that takes your excel file then adds the information line by line to the route planner, giving you your route as it showed on your excel spreadsheet.

The while loop is one big argument, where each "Cells..." populates a different part of the requirement for the find address function.

FindAddress Syntax:

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

Quick & Dirty to use it:

Change the numbers of the above while loop (integers only i.e. 1, 2, 3, etc) to match your column where the correct data is stored.

Mine works with 1 through 5 since I have a different macro that will automatically format and rearrange my canned report to place the needed information in columns 1 through 5.

Really simple and works as I asked above. Too hard to find the right ideas or maybe I wasn't asking the right questions. Either way, based on how many people look for this type of idea, hopefully it helps someone else in the future.

Eric Frost
09-28-2011, 01:18 PM
Sweet! Very good work. You are probably finding MapPoint is easier to program than you thought. The Help file is really good with tons of examples.

A couple comments:

For this line, you can omit the .NA and .16.


Set App = CreateObject("MapPoint.Application.NA.16")

Then it will work with whatever version of MapPoint is installed on your computer, whether it is European or North American or year (2006/2009/2010/2011, 2012?).


Also, if you have lat/lon instead of address, you would modify the Waypoint.Add line to be something like:


objRoute.Waypoints.Add objMap.GetLocation(Cells(Row, 1), Cells(Row, 2))


Anyway, glad you figured it out so easily and thanks for posting your solution!

Eric

Sidereus
09-29-2011, 08:14 PM
Last little issue with this.

I can not for the life of me get MP to zoom to the waypoints (automatically) once they are all placed, I am sure that I must be missing something simple as I have tried all types of variances with .GoTo and .ZoomTo and nothing.

Anyone who can help on this, it would be appreciated.

Eric Frost
09-30-2011, 08:32 AM
The route object itself has a ZoomTo method, see attached.

Did you try this or were you trying the .ZoomTo method on the WayPoints?

Eric

Sidereus
09-30-2011, 08:39 PM
No sure what version you are using, but with mine under methods for a route object it only shows Calculate and clear

Eric Frost
09-30-2011, 08:50 PM
Oops, what version are you using? It's possible they added that method. There was a ton of new stuff in 2009 and 2010, especially 2010. Current version is 2011.

Eric

Sidereus
09-30-2011, 09:09 PM
I am running 2009.

I actually thought that I should be able to use that type of method and tried all kinds of ways to apply it to the ojbRoute in my code above with 0 success.

The most direct way it seems to do it is with the .DataSets.ZoomTo however only pushpin data seems to be a dependent of DataSets and not waypoints.

The only way I can get it to zoom on the route is the use the .Calculate function but then it calculates the route and shows it on the screen which I do not want at that point as before this can be done, I need to enter different start / end points.

Very Frustrating. I would at this point settle for the .Calculate method if I could somehow get MP to not show the route directions to the screen, but even this is not idea as it wastes unnecessary computing time.

Eric Frost
10-01-2011, 07:16 AM
Is upgrading to get the new Zoomto method an option?

Also, is just zooming in manually that bad? You're not automating anything after that which is dependent on the Zoomto, correct?

Besides calculate, you could code something to add pushpins at all those location which would create a dataset, and then zoom to that. Then if you don't want those pushpins on the map, just delete the dataset. To make it slightly faster, just add four pushpins at the corners using some min/max code


maxX = Application.Max(maxX, oRS.Location.Longitude)
minX = Application.Min(minX, oRS.Location.Longitude)
maxY = Application.Max(maxY, oRS.Location.Latitude)
minY = Application.Min(minY, oRS.Location.Latitude)

hope this helps!

Eric

cable0
11-13-2011, 11:03 PM
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! :clap:

Eric Frost
11-14-2011, 05:29 AM
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)

http://www.mapforums.com/import-route-stoptimes-8292.html

http://www.mapforums.com/import-route-stoptimes-depart-times-16464.html

Eric

cable0
11-14-2011, 06:44 AM
Here is the code I am using that does not work for the stop time, the importing of addresses does work...




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...

Eric Frost
11-14-2011, 08:26 AM
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.


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

Rosevillerod
11-25-2011, 11:31 AM
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!

cable0
11-25-2011, 01:51 PM
Rosevillerod - send you a PM on the way you did it, hopefully you can help me....:censored:

Eric Frost
11-26-2011, 05:47 AM
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

Eric Frost
11-26-2011, 05:49 AM
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

Rosevillerod
11-26-2011, 10:39 AM
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.