PDA

View Full Version : Developing with MapPoint and Excel - Getting Started



Eric Frost
09-29-2004, 12:17 AM
Developing with MapPoint and Excel - Getting Started

This thread refers to a mini-tutorial in the latest MP2Kmag Newsletter. Subscribe by sending an e-mail to newsletter-subscribe@mp2kmag.com.

"Excel is an easy to use black board or sandbox for playing with MapPoint automation/programming and in tandem with output from MapPoint can be a powerful platform for all sorts of geospatial analysis. Perhaps the hardest part for beginners is figuring out where to begin. In this example I will outline the steps involved in setting up a basic macro for launching MapPoint."

Read the mini-tutorial here:
http://www.mp2kmag.com/update/mappoint.newsletter/2004-09-30/#features_sec

Click Post Reply to post a question or comment.

afryan
10-11-2004, 08:35 PM
I am interested in seeing examples of controlling MapPoint through Excel, this promises to be an interesting topic.

I was recently had a set of maps that had been created that used text boxes as labels for multiple locations on each map. I wanted to use VB in Excel to select all text boxes on the map and set the border to 1 pt. I'd be curious to see how that is done. Thanks.

paul howard
11-15-2007, 06:14 PM
[quote=Eric Frost;8103]Developing with MapPoint and Excel - Getting Started

This thread refers to a mini-tutorial in the latest MP2Kmag Newsletter. Subscribe by sending an e-mail to newsletter-subscribe@mp2kmag.com.

"Excel is an easy to use black board or sandbox for playing with MapPoint automation/programming and in tandem with output from MapPoint can be a powerful platform for all sorts of geospatial analysis. Perhaps the hardest part for beginners is figuring out where to begin. In this example I will outline the steps involved in setting up a basic macro for launching MapPoint."

Read the mini-tutorial here:
What's New at MP2K Magazine - The Magazine for MapPoint (http://www.mp2kmag.com/update/mappoint.newsletter/2004-09-30/#features_sec)

New starter

I have Mappiont 2004 Europe and I am trying to follow yout first tutorial but when running the Macro I have the message ActiveX component can't create object . I am sure it is due to the Set oApp line of the script.

Eric Frost
11-16-2007, 07:01 AM
Yes, change the NA in this line to EU

Set oApp = CreateObject("MapPoint.Application.NA.11")

Or better yet, try ommitting the entire .NA.11 part and just do this

Set oApp = CreateObject("MapPoint.Application")

hope this helps,
Eric

tukes
02-27-2008, 10:16 AM
Hi Erick,
I have been following the tutorial to work out how to do the programing.
I cant select Microsoft Mappoint11.0 Object... I only have the 13.0 version. I have selected this, but cant get the button to work.
any ideas?
Tom

Eric Frost
02-27-2008, 10:34 AM
Do you want to post your spreadsheet here as an attachment? I will take a look. What version of Excel and MapPoint do you have (NA or EU) ?

Eric

Lagair
08-25-2008, 01:56 AM
Hi Eric,

I have been playing with this problem only for a weekend, but your code seems to work beautifully, after editing for my current version of MapPoint.

However, I was wondering if there was a way to automate the closing of MapPoint after the code has ran, without saving the map of course. Then I can set the Visible to false and make it look automagical.

Thanks in advance.

Eric Frost
08-25-2008, 07:36 AM
Actually if you just put the oApp statement inside the Sub then it will go out of scope and close... this would be one way to do it.



Private Sub CommandButton1_Click()

Dim oApp As MapPoint.Application

Set oApp = CreateObject("MapPoint.Application.NA.11")
oApp.Visible = True

End Sub

Eric

Lagair
08-25-2008, 08:51 AM
That works, MapPoint closes, but it still asks to save the map. I am a non-programmer and am not sure how to work around this.

Eric Frost
08-25-2008, 10:52 AM
Just put in there a statement objMap.Saved = True..

You also need a objMap object. All the code would look like:


Private Sub CommandButton1_Click()

Dim oApp As MapPoint.Application
Dim objMap As MapPoint.Map

Set oApp = CreateObject("MapPoint.Application.NA.11")
oApp.Visible = True

Set objMap = objApp.ActiveMap
objMap.Saved = True

End Sub

Eric

Elliot Bendoly
09-20-2008, 01:26 PM
For those interested in leveraging Excel and/or VBA with MapPoint, I have a new reference out titled "Excel Basics to Blackbelt" (Cambridge Press 2008 ). Lot's of tricks to make use of. I've made a number of applications that integrate MapPoint with other apps through Excel and VBA as well.
Here's a video link to a redux of a Webinar I gave for both Palisades and Cox Communication recently:

