Quering number of pushpins in a shape

Microsoft MapPoint

Hello everybody,

Has anyone already created an add-in that will allow the user to draw any
type of free form shape and then query the number of pushpins in that shape?

Currently to find the number of pushpins in my shapes, I have to export to
excel, view the results, and then close out of that excel page.

Although it doesn't seem like a major inconveinence, I am doing that 30-40
times, and it adds up, not to mention, slows my computer down by continuously
opening another instance of excel everytime.

I've looked around the web, and even this forum, and it seems that the code
is there, but I am not a programmer, and wondering if anyone already has this
tool as an add-in for MapPoint.

Thank you in advance for your help,
-Alex
Hi there

I’m not a programmer either but with repetitive MapPoint tasks like this
I’ve found it’s very useful to know enough excel vba to be able to automate
things a bit. So for example to address your problem you can set up some code
for a worksheet called “Shapes” something like this

Option Explicit

Dim objApp As MapPoint.Application
Dim objMap As MapPoint.Map
Dim objShape As MapPoint.Shape
Dim objDataSet As MapPoint.DataSet
Dim objDataSets As MapPoint.DataSets
Dim objRecords As MapPoint.Recordset
Dim lngCount As Integer, NDataSets As Integer, NCount As Integer, NRow As
Integer
Dim strDataSet() As String, Total As Integer

Private Sub PointsinFreeForm_Click()

Sheets("Shapes").Select
Cells.Select
Selection.ClearContents

Range("A1").Select
ActiveCell.Value = "Pushpins in the Selected Area"
Selection.Font.Bold = True

Set objMap = GetObject(, "MapPoint.Application.EU.13").ActiveMap

If (objMap.Selection Is Nothing) Then
MsgBox "Sorry - No Shape Selected"
Else
Set objShape = objMap.Selection
If (objShape.Type = GeoShapeType.geoFreeform) Then
Set objDataSets = objMap.DataSets
NDataSets = objDataSets.Count
ReDim strDataSet(1 To NDataSets)
NCount = 1
Total = 0

For Each objDataSet In objDataSets
NRow = NCount + 1
strDataSet(NCount) = objDataSet.Name
Worksheets("Shapes").Cells(NRow, 1).Value = objDataSet.Name
Set objRecords = objDataSet.QueryShape(objShape)
objRecords.MoveFirst
Do While Not objRecords.EOF
lngCount = lngCount + 1
objRecords.MoveNext
Loop
Total = Total + lngCount

Worksheets("Shapes").Cells(NRow, 2).Value = lngCount
NCount = NCount + 1
lngCount = 0
Next


Else
MsgBox "Sorry - Selection wasn't a Freeform"
End If

End If

NRow = NRow + 1
Worksheets("Shapes").Cells(NRow, 1).Value = "TOTAL"
Worksheets("Shapes").Cells(NRow, 2).Value = Total

End Sub

This is rough and ready code but if it works properly then every time you’ve
got a freeform highlighted on the map just press the button and it will
report the number of pushpins in that area by dataset and the total.

If you’ve got no experience in using excel vba with MapPoint here are some
excellent introductory tutorials.

http://www.mp2kmag.com/update/mappoint.newsletter/2007-04-10/

If you scroll a little way down the page you’ll find the 5th tutorial -
Automating MapPoint with Excel VBA - Tutorial 5 - and links to the previous
4.
Thank you David- that worked very well. I just had to change the MapPoint path.
Another question,
is it possible to import that macro as an add-in, so it can run directly
from MapPoint?

Or do you know of a way to anchor excel over another program, not
neccesarily running two windows side by side, but minimizing the excel
workbook with the macro, and have it lay over an empty part of the map?


thank you again for that wonderful code!
Sorry that's getting a bit complicated for me. I know MapPoint has a COM
programming interface so I guess the first is possible, but I've no
experience. I just have an excel workbook with lots of different vba
'utilities' that operate on the active map.