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 4 of 4

VBA to Find Nearest Custom Pushpin

This is a discussion on VBA to Find Nearest Custom Pushpin within the Development forums, part of the MapPoint Desktop Discussion category; I recently posted this in the Desktop Discussion forum, but I've hit a bit of a brick wall. I'm trying ...

  1. #1
    nickshep85 is offline Junior Member Yellow Belt
    Join Date
    May 2010
    Posts
    13

    VBA to Find Nearest Custom Pushpin

    I recently posted this in the Desktop Discussion forum, but I've hit a bit of a brick wall.

    I'm trying to use VBA to automate a process whereby I can input a UK postcode and find the nearest custom pushpin, in my case nearest Centre.

    Ideally, I'd like to find the three nearest locations in order, then I could use a VLookup to find the centre type (Cluster, Truck, Retail). I've exported my custom pushpins to Excel so that I can lookup the information that I need.

    My problem here is that I'm very new to VBA and coding. I've been told that on a scale of 1-10, 10 being expert level at VBA, I'm about 1.5 and what I'm trying to achieve is about a 9!

    Below is the code which I have got so far, the only thing I have in my spreadsheet is "Postcode" as a header in A1, the rest of the headers are added by the macro.

    Can anyone help me with this, I'm fumbling through as much as I can, but I might be old and grey by the time I'm finished.



    Code:
    Sub FindNearbyPlaces()
    
    Dim objApp As New MapPoint.Application
    Dim objMap As MapPoint.Map
    Dim objRoute As MapPoint.Route
    Dim objNearby As MapPoint.Waypoint
    Dim objLoc As MapPoint.Location
    
    Set objApp = CreateObject("Mappoint.Application.EU.13")
    objApp.Visible = False
    Set objMap = objApp.OpenMap("C:\Program Files\Microsoft MapPoint Europe\Centres By Type.ptm", False)
    
    
    'Insert Column Headers
    Sheets("Sheet1").Cells(1, 2).Value = "Nearest Centre"
    Sheets("Sheet1").Cells(1, 3).Value = "Centre Type"
    Sheets("Sheet1").Cells(1, 4).Value = "2nd Nearest Centre"
    Sheets("Sheet1").Cells(1, 5).Value = "Centre Type"
    Sheets("Sheet1").Cells(1, 6).Value = "3rd Nearest Centre"
    Sheets("Sheet1").Cells(1, 7).Value = "Centre Type"
    NReadRow = 2
    
    Do While Sheets("Sheet1").Cells(NReadRow, 2) <> ""
    
    'Locate Start Point
    Set objLoc = objMap.FindResults(Sheets("Sheet1").Cells(NReadRow, 1)).Item(1)
    
    'Find Nearest Centre and Show Results In Spreadsheet
    Sheets("Sheet1").Cells(NReadRow, 2) = objMap.DataSets.Item(1).Name
    
    
    NReadRow = NReadRow + 1
    
    Loop
    
    
    
    End Sub

  2. #2
    Wilfried is offline Senior Member Black Belt
    Join Date
    Nov 2004
    Location
    Belgium
    Posts
    2,433

    Re: VBA to Find Nearest Custom Pushpin

    Hi,

    You don't tell us what the problem is!

  3. #3
    nickshep85 is offline Junior Member Yellow Belt
    Join Date
    May 2010
    Posts
    13

    Re: VBA to Find Nearest Custom Pushpin

    My problem is I don't know where to go with the coding from here on in. I'm not entirely sure that my "find Nearest Centre and Show in Spreadsheet" line will work.

    Do you know how I'd continue from this point? I need to give the name of the nearest custom pushpin to any given location.

  4. #4
    Wilfried is offline Senior Member Black Belt
    Join Date
    Nov 2004
    Location
    Belgium
    Posts
    2,433

    Re: VBA to Find Nearest Custom Pushpin

    Hi,

    It is not completely clear to me what you try to do but maybe this can help:

    FindResults does not return a Location object but a FindResults collection. You have to step trough this collection to find what you want. It is a collection of Location objects and Pushpin objects. So when you loop in it you have to pick out the object you wants.

    DataSet(Item).Name will return the name of the particular dataset. To get the name of the pushpins in the dataset you have to loop trough the dataset to find all the pushpins. Is that what you want?

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA To Find Nearest Custom Location
    By nickshep85 in forum MapPoint Desktop Discussion
    Replies: 11
    Last Post: 06-03-2010, 08:35 AM
  2. Find the nearest address.
    By VC_Man in forum MapPoint Desktop Discussion
    Replies: 4
    Last Post: 02-28-2007, 02:58 AM
  3. Custom location returns null when using find nearby in route
    By briansbrown in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 11-15-2005, 02:40 PM
  4. find nearest locality
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 07-09-2003, 04:26 AM
  5. How to find nearest city?
    By Petr Brant in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 08-23-2002, 02:49 PM

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 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127