http://www.new.facebook.com/video/video.php?v=35676304438 (http://www.new.facebook.com/video/video.php?v=35676304438)

The text is currently available at a promotional discount at Amazon.com
Enjoy

dfairbanks
10-06-2008, 01:58 PM
I hope someone is still viewing this thread. I need some help.
I do not have a lot of knowledge of VB or any programing language.
What I want to do is edit the script on this page MapPoint VBA Tutorial, Vista Gadgets, Web Site Visitors Map, MapPoint GPS API, more (http://www.mp2kmag.com/update/mappoint.newsletter/2007-04-10/) to use Latitude and Longitude instead of the address.

Thank you for any help.

MapAdmin
10-06-2008, 06:25 PM
Sure, here is the code:



Dim objMap As MapPoint.Map
Private Sub Begin_Click()
Dim objFindResults As MapPoint.FindResults
Dim objLoc As MapPoint.Location
Dim objPushpin As MapPoint.Pushpin

Set oApp = CreateObject("MapPoint.Application.NA")
Set objMap = oApp.NewMap

Dim nCurrentRow As Integer
nCurrentRow = 6

szLatitude = Cells(nCurrentRow, 3)
Do While (szLatitude <> 0)
szName = Cells(nCurrentRow, 2)
If szName = 0 Then
szName = nCurrentRow - 11
End If

szLongitude = Cells(nCurrentRow, 4)
nRadius = Cells(nCurrentRow, 7)
Set objLoc = objMap.GetLocation(szLatitude, szLongitude)
Set objPushpin = objMap.AddPushpin(objLoc, szName)
' draws circle based on radius in column G
objMap.Shapes.AddShape geoShapeRadius, objLoc, nRadius, nRadius
' pop up pushpin name
objPushpin.BalloonState = geoDisplayName

nCurrentRow = nCurrentRow + 1
szLatitude = Cells(nCurrentRow, 3)

Loop

objMap.Saved = True
oApp.Visible = True
objMap.DataSets.ZoomTo
' change all pushpins to small red circle
objMap.DataSets(1).Symbol = 25
objMap.Altitude = objMap.Altitude * 1 'adjust altitude if desired

Exit Sub




The trick is to use the GetLocation method..

Also the spreadsheet with the modified code is attached.

hope this helps!

Eric

dfairbanks
10-07-2008, 12:07 PM
That is awesome, Eric! Thank you very much.

Eric Frost
10-07-2008, 07:57 PM
Glad to be of help! Let us know if it works for you..

By the way, why do you need circles? When I originally wrote the sample code, I did not have any application in mind..

This is also a good suggestion for the MapForums Plus downloads.. I will clean it up and post over there as well.

Eric

dfairbanks
10-08-2008, 12:14 PM
It worked exactly as hoped.

What I am doing is creating coverage maps that show radius distances from 800 plus points. Doing them individually is quite time consuming.

I am curious why it does not give the actual radius. It takes the number provided, say 50 miles, and it returns a 24.8 mile radius circle on the map. It looks like it is returning the diameter of the circle instead of a radius.

There is an easy enough work around and I set up my spreadsheet to compensate for this I just wanted to understand why.

Another question, if you do not mind - Is there a way to code this to work with a map that is already opened instead of opening a new one every time?

Thank you again for your help.

arrancollins
01-26-2010, 08:12 AM
Hi,

I have read the installment of how to create a macro that opens MapPoint and plots an address.

Can anyone help me with the following; instead of opening a new map can an existing mappoint map that has been embedded in the the same excel work book be referenced instead?

Any help on this will be greatly appreciated.

Many thanks

Arran

himanshu.agarwal
05-05-2010, 11:41 AM
Hello everyone !

First of all, fantastic effort putting this resource together. I am overwhelmed by the level of detail that's available.

I tweaked this macro a little to plot a list of ~500 properties that I had on my spreadsheet. Though it works really well, a road block I faced was there are some properties which do not have the address in a perfect format and map point usually provides suggestions in such a case (~44 properties not mapped). When I do this manually, I can review the suggestions and either apply/reject them. But when I use this macro, it just gives me a run time error for the values it couldn't plot and no suggestions pop up.

Any guidance would be deeply appreciated. Thanks !

valz
11-22-2010, 02:51 PM
I have a map set up in Excel and it's linked to data in the spreadsheet. I can't find anywhere how to refresh the map once the data has been updated. I can use VBA to open the object in mapPoint, but I have to manually click the Update Linked Records. Anyone know how to do this?
Thanks in advance for your help.

Eric Frost
11-22-2010, 09:18 PM
Start a new thread.