Using MapPoint from Word, Access and Excel

Subscribe

Magazine

Front Cover
What's New
Articles
News
Sample Data
Gallery
Advertise
About

Features

MapPoint 2013
Press Releases
MapPoint Forums
Companies
Link to MP2Kmag
Wish List
MapPoint Trial
Authors

Earlier Content

Past News Items
Past What's New Announcements

Sponsors

Order

MapPoint 2013

Programming MapPoint in .NET

MapPoint Book

Spatial Community

SVG Tutorials
MapPoint


	ARTICLES

This article contains code snippets that allows you to seamlessly drop a map graphic into your Word, Access or Excel documents anywhere you have a valid address.

Using MapPoint From Word

MapPoint can be used from within Word. This code snippet allows you to seamlessly drop a map graphic into your Word documents anywhere you have a valid address. There is no reason to open MapPoint manually, enter the address, geocode it, copy the map to the clipboard, and then paste it into Word. This snippet does all that for you with a single mouse click.

1) Create a Macro in Normal.dot with this code:


Sub CreateMap()
    Dim oApp As MapPoint.Application        'Create all the variables
    Dim oMap As MapPoint.Map
    Dim oPush As MapPoint.Pushpin
    Dim oLoc As MapPoint.Location
    Dim mySelect
    
    mySelect = Selection.Text	'Make sure you select the address text before clicking the macro
    If Len(mySelect) < 2 Then Exit Sub	'If the text is less than 2 characters, we exit the macro
    
    Set oApp = CreateObject("Mappoint.Application")	
    Set oMap = oApp.NewMap		'Open and create a new map
    
    Set oLoc = oMap.Find(mySelect)	'Find the address that is highlighted in Word
    If Not oLoc Is Nothing Then		'If MapPoint finds it, then proceed
        Set oPush = oMap.AddPushpin(oLoc)	'Create a PushPin for our location
        oPush.GoTo			
    
        oPush.Highlight = True
        
        oMap.DataSets(1).ZoomTo		
        oMap.CopyMap			'Copy the map to the clipboard
        Selection.MoveDown		'In Word, move down to the next paragraph
        Selection.TypeParagraph		'Select the paragraph
        Set myPic = ActiveDocument.InlineShapes.New(Selection.Range)	'Create a new shape in Word
        myPic.Select			
        Selection.Paste			'Paste our map into the shape we created
        Selection.TypeParagraph		'Reselect the entire paragraph
    Else
        MsgBox "Location not found."	'Oops.  Can't find the address!
    End If
    
    Set oPush = Nothing		'Clean Up
    Set oMap = Nothing
    Set oApp = Nothing
End Sub

2) Add the CreateMap Macro to your toolbar by right clicking on any toolbar and selecting customize.

3) Type a fully qualified address and click the macro.

"6531 Chesterbrook Drive, Sacramento, CA" works fine
		"6531 Chesterbrook Drive" does not work
"300 Indiana Avenue NW, Washington, DC" works great
"300 Indiana Avenue NW" does not because there are two. (MI and DC)

4) When you click the macro button on the toolbar, Word will contact MapPoint. MapPoint will then proceed to find the point and copy the map directly into your Word document.

Using MapPoint from Access forms

(MapDemo.mdb)

Microsoft Access can display MapPoint maps just like Word can. The macro is very similar to that of Word. Here, we have a form that can capture address information into a table. We can use that address information to generate a map as well as driving directions. (NOTE: For this demonstration, you can either create a database from scratch or use the accompanying MapDemo.mdb)

1) Create a form with Address information:
a. Fields for First Address: (Address, City, State)
b. Fields for Second Address: (Address2, City2, State2)
c. Field for Address Name: myName
d. Empty OLE Image (imgClip)
e. Command Button (cmdMap)

2) Add this code:

Private Sub cmdMap_Click()
	'Define variables to hold our information.
	'oPush(1) & oLoc(1)  are the first address
	'oPush(2) & oLoc(2) are the second address
    Dim oApp As Object, oMap As Object, oPush(1 To 2) As Object, oLoc(1 To 2) As Object
    
    Set oApp = CreateObject("Mappoint.Application")
    Set oMap = oApp.NewMap	'Create a new map
    
    Set oLoc(1) = oMap.Find(Address & " , " & City & " , " & State)	'Geocode the first address
    Set oLoc(2) = oMap.Find(Address2 & " , " & City2 & " , " & State2)  'Geocode the second address

    If Not oLoc(1) Is Nothing Then		'Check to see if the first address was found.
        Set oPush(1) = oMap.AddPushpin(oLoc(1))	'Place a pushpin on the map
        oPush(1).Goto				'Go to that pushpin

    'Replace the name of the pushpin with the myName from the database
        If Not IsNull(MyName) Then oPush(1).Name = MyName
        oPush(1).Highlight = True
        
        If Not oLoc(2) Is Nothing Then		'Check to see if the second address was found
            Set oPush(2) = oMap.AddPushpin(oLoc(2))	'Add a pushpin for it
        
            oPush(2).Highlight = True
        
            With oMap.ActiveRoute		'Let's generate some directions between the points
                .Waypoints.Add oLoc(1)	'Add the first address
                .Waypoints.Add oLoc(2)	'Add the second address
                .Calculate			'Now find the directions between them
            End With
            oMap.CopyDirections		'Copy the directions to the clipboard
            txtDir.SetFocus		'Set the focus to the Access form's txtDir object, a textbox

'Run the Access command to paste the directions from the clipboard
            RunCommand acCmdPaste	

        Else				'If the second location is not found, then indicate no directions
            txtDir.SetFocus
            txtDir.Text = "No second location found!"
        End If
            
	'Time to place the map into Access
        oMap.DataSets(1).ZoomTo		'Zoom in so that the entire map with directions is viewed
        oMap.CopyMap			'Copy the map to the clipboard
        imgClip.Visible = True	'Change the image  place holder in the Access form to visible
        imgClip.Action = acOLEPaste	'Paste the map from the clipboard to the image
        
        lblMapInfo.Caption = ""		'Erase the Map Information label
    Else
        lblMapInfo.Caption = "Address Not Found!"	'Oops, can't find the addresses!
    End If
    Set oMap = Nothing
    Set oApp = Nothing
    Me.SetFocusEnd Sub

Using MapPoint from Excel

(MapDemo.xls)

We will use the same manner in which Microsoft Access accessed MapPoint and created a map in Excel. Excel will store the picture of the map right on our spreadsheet. In this example, we create a command button to launch MapPoint in the background and paste a copy of the map to the spreadsheet.

1) Add a command button to your spreadsheet.
2) Copy and paste the following code to your new command button's click event:

Sub Button1_Click()
    Dim oApp As Object 'MapPoint.Application
    Dim oMap As Object 'MapPoint.Map
    Dim oPush As Object 'MapPoint.Pushpin
    Dim oLoc As Object 'MapPoint.Location
    Dim myAdd, myCity, myState, myZip, myFind, myRow As Integer
    
	'Check to see if the map already exists.  If so, delete it from the spreadsheet
    maxshapes = ActiveSheet.Shapes.Count
    For x = 2 To maxshapes
        ActiveSheet.Shapes(x).Delete
    Next x
    
    myRow = Selection.Row		'Get the currently selected row
    myAdd = Cells(myRow, 2)		'Use the first column as the address info
    myCity = Cells(myRow, 3)		'Use the second column as the city info
    myState = Cells(myRow, 4)		'Use the third column as the state info
    myZip = Cells(myRow, 5)		'Use the fourth column as the zip info

	'The first column on the spreadsheet is a blank used for the confirmation info below
    Cells(myRow, 1).Value = ""
    Cells(myRow, 1).Interior.ColorIndex = xlColorIndexNone	'Set the background
    
    If Len(myAdd) < 2 Then Exit Sub	'If the address is less than 2 characters, exit
    
    Set oApp = CreateObject("Mappoint.Application")
    Set oMap = oApp.NewMap		'Create the new map
    myFind = myAdd & "," & myCity & "," & myState	'Create the address string
    Set oLoc = oMap.Find(myFind)	'Find the new address
    If Not oLoc Is Nothing Then		'If found, start processing
        Cells(myRow, 1).Value = "Found!"	'Change the first column to indicate found
        Cells(myRow, 1).Interior.ColorIndex = 4	'Change the color to Green
        
        Set oPush = oMap.AddPushpin(oLoc)	'Create a pushpin on the map
        oPush.Goto
        oPush.Highlight = True
        oMap.DataSets(1).ZoomTo
        
        oMap.CopyMap			'Copy the map to the clipboard
        Cells(myRow, 7).Select		'Select the seventh column of this row
        ActiveSheet.Paste		'Paste the map into the spreadsheet

'Hide the pesky picture toolbar!
        Application.CommandBars("Picture").Visible = False	
        Cells(myRow, 2).Select		'Reselect our address field
    Else
        Cells(myRow, 1).Value = "Not Found!"	'Didn't find the address
        Cells(myRow, 1).Interior.ColorIndex = 3	'Set color to red
    End If
    
    Set oLoc = Nothing
    Set oPush = Nothing
    Set oMap = Nothing
    Set oApp = Nothing
End Sub

3) Save the spreadsheet!
4) On the B column, enter the address.
5) On the C column, enter the city
6) On the D column, enter the state
7) On the E column, enter the zip
8) Press the Command Button

Discuss this story in the forum.

Author: Christopher Gebhardt
Email: Chris(AT)CrimeAnalysis.net
URL: http://www.crimeanalysis.net
Chris Gebhardt is the Director of Marketing for CRISNet (www.crisnet.com) in Salt Lake City, Utah. He is also the CEO of Crime Analysis Associates (www.CrimeAnalysis.net) where he uses Microsoft products and specifically MapPoint in the creation of his Crime Analysis software.

Chris is a former Lieutenant with the Metropolitan Police Department in Washington, D.C. where he was the Commander of the Tactical Crime Analysis Unit responsible for crime analysis and crime mapping. This job is the basis for the TV show "The District."

In his spare time, Chris is the Technical Advisor to the California Crime and Intelligence Analysts Association (www.CrimeAnalyst.org).

Chris can be reached at chris@crimeanalysis.net.

Recent Discussion

Resources

Browse GIS books and periodicals
Find a MapPoint Partner or Consultant
Real Estate Columbia, MO Real Estate

Want Your Site To Appear Here?

© 1999-2012 MP2K. Questions and comments to: website@mp2kmag.com
Microsoft and MapPoint 2002/2004/2006/2009/2010/2011/2013 are either trademarks or registered trademarks of Microsoft.