Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 6 of 6

Excel & MapPoint

This is a discussion on Excel & MapPoint within the MapPoint Desktop Discussion forums, part of the Map Forums category; I have used the tutorial posted in the newsletter to write some code which allows me to plot an address ...

  1. #1
    Dingo1 is offline Junior Member White Belt
    Join Date
    Oct 2004
    Posts
    6

    Excel & MapPoint

    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.

  2. #2
    Dingo1 is offline Junior Member White Belt
    Join Date
    Oct 2004
    Posts
    6

    Excel

    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

  3. #3
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4
    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
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  4. #4
    Dingo1 is offline Junior Member White Belt
    Join Date
    Oct 2004
    Posts
    6

    Map Point in XL

    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.

  5. #5
    Pete_ADL is offline Junior Member White Belt
    Join Date
    Dec 2005
    Posts
    3
    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

  6. #6
    calv1ns's Avatar
    calv1ns is offline Member Green Belt
    Join Date
    Mar 2005
    Posts
    91
    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,
    Calv1ns

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. mappoint and excel
    By mike_v in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 05-30-2006, 03:35 PM
  2. Help W Excel Mappoint VBA
    By Eric_j_wick in forum MapPoint Desktop Discussion
    Replies: 9
    Last Post: 04-24-2006, 11:49 AM
  3. Excel and MapPoint
    By Eric Frost in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 05-09-2005, 10:01 PM
  4. Mappoint & Excel
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 10-27-2004, 04:38 AM
  5. MPControl embedded in excel - excel loses functionality
    By ruyasan in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 05-20-2004, 04:41 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96