PDA

View Full Version : MapPoint Power Tools Spreadsheet



Eric Frost
07-19-2008, 09:36 AM
The MapPoint Power Tools spreadsheet has several tools which are separate tabs or worksheets.


Geocode Addresses
Optimize Route
Initialization
Mapping An Address
Distance Between Zip Codes
Drawing Drivetime Zones


Each of these includes full source code and can be customized... it basically shatters the kludgy MapPoint interface for doing things and shows you how to automate or program the MapPoint functions from lists and values in Excel.

PhilUK
08-09-2008, 04:18 AM
Eric. For someone who knows the basics of MapPoint... but only that...how do I use the optimization tab with my own data. Also as I am in the UK I use the European map version...is this a problem?

Eric Frost
08-11-2008, 10:20 AM
Hi Phil,

You just need to replace the sample addresses that are in there with your own list of addresses and click the button.

Let me know if you have any trouble and I can set up a LiveMeeting session to demo it.

The spreadsheet is fully compatitible with the European version.

Eric

PhilUK
08-13-2008, 08:38 AM
Eric

Thanks for the response

I know I am probably being thick over this but when I open the spreadsheet on any tab and I even put the cursor near the button, let alone click it, I get a Visual Basic error message "Compile error. Can't find project or library"

Eric Frost
08-13-2008, 09:18 AM
No, I'm sure it's something I need to fix. What version of MapPoint Europe do you have installed? 2004 or 2006? I'll set up a test environment and check it out.

Eric

PhilUK
08-13-2008, 09:19 AM
Thanks. 2006 Eric

DBWalk
08-13-2008, 12:21 PM
No, I'm sure it's something I need to fix. What version of MapPoint Europe do you have installed? 2004 or 2006? I'll set up a test environment and check it out.

Eric
I am getting the same error message trying to use the tool with an older version (2004). Can't figure out how to link the right objects.

Eric Frost
08-13-2008, 12:52 PM
Thanks. 2006 Eric

Try this one for the European version.. the code WAS explicitly trying to load the .NA version, so this should fix it. Let me know if it works and I'll go ahead and replace the version posted at the top of this thread.

Eric

Eric Frost
08-13-2008, 01:06 PM
I am getting the same error message trying to use the tool with an older version (2004). Can't figure out how to link the right objects.

I directly reference the 2004 (NA) version with the attached spreadsheet, I tested it and it works.

Note that the Geocode address will not work because I use the .Latitude and .Longitude properties that were added in MapPoint 2006... let me know if you need this and I can substitute those properties with Gilles Kohl's hack.

Eric Frost
08-21-2008, 10:01 AM
I added several screenshots to show some of the functions..

Eric

garyclin57
09-12-2008, 03:17 PM
I have just downloaded the MapPoint Power Tools_v1[1].2.zip. I would like to know whether this version will work on MapPoint 2009. Thanks.

Gary Lin

Eric Frost
09-13-2008, 11:48 AM
Yes, it should.. have you tried it and did you get any error? Let me know and I'd be happy to fix it.. if there is an error it should be an easy fix.
Eric

RonH
02-05-2009, 09:18 PM
Similar to the post above, I just purchased MapPoint 2009. I had been using the Power Tools spreadsheet with MapPoint 2002 without a problem, but after un-installing MP2002 and installing MP2009, I get an error.
The error says "Run-Time error '429' - ActiveX component can't create object"

When I debug the program is stopped at this line:
Set oApp = CreateObject("MapPoint.Application.NA")

The Microsoft MapPoint 16.0 Object Library (North America) reference is checked in the Tools-References dialog.

MapPoint 2009 runs fine by itself. Can you assist me?

Thanks,
Ron

Eric Frost
02-06-2009, 04:23 AM
Here's two things to try..

Using a command prompt, go to
C:\Program Files\Microsoft MapPoint 2009\
and type
> mappoint /regserver

