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

Mappoint 2006 - Dealing with importing incorrect postcodes

This is a discussion on Mappoint 2006 - Dealing with importing incorrect postcodes within the MapPoint Desktop Discussion forums, part of the Map Forums category; Hi I have developed an Excel spreadsheet working with MP 2006 EU through VBA that returns the driving distance between ...

  1. #1
    tomevans is offline Junior Member White Belt
    Join Date
    Jan 2009
    Posts
    2

    Mappoint 2006 - Dealing with importing incorrect postcodes

    Hi

    I have developed an Excel spreadsheet working with MP 2006 EU through VBA that returns the driving distance between 2 UK postcodes (also tried and tested with NA version and USA zipcodes). I have a long list of postcodes working out distance between employees home addresses and their work location, but in some cases the postcode has been input incorrectly. In this instance, the VBA asks me to debug or end the macro, whereas I would like it to simply clear the current search and go on to the next one, leaving a blank cell. The current code I'm using is below and the excel sheet simply has Work postcode in Column A, Home postcode in Column B and the returned mileage appears in Column C.

    PHP Code:
    Private Sub CommandButton1_Click()
      
    Dim MPApp As Object
      Set MPApp 
    CreateObject("MapPoint.Application.EU.13")
      
    MPApp.Visible False
      Set objMap 
    MPApp.NewMap
      Set objRoute 
    objMap.ActiveRoute
      Dim myrow
    mycol
      Dim rRng 
    As Range
      myrow 
    1
      mycol 
    1
      
    Do
         
    myrow myrow 1
         szZip1 
    Worksheets("Sheet1").Cells(myrowmycol)
         
    szZip2 Worksheets("Sheet1").Cells(myrowmycol 1)
     
         
    Add route stops and calculate the route
         objRoute
    .Waypoints.Add objMap.FindResults(szZip1).Item(1)
         
    objRoute.Waypoints.Add objMap.FindResults(szZip2).Item(1)
         
    objRoute.Calculate
     
         Worksheets
    ("Sheet1").Cells(myrowmycol 2) = objRoute.Distance
         objRoute
    .Clear
         Set rRng 
    Sheet1.Range("A" myrow 1)
     
       
    Loop Until IsEmpty(rRng.Value)
       
    MPApp.Quit
     
    End Sub 
    If there is no easy solution to this, would it possible for a macro to run through all the postcodes and tell me which are not valid?

    Many thanks

  2. #2
    davidb is offline Senior Member Blue Belt
    Join Date
    Oct 2003
    Location
    London
    Posts
    290

    Re: Mappoint 2006 - Dealing with importing incorrect postcodes

    One approach is to use the ResultsQuality property and if you get a good match then calculate the distance, otherwise continue to the next postcode. Using your code as a base Iíve added in a possibility using FindAddressResults as shown below

    Code:
    Private Sub CommandButton1_Click()
      Dim MPApp As Object
      Set MPApp = CreateObject("MapPoint.Application.EU.13")
      MPApp.Visible = False
      Set objMap = MPApp.NewMap
      Set objRoute = objMap.ActiveRoute
      Dim myrow, mycol
      Dim rRng As Range
      myrow = 1
      mycol = 1
      
      Do
         ErrorValue = 0
         myrow = myrow + 1
         szZip1 = Worksheets("Sheet1").Cells(myrow, mycol)
         szZip2 = Worksheets("Sheet1").Cells(myrow, mycol + 1)
         
                
         Set objFindResults = objMap.FindAddressResults(, , , , szZip1, geoCountryUnitedKingdom)  ' Find the address
            If objFindResults.ResultsQuality = geoFirstResultGood Then ' Test the quality of the result
             objRoute.Waypoints.Add objFindResults(1)
            Else
              ErrorValue = 1
              Worksheets("Sheet1").Cells(myrow, mycol + 3) = szZip1
           End If
         
         Set objFindResults = objMap.FindAddressResults(, , , , szZip2, geoCountryUnitedKingdom)  
           If objFindResults.ResultsQuality = geoFirstResultGood Then
             objRoute.Waypoints.Add objFindResults(1)
           Else
             ErrorValue = 1
             Worksheets("Sheet1").Cells(myrow, mycol + 4) = szZip2
           End If
         
         If Not ErrorValue = 1 Then
           
           objRoute.Calculate
           Worksheets("Sheet1").Cells(myrow, mycol + 2) = objRoute.distance
              
         Else
         End If
         
         objRoute.Clear
         Set rRng = Sheet1.Range("A" & myrow + 1)
         
     
       Loop Until IsEmpty(rRng.Value)
       MPApp.Quit
     
    End Sub
    This code should report distances in column3 and errant postcodes in colmns 4 or 5. I haven't tested it so you need to treat it as a starter for ten. One word or warning - MapPoint will assume good matches even if the postcode is incomplete eg. KT17, KT17 4, and KT17 4BL will all receive good matches in terms of results quality.

    Rgds
    David
    UK mapping and map analysis services at www.broomanalysis.plus.com

  3. #3
    tomevans is offline Junior Member White Belt
    Join Date
    Jan 2009
    Posts
    2

    Re: Mappoint 2006 - Dealing with importing incorrect postcodes

    David

    I can't thank you enough. It worked straight away with no alterations needed - my headache has now gone away!

    Many thanks

    Tom

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Route with highway's gives incorrect result
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 10
    Last Post: 09-28-2009, 10:48 AM
  2. Importing LatLongs and postcodes from excel
    By apgriffiths in forum Development
    Replies: 3
    Last Post: 07-04-2008, 09:52 AM
  3. Importing new layers in Mappoint Europe 2006
    By Jama in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 10-30-2006, 03:10 PM
  4. Incorrect locations?
    By harris00 in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 05-25-2004, 11:40 PM
  5. MapPoint Street Incorrect
    By cathsaigh in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 08-29-2002, 12:10 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