PDA

View Full Version : VBA Export from Excel to MapPoint 2013



matt.kunkle
01-03-2013, 04:00 PM
Hello,

I have an Excel sheet that queries into a database and pulls lat long locations from a GPS system with a time stamp. I am trying to export that information into a route into MapPoint. I have not done any VBA with MapPoint yet, and am a self-taught VBA developer with a couple years experience, so I apologize if I am not as up to speed.

Anyways, I began trying to use code we use in another worksheet, but it relies on address, city, state information. I am not sure how to get it to import into MapPoint by tweaking, although I'm sure I probably need to go a whole different direction. Below is the code I am using from the other workbook. My lat and longs in my sheet begin on row 6 and are in columns 5 and 6 respectively. I would not need to optimize, but would like to route based on the sequential time stamps in column 2. I appreciate any help or direction you could provide.


Sub AddWaypoints()
Dim oApp As New MapPoint.Application
Dim oMap As MapPoint.Map
Dim oRte As MapPoint.Route
Dim strStreet As String
Dim strCity As String
Dim strState As String
Dim strZip As String
Dim currentRow As Integer
Set oMap = oApp.ActiveMap
oApp.Visible = True
oApp.UserControl = True
oMap.Parent.PaneState = geoPaneRoutePlanner


Set oRte = oMap.ActiveRoute
oRte.Clear

Sheets("Data").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
currentRow = 2

Do While Cells(currentRow, 9) <> ""
strStreet = Cells(currentRow, 9).Value
strCity = Cells(currentRow, 10).Value
strState = Cells(currentRow, 11).Value
strZip = Cells(currentRow, 12).Value
With oRte.Waypoints
.Add oMap.FindAddressResults(strStreet, strCity, , strState, strZip)(1)
'.Add oMap.FindAddressResults(, "New Bedford", , _
' "Mass")(1)
'.Add oMap.FindAddressResults(, "Newport", , "RI")(1)
'.Add oMap.FindAddressResults(, "Mystic", , "Conn")(1)
End With


currentRow = currentRow + 1
Loop
oRte.Waypoints.Optimize
oRte.Calculate
'Zoom to the route
oRte.Directions.Location.GoTo

Sheets("Main").Select
End Sub

Eric Frost
01-04-2013, 10:29 AM
Hi and Welcome to the Forums!

How long has your company been using MapPoint?

You can use oMap.GetLocation(,) to add a lat/lon instead of address.

Do you want to post your spreadsheet with some sample data as a zip file attachment to this thread? Then I can take a stab at making it work for you.

Eric

matt.kunkle
01-11-2013, 08:55 AM
Eric,

Thanks for the welcome and for your help. I have uploaded a sample attachment with the exact format of the sheet I'm using. The sheet queries into an ODBC connection which is where the data will come from with the refresh. As you can see, my band aid while trying to figure this out was to create a quick button to move the data to a new sheet, which I've been then importing manually.

We've been using it as long as I've been here (3 years).

Ideally, once the data is imported, it would be great to go a step further and map a route using the timestamp to determine sequence. Since I am not familiar with the MapPoint functions, I am not sure if this is easy to do or not. Any help you can offer would be greatly appreciated.

Thanks again.

-Matt

Eric Frost
01-13-2013, 08:11 AM
I see two buttons in there, one runs the "refresh" macro and the other runs the "newsheet" macro.

But have errors right away, but actually you don't want me to run either one of those, correct?

You need to get the AddWaypoints macro working?

Eric Frost
01-13-2013, 08:29 AM
I played with the code a bit, does this get you closer to what you need?


Sub AddWaypoints()
Dim APP As New MapPoint.Application
Dim MAP As MapPoint.MAP
Dim RTE As MapPoint.Route

Dim strLat, strLong, strTime As String

Dim row As Integer
Set MAP = APP.ActiveMap
APP.Visible = True
APP.UserControl = True
MAP.Parent.PaneState = geoPaneRoutePlanner

Set RTE = MAP.ActiveRoute
RTE.Clear

row = 6

Do While Cells(row, 5) <> ""
strTime = Cells(row, 2).Value
strLat = Cells(row, 5).Value
strLong = Cells(row, 6).Value
With RTE.Waypoints
Dim loc As MapPoint.Location
Set loc = MAP.GetLocation(strLat, strLong)
.Add loc, strTime
End With

row = row + 1
Loop

RTE.Calculate
RTE.Directions.Location.GoTo

End Sub


I put the TimeStamp in as the WayPoint name, I thought that might be more usable than the lat/lon as the name.

Here's a screenshot of the map it comes up with --

http://www.mapforums.com/images/articles/route_map.jpg

matt.kunkle
01-18-2013, 07:50 AM
Eric,

This works perfectly! Thank you very much for your help.

I changed one thing - because I recently edited my query to do a descending sort on the date time (personal preference on viewing this particular location data), I added RTE.Reverse to the end of the code (MapPoint 2010 and newer). Hopefully this may help others in the future.

Thanks again Eric.

Eric Frost
01-18-2013, 09:24 AM
Hopefully this may help others in the future.

Would you be interested in doing a brief write-up to post as an article?

This way I can include in our newsletter e.g. The Magazine for MapPoint - MP2K Magazine (http://www.mp2kmag.com)

It's also one way for your business to get some exposure if you like.

Here's a recent article from someone else I helped -- http://www.mapforums.com/texas-bluebird-society-uses-mappoint-split-up-prioritize-mailing-list-27596.html

best,
Eric