Now, try the attached spreadsheet, I explicitly reference MapPoint 2009. I don't think this would be necessary, but I tested this spreadsheet after running mappoint /regserver and it worked.

Let me know how it goes!

Eric

RonH
02-06-2009, 10:23 AM
Thanks, Eric, but it didn't work. It must be a problem with my laptop, as I installed MP 2009 on my desktop machine (after removing MP 2006), and everything works OK, even with the normal version of the Power Tools Spreadsheet.

I tried an uninstall and then a re-install of MP 2009, and then I did the mappoint /regserver command in the command prompt. Still didn't work. The same error as before comes up. For some reason, my laptop system refuses to recognize the mappoint object library or the mappoint activex control. I tried both in the Tools-Reference dialog, not at the same time.

Is there supposed to be some type of response with the mappoint /regserver command? After hitting enter, there is no comment on the action taken, the c:\Program Files\Microsoft Mappoint 2009 directory prompt just appears.

Thanks for your help. I know it is very frustrating to troubleshoot long distance. I have a copy of Mappoint 2006, I wonder if I removed 2009, installed 2006, removed 2006, then reinstalled 2009 if it would work?
My laptop had an old version of MP 2002 originally installed, so maybe MP 2006 leaves some files needed?

Eric Frost
02-06-2009, 10:30 AM
mappoint /regserver is supposed to clean up cases where there are multiple versions installed (or old versions were installed previously) and force the exectuable you are running it with to be the "current" version. There is no response, it is silent.

Your suggested course of action doesn't sound to me like the solution, but it couldn't hurt!

I am wondering if it is something with the Excel/Office versions? What version of Excel is on each? Do you have admin rights on both machines? Anything else that could be different between the machines?

Eric

RonH
02-06-2009, 12:24 PM
Fixed it!!! I found it under an old posting to this forum at http://www.mapforums.com/sitemap/t-5256.html

================

"We experienced the same problem with some users who had been running an earlier version of MapPoint (2002 or 2004) on their PC, and then installed MapPoint 2006. After spending some time with Microsoft techs, the problem was discovered to be permissions-related. The solution was as follows:

1. Using RegEdit, change the permissions on HKEY_CLASSES_ROOT\.ptm and HKEY_CLASSES_ROOT\.ptt to Full Access for the Administrator. (to do this, right-click on the .ptm folder under HKEY_CLASSES_ROOT and select "permissions")

2. From command prompt, reregistered MapPoint using the command "C:\Program Files\Microsoft MapPoint\MapPoint.exe" /regserver.

The Microsoft tech said that he has seen this more than a few times with users running MP2006 who had previously been running an earlier version. He says that they are checking to see if this is, in fact, a bug with the MP2006 install routine. Good luck!"

=======================

I uninstalled MP 2009, then removed an old registry value to Mappoint.exe, then re-installed MP 2009. Using the above suggestion, I went into the registry and sure enough, the .ptm and .ptt did not have full permissions set. I set them to full access. I then ran the /regserver just to be sure and everything works great now.

Thanks for your quick replies. You set me on the right track.

Ron

Eric Frost
02-06-2009, 12:39 PM
"He says that they are checking to see if this is, in fact, a bug with the MP2006 install routine."

I guess they never figured it out since you are having trouble with MapPoint 2009! :clown2:

Anyway, glad to hear you have solved and it maybe I should delete the "_2009" version so as not to confuse people. I assume you can use the original one OK?

I want to create a new version 2.0 with some new tools to be released in the next month or two. Do you have any suggestions on functions to include or stuff that would be useful to have?

Eric

RonH
02-06-2009, 12:59 PM
Yes, the original version of the Power Tools spreadsheet works fine now.

I am glad :clap:you are thinking of updating it, because I do have a suggestion. Could you add the capability of using latitude and longitude numbers for point locations as well as normal street addresses? I could use this for all the routines. You see, our company uses lats and longs for our delivery locations because of the inherent unreliability of street addresses due to new roads, misspellings, etc. of which I know you are aware. Any lat/long format would do, but for simplicity we use decimal degrees, i.e.; -78.36, 37.45

