PDA

View Full Version : Automating MapPoint with Excel VBA



amarkbass
02-19-2013, 02:59 PM
I've found tutorial 5 to be extremely helpful (radius circles: MapPoint VBA Tutorial, Vista Gadgets, Web Site Visitors Map, MapPoint GPS API, more (http://www.mp2kmag.com/update/mappoint.newsletter/2007-04-10/)) -- does anyone know how to adjust the VBA code so that it only looks to ZIP code, rather than the complete address (address, city, state, etc) ??

Also, is there a way to set up the vba so I can just input the data pushpins/circles using the data mapping wizard, rather than having to start a new sheet every time?

Any help would be greatly appreciated!!

Eric Frost
02-21-2013, 10:12 AM
Awesome, welcome to the forums!

The first should be very easy, just change this line
Set objFindResults = objMap.FindAddressResults(szAddress, szCity, , szState, szZip)
to this
Set objFindResults = objMap.FindAddressResults(, , , , szZip)
You might also want to force the County just to help ensure things work right like this
Set objFindResults = objMap.FindAddressResults(, , , , szZip, "United States")

Let me know if this does not work the way you'd like. Another option would be to use FindPlaceResults()

The second change is a little more involved as you have to loop over a dataset as opposed to the Excel sheet, but still fairly straightforward. I haven't tested this code, but you would do something like this:



Set ODS = MAP.Datasets("My Dataset")
Set ORS = ODS.QueryAllRecords()
ORS.MoveFirst
Do While Not ORS.EOF
'Create the circle using the ORS.Location
...
Loop


Hope this helps!
Eric

amarkbass
02-21-2013, 10:29 AM
thank you kindly sir, I'll give it a whirl and let you know how it goes

amarkbass
02-21-2013, 12:01 PM
Eric - The 1st change worked like a dream, I'm not sure how to implement the 2nd change but I'm working on figuring it out.

On another note, is there any way to track/export an excel file that shows what Zip codes are within the radius of each circle?

Eric Frost
02-21-2013, 12:07 PM
Yes, I think the best way would be to Export all the Zip codes and then re-import as Pushpins.

After this you can use the QueryShape or QueryPolygon method to see what Zip Code Pushpins fall within each one.

I answered a similar question using UK Postcodes recently, I think there's a link from our recent newsletter -- What's New at MP2K Magazine (http://www.mp2kmag.com/update/mappoint.newsletter/2013-02-18/)

amarkbass
02-21-2013, 01:34 PM
oh boy, sounds like some heavy lifting, but well worth it if it works.

Thanks for pointing me in the right direction Eric