PDA

View Full Version : Drawing Lines with MapPoint from Coordinates in Excel



Eric Frost
02-20-2010, 03:22 PM
Here's a fun little Excel app that draws lines from pairs of lat/lon coordinates in Excel. I'm sure you could come up with some interesting-looking maps :eek2:

Originally I had thought to do an app with a single starting point and then lots of ending points so there is a single "spider" graphic, but thought it would be more flexible if there are pairs of lat/lon's on the whole list, see attached.

Also, rather than a single settings for the line Weight, Color, and Arrow, the settings are made for each individual row making it more flexible. Of course if you only have a single starting point and only want one color/weight/etc. there is a lot of redundancy on the list, but it is really easy in Excel to double-click and automatically copy that data down the whole list.

Note that this app using lat/lon pairs, not addresses or location names, but it could easily be adjusted to use addresses or pushpin names, or whatever.

Note that this macro attaches to an existing open instance of MapPoint. YOU NEED TO OPEN MAPPOINT FIRST BEFORE RUNNING THE MACRO. (bold just to make sure people notice this part of the instructions this long into the text haha).

Let me know if you have any questions or would like to see this modified or integrated with your business processes :-)

Eric

____
more info added 10/25/11

I added a screenshot of the input form. The Colors tab is included so you can see the values of all the MapPoint color codes for easy reference.

One obvious enhancement related to the comment above: this could be easily modified so that the Colors tab just had a list of all the valid MapPoint colors (e.g. Black, Brown, OliveGreen, DarkGreen, DarkTeal, DarkBlue, Indigo, Gray80, DarkRed, Orange) and the macro would do the translation to color code internally so the user doesn't have to deal with numeric codes :-) Let me know if someone wants this.

Any other suggestions welcome!

Eric

Eric Frost
02-20-2010, 04:40 PM
Here's another screenshot and the Excel spreadsheet with the data and macro used to generate the lines.

Eric

Eric Frost
12-08-2010, 03:55 PM
There is now another version of this tool using City/State pairs instead of lat/lon coordinates. It is here --

http://www.mapforums.com/drawing-lines-between-city-state-pairs-14749.html

Eric