I purchased the excellent add-in Routene found on this site from Tamarack, and the developer has graciously made it able to read lats and longs for me. It is a great help and makes the add-in extremely useful for me.

If I can think of other suggestions, I will forward them to you via this forum. I will be working with the Power Tools extensively on several projects I am considering for my company.

Thanks again,
Ron

jonesy
02-17-2009, 03:35 PM
Eric,
I've tried to get the MapPoint Power Tools Spreadsheet working on my client but to no avail. I've read through the following thread and even changed the ptm and ptt hotkeys to allow administrator full use privileges thinking this may be the constraint as I had a previous (trial) version of mappoint some time ago that has since been deleted.

I spreadsheet is not functioning. I recently installed MP2009 and would truly like to use the spreadsheet tools but at this point i am stumped. In addition I am not advanced programming capable. Please advise.
Thanks
Jonesy - new user

Eric Frost
02-17-2009, 04:12 PM
What error do you get?

I would recommend to go into the Program Files directory where the Microsoft MapPoint folder is located and at the command prompt type

> mappoint /regserver

This sometimes solves problems when different versions of MapPoint have been installed on the computer.

Do you have MapPoint North America or Europe?

Eric

jonesy
02-20-2009, 09:26 AM
Hi Eric,
I have the 2009 North American Map Point version. I also have Excel 2007 and have tried runnng the ap as both a .xlsm and .xls version.

I misstated in my previous thread. I am not actually getting an error message. I am not getting anything when I select the begin button. It is silent. I've been trying to use the distance zip code tab replacing the values in line 11 but nothing happens.

From the command prompt window I have attempted to go to the map point directory and type in > mappoint /regserver as suggested in your previous thread. Although I do not believe this is the solution I have been unsuccessful in attempting this because I am either doing it wrong because it fails to recognize in the command prompt window this path. I am sorely inadequate in using DOS.

I seem to be running out of options and really would like to be able to use this tool and wonder what else I can try.

jonesy

RonH
02-21-2009, 11:10 AM
Perhaps you are not getting to the correct directory. First, open your Windows Explorer and find the exact path to your Mappoint 2009 directly, most likely it is: C:\Program Files\Microsoft Mappoint 2009. But check, yours may be different.
Then open the command prompt, found by going to Start-All Programs-Accesories-Command Prompt. When open type in (without the quotes) "cd c:\Program Files\Microsoft Mappoint 2009". (of course, use your path found above).
When in that directory, type in the command mentioned above: > mappoint /regserver
Hope this helps.

jonesy
02-22-2009, 05:17 PM
Ron H
Thank you. Correct. I had the wrong directory path and needed help on changing the directory. In addition I found that I had a setting in my version of excel preventing the maco from working. Once I corrected that and caught on to how the application works by changing values on line 11 that launched the map it all came together.

jonesy

Eric Frost
02-23-2009, 10:48 AM
Glad you got it working. Let me know if you have any suggestions for the spreadsheet. I'm hoping to release a new one in a month or two.
Eric

flystol
04-17-2009, 12:01 PM
Hi Eric,
I am a new forum member and lo level MapPoint user. I need to calculate aprox. mileage between zip codes for about 2 million records. I donwloaded your Excel tools but it appear the calculator only works on single records. Is there a way I can automate this function? Also, I am now using Excel 2007 - will your add-ins work with that newer version? Thanks for your help.

Eric Frost
04-17-2009, 12:04 PM
It should work without any problem in Excel 2007. If you have a problem let me know, it would be a simple fix.

2 million is a lot of records. It would take a really long time and I'm not sure Excel is going to handle lists that size.

Do you need road distance or would straight line distance be sufficient? If straight line distance is OK, I could cook something up in Access...

Eric

flystol
04-17-2009, 12:07 PM
Eric,

I only need straight line distance - does not have to be super accurate. An Access solution would be super.

