Running MPRouteWriter

Top  Previous  Next


MPRouteWriter is a relatively simple add-in, and has two panels. The main MPRouteWriter panel is used to write route information to an external destination (eg. Excel). The MPRouteReader panel is used to create a new route from pushpin data.


This section discusses the MPRouteWriter panel. To use it, first create the route that you wish to export. Then select MPRouteWriter... from the Tools menu in MapPoint. You will be presented with a panel that looks like this:





Unregistered installations of MPRouteWriter will also show a Buy Now! button.


To export the current route to Excel, all you have to do, is to make sure Output Route to is set to Excel Spreadsheet and then press the Save Route button. This will start Excel, calculate the route and write the route data out. The resulting Excel spreadsheet will look something like this:




This example is taken from the sample data provided for MPRouteWriter, and is based on the clients sample data that Microsoft supplies with MapPoint.


The first column is the sequence number for the waypoint within the route. The next column is the name of the waypoint. This will also be the same as the name of the pushpin used to create the waypoint, if you used one. The remaining columns are all optional, and depend on the other settings.


As well as Excel, MPRouteWriter can write the same information to a text file. This is covered on the Writing to a Text File page.


Use the Output Times and Output Distances checkboxes if you want times and distances to be written for each waypoint. These are relative to the start location. Hence the start always has a time of 0 minutes and 0 miles. Times are always in minutes. The distances will be either miles or kilometers, depending on MapPoint's unit settings.


Distance and time intervals can also be calculated by setting the Output Intervals checkbox. These are the distance and travel time measurements between successive stops. Note that the travel time includes the stop time (if any). MapPoint does not include a stop time for the start & end waypoints, and end-of-day stops. An average speed for each interval can also be calculated, by setting the Output Average Interval Speeds checkbox.


By default, all of the route distances, travel times, and speeds are written and formatted to display all of the numeric precision that is available. This can be difficult to read. Set the number of required decimal places to use by setting the Format distance, time & speed checkbox. Distances, travel times, and speeds written to a text file will be rounded to the specified number of decimal places. Excel will still receive the complete values, but the numeric format will be set to only display the specified number of decimal places.


Set the Output Coordinates checkbox if you want the coordinates of each waypoint and rest stop to be written. These are reported in decimal geographic (longitude, latitude) coordinates using the WGS84 geoid (i.e. MapPoint and GPS standard). These are only reported if they are present . Coordinates are present for all pushpins and rest stops; and typically most waypoints.


MPRouteWriter can optimize the waypoint order, i.e. it can find calculate the shortest waypoint order before writing the route. Do this by setting the Optimize Route Order check box. With the exception of the start and end waypoints, this re-arranges all of the waypoints to minimize the route length.  Note that it can take a few minutes to calculate if you have a lot waypoints or if they are separated by large distances.


The Overwrite all stop times option sets the stop time for each and every waypoint. Enter the required stop time in the Time for each stop box. This is specified in minutes. Note that option over-writes stop times. If you clear the checkbox, it will use the existing stop times. It will not clear them. If you wish to clear the stop times, set the check box and set the stop time to zero minutes.


Use the Output Rest Stops check box if you wish to also output the rest stops. These will be written between the waypoints in the output spreadsheet and marked with the name "Rest Stop" (or the equivalent text in your geographic edition of MapPoint). Corresponding distances, times, and coordinates will also be written if these options are selected. The Interval between rest stops (in minutes) and Length of each rest stop (also in minutes) can be set. Note that no attempt is made to find a street address or any pushpin data for the rest breaks.


Waypoints are often created using pushpins. If they are, MPRouteWriter has the ability to search for these pushpins, and report extra information for each waypoint. Pushpins are matched to waypoints according to their name. The pushpins can be in any pushpin set. Do not create multiple pushpins with the same name because this will result in unpredictable results. Set the Cross-reference with pushpins check box if you wish to search for matching pushpins. This option will also result in MPRouteWriter writing all available data fields for each pushpin. These are written in the order that they are found. Note that different pushpins in different pushpin sets may have different field definitions. MPRouteWriter can handle this, and will simply write blank cells for all fields that are not set for a particular pin. MPRouteWriter will also write blank cells for field values which were corrupt, null, or simply not set. Similarly, pushpins that have not been located (eg. because a data link was refreshed) can be listed with empty location information.


Checking the Cross-reference with pushpins checkbox also enables three further check-boxes. The first checkbox is labeled Resolve duplicate matching pushpins. Pushpins are matched to waypoints using their name. Spurious matches may occur if there are multiple pushpins with the same name. Select this checkbox if you want MPRouteWriter to attempt to resolve multiple pushpins that have the same name. MPRouteWriter resolves these pushpins by choosing the pushpin with a matching name that is closest to the waypoint in question. Note that although this process is the most reliable option, it can take a few minutes. For large datasets, it is recommended that you use unique pushpin names and disable this checkbox.


The second checkbox is labeled Output Pushpin Street Address. If the pushpins were created from addresses (eg. using the Data Import Wizard), then this street address location information will be available to MPRouteWriter. Select this checkbox if you wish to have the street data written to the spreadsheet.


The third checkbox is Sort fields in alphabetical order. MPRouteWriter orders the pushpin data field columns in the order that they are found. This can result in some variation between runs, if you are using multiple pushpin sets with different data field definitions. The best solution is to sort the data fields in a consistent order, and this what this option does. Note that a consistent (i.e. alphabetical) order is not necessarily a natural order for your dataset. For example, a typical US street address would be re-arranged as:  City, State, Street, Zipcode.


A final summary line can be written out after the waypoints and rest stops have been written. Enable this by setting the Write final summary line checkbox. This line lists the total distance and travel times. If average interval speeds have also been calculated, it also includes the average speed (including stops) for the entire route. As the summary travel time merely duplicates the end waypoint's travel time, the summary line formats it in hours and minutes ("xx hrs yy mins").


The Load/Run Excel Add-ins checkbox will be visible if you have selected Excel for output. It is possible to configure Excel to load or run various add-ins and recorded macros when it is started or when a new workbook is created. Check this checkbox, if you would like Excel to do this. For efficiency reasons, the MPRouteWriter default is for these add-ins to be not loaded. It is recommended that you only check this checkbox if you need the add-ins or macros that you have installed.


MPRouteWriter can also write the route information to a text file, and this is covered on the Writing to a Text File page.