I have used the tutorial posted in the newsletter to write some code which allows me to plot an address from a spreadsheet onto MapPoint by way of a pushpin by using a command button through VBA. I am new to VBA so do not understand the coding (yet!) and what I would like to do is to expand the number of address to show on the map. the code is as follows:

Dim oApp As MapPoint.Application

Private Sub CommandButton1_Click()

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

Set objLoc = _
objMap.FindAddressResults( _
Worksheets("Sheet1").Cells(7, 2), _
Worksheets("Sheet1").Cells(7, 3), , _
Worksheets("Sheet1").Cells(7, 4), _
Worksheets("Sheet1").Cells(7, 5))(1)

objMap.AddPushpin objLoc, _
Worksheets("Sheet1").Cells(2, 1)
'objMap.DataSets.ZoomTo

End Sub

I would like to add a few more lines of address. For instance, 233 South Wacker Drive, Chicago, IL, 60606.

Can someone please advise as to the changes needed topt the code above.

Thanks very much.

Dingo.

Have a good weekend all.

I have added the Set ObjLoc and objMap part of the code again and amended the ref and now have what I was originally after.

However - how would i simplify the process if i had multiple locations ?(1,000+) As i wouldn't want to add/amend for each location.

Any help on the expansion of the code would be much appreciated.

Thanks again

Dingo

Hi Dingo,

You are in London. You would use a For...Next Loop. Check in the Programming / VBA section of the Help file... just seach for For..Next or you might find it in the Control Structures section.

You would do something like -

For i = 1 to 1000
Set objLoc = _
objMap.FindAddressResults( _
Worksheets("Sheet1").Cells(6+i, 2), _
Worksheets("Sheet1").Cells(6+i, 3), , _
Worksheets("Sheet1").Cells(6+i, 4), _
Worksheets("Sheet1").Cells(6+i, 5))(1)

objMap.AddPushpin objLoc, _
Worksheets("Sheet1").Cells(2, 1)
Next

Eric

Thanks Eric - It works a treat now.

I am also setting up a European map and have tried to replocate the same file/map for this.

I have changed the refernce to the 'Europe map' instead of the NA map and changed the sheet1 selection to the relevant European mapping, but I am getting the following error message from the following code:

'Runtime Error - The requested member of the collection does not exist. Use a valid name or index number.'

From the code:
Dim oApp As MapPoint.Application

Private Sub CommandButton1_Click()

Set oApp = CreateObject("MapPoint.Application.EU.11")
oApp.Visible = True
Set objMap = oApp.NewMap

For i = 1 To 15
Set objLoc = _
objMap.FindAddressResults( _
Worksheets("Sheet1").Cells(6 + i, 2), _
Worksheets("Sheet1").Cells(6 + i, 3), _
Worksheets("Sheet1").Cells(6 + i, 4), _
Worksheets("Sheet1").Cells(6 + i, 5))(1)

objMap.AddPushpin objLoc, _
Worksheets("Sheet1").Cells(6, 1)
Next

End Sub

Can you advise as to where I have gone wrong please.

As always, thanks for your help.

Dingo.

I'm having the same problem (I'm new to MapPoint and VB)

I believe the that the problem is with the 1 in brackets on it's own
I'm assuming that that is the index number, I also assume that it needs to increment but I don't know how

I'm using the following code

Dim oApp As MapPoint.Application

Private Sub CommandButton1_Click()

Set oApp = CreateObject("MapPoint.Application.EU.11")
oApp.Visible = True
Set objMap = oApp.NewMap

For i = 2 To 300

Set objLoc = _
objMap.FindAddressResults( _
Worksheets("Sheet1").Cells(i, 2), _
Worksheets("Sheet1").Cells(i, 3), , _
Worksheets("Sheet1").Cells(i, 4), _
Worksheets("Sheet1").Cells(i, 5))(1)

objMap.AddPushpin objLoc, _
Worksheets("Sheet1").Cells(i, 1)
objMap.Shapes.AddTextbox objLoc, 50, 30
objMap.Shapes.Item(1).Text = Worksheets("Sheet1").Cells(i, 6)

Next i

End Sub

No. The one (1) tells the program to use the first record of the set of records returned by the FindAddressResults method. While this is therefore an index, you'd never really want to increment this number unless you were wanting to loop through the results looking for something other than the first record returned. This code may help:

Code:

  Sub FindAddressSearch()
  Dim objApp As New MapPoint.Application
  Dim objFindResults As MapPoint.FindResults

  'Set up application
  objApp.Visible = True
  objApp.UserControl = True

  'Output first result of find search
  Set objFindResults = objApp.ActiveMap.FindAddressResults("One Microsoft Way", "Redmond", , "WA", , geoCountryUnitedStates)
  MsgBox "The first item in the find list is: " _
    + objFindResults.Item(1).Name

'is the same as 
  Set objLoc = objApp.ActiveMap.FindAddressResults("One Microsoft Way", "Redmond", , "WA", , geoCountryUnitedStates)(1)
  MsgBox "The first item in the find list is: " _
    + objLoc.Name

  End Sub

Ciao,