Eric Frost
04-17-2009, 12:13 PM
Can you send me a sample of your data and describe what you would like to do specifically?

efrost@gmail.com

Eric

flystol
05-10-2009, 12:40 PM
Eric,

Thanks for all your help on this solution. The Access macro works great - I was able to process my 1.5M records in less than an hour. I will provide you with a list of nice to have enhancements, but the basic functionality is there!

Flystol:clap:

hogateb
01-26-2010, 11:56 AM
I have seen the question asked many times here in the forums and I have not seen an answer as of yet. I am looking to export out the Census Tract info for a group of approximately 2,300 push pins. I then want to use this data to create a Custom Territory. In the past I created territories by zip code and this proved to be unreliable.

Any suggestions?

Thanks!

Eric Frost
01-26-2010, 12:41 PM
You can't probably do what you want with MapPoint, you might need some other demographic report generator. However, you can export census tract data by doing a Data Map with census tracts.

Data | Data Mapping Wizard | Shaded Area | United States Demographic Data | Pick Census Tract and Whatever Variable you want (for instance Population 2007) | Now draw a shape or freeform around the whole area | Right-click and export to Excel.

You'll get a worksheet with all the Census tracts listed along with your demographic variable. Unfortunately this is not associated with the Pushpins.

It would be kind of computationally intensive, but you could program something to automate drawing a little shape around each pushpin and getting the census tract data individually, but I don't know if it's worth it.

Eric

hogateb
01-26-2010, 03:38 PM
Thanks for the idea. Not sure that I really want to go that route as you suspected. I will keep toying with it to see what I can come up with. May be as simple as just getting zip code and Census Tract data and then adding the Census Tract based upon the zip. Just trying to use current tools / data without purchasing new or additional ones.

This is obviously not as simple as exporting the lat/lon coordinates out of Mappoint as I had hoped.

ukomat3
04-21-2011, 10:18 AM
Hello Everybody,

1. Did anyone try to use MapPoint Power Tools_v1.2EU for Geocoding addresses in Europe. I am getting this strange error once I open the spreadsheet.

Compile error. Can't find Project or Library.

I am using Mappoint Europe 2010, is it compatibility issue because I downloaded this tool from one of the old posts.

2. I am running into same issue when I am trying to get the driving and straight line distances using the Driving and Straight Line Distance Between LatLons.xlsm posted in this forum.



Thanks!
Uday

Eric Frost
04-21-2011, 10:36 AM
Have you had multiple versions of MapPoint installed in the past or just the one version?

In any case, if you can, go into the VBA Editor (Alt-F11) and go to the Menu --

Tools | References

I'm guessing it will probably say that the MapPoint reference is missing. Uncheck the one that is missing and then scroll down to find the correct reference --

"Microsoft MapPoint ..."

I'm not sure off the top of my head exactly what version it will say for MapPoint 2010. Probably --

"Microsoft MapPoint 17.0 Object Library (Europe)"

Please let us know if this takes care of the issue!

Thanks,
Eric

ukomat3
04-21-2011, 11:34 AM
Thanks Eric.

I had Mappoint NA 2006, 2009 installed earlier. I unchecked the missing ones and the spreadsheet works without any issues.

Also wanted to seek your advise on Geocoding European addresses. I am trying to Geocode addresses in about 26 countries of Europe. Part of the challenge is most of the data is unparsed as it would be used in Shipping a mail. Do you have any thoughts on this?


Uday

Eric Frost
04-21-2011, 11:39 AM
Do you mind to start a new thread and re-post your question there? As this is a new topic.

My guess is that you'll need to parse it. MapPoint does OK when it tries to automatically parse when you paste an address into the Find Box, but not super, and in any case I don't think there's a programmatic equivalent. There may be some address parsing functions from "data" companies like melissadata.com or Pitney Bowes, or some high-end geocoders, but my guess it that it would be easier and more accurate to just do it yourself.

Do you want to post some sample data (in your new thread) and we can take a look?

Eric