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

VBA Export from Excel to MapPoint 2013

This is a discussion on VBA Export from Excel to MapPoint 2013 within the Development forums, part of the MapPoint Desktop Discussion category; Hello, I have an Excel sheet that queries into a database and pulls lat long locations from a GPS system ...

  1. #1
    matt.kunkle is offline Junior Member White Belt
    Join Date
    Jan 2013
    Posts
    3

    VBA Export from Excel to MapPoint 2013

    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.

    Code:
    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
    Attached Files Attached Files
    Last edited by matt.kunkle; 01-11-2013 at 09:51 AM. Reason: upload attachment

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

    Re: VBA Export from Excel to MapPoint 2013

    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
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  3. #3
    matt.kunkle is offline Junior Member White Belt
    Join Date
    Jan 2013
    Posts
    3

    Re: VBA Export from Excel to MapPoint 2013

    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

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

    Re: VBA Export from Excel to MapPoint 2013

    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?
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

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

    Re: VBA Export from Excel to MapPoint 2013

    I played with the code a bit, does this get you closer to what you need?

    Code:
    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 --

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

  6. #6
    matt.kunkle is offline Junior Member White Belt
    Join Date
    Jan 2013
    Posts
    3

    Re: VBA Export from Excel to MapPoint 2013

    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.

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

    Re: VBA Export from Excel to MapPoint 2013

    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

    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 -- The Texas Bluebird Society Uses MapPoint to Split up and Prioritize a Mailing List

    best,
    Eric
    ~ 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. Export Rotes from MapPoint into Excel
    By ccm in forum MapForums Plus Chat
    Replies: 1
    Last Post: 04-18-2011, 12:33 PM
  2. Mappoint 2009 Export to Excel Error
    By dcharles226 in forum MapPoint Desktop Discussion
    Replies: 4
    Last Post: 04-20-2009, 06:12 PM
  3. Does MapPoint 2006 export Zip Codes to Excel (if territories are counties)?
    By SentryEquip in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 06-25-2008, 03:41 PM
  4. Macro to Export to Mappoint (from Excel)
    By infinityukok in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 08-03-2007, 07:39 AM
  5. how to export the data of an Excel table on a chart mappoint
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 02-17-2004, 05:37 AM

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 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127