PDA

View Full Version : Getting PostalCode/City from latitude/longitude in excel?



rassbech
01-27-2014, 09:01 AM
I have rows and coloums of latitude and longitude values in my excel sheet. And I need to get the location (PostalCode and City) from these values from MapPoint down to my Excel sheet by using some VBA coding.

I know how to get the calculated distances etc based on the coordinates, but I am having a hard time getting the Postal Code and City from the respective coordinates.

I know I have to use the classes; StreetAddress.PostalCode; but it keeps spamming me with errors etc.

This is my code so far (without my failure attempts for the Postals/Cities):



Sub Coordinates_Mappoint_VBA()
Dim APP As New MapPoint.Application
Dim MAP As MapPoint.MAP
Dim RTE As MapPoint.Route
Dim row As Integer

Set MAP = APP.ActiveMap
APP.Visible = True
APP.UserControl = True
MAP.Parent.PaneState = geoPaneRoutePlanner

Set RTE = MAP.ActiveRoute
RTE.Clear

row = 5
Do While Cells(row, 2) <> ""
RTE.Clear
LatX = Cells(row, 1).Value
LongX = Cells(row, 2).Value
LatY = Cells(row, 5).Value
LongY = Cells(row, 6).Value

With RTE.Waypoints
Dim locX As MapPoint.Location
Dim locY As MapPoint.Location
Set locX = MAP.GetLocation(LatX, LongX)
.Add locX
Set locY = MAP.GetLocation(LatY, LongY)
.Add locY
End With

RTE.Calculate
Worksheets("GPS Makro").Cells(row, 9) = RTE.Distance
row = row + 1
Loop

End Sub

If anyone has a smart way to do it, I would be very happy :)

Eric Frost
01-27-2014, 11:17 AM
I don't think you'll be able to use StreetAddress since it's not a matched address location.

Take a look at ObjectsFromPoint. I did a couple projects for people in the last year and it worked fairly well.

I think I was going to write an article showing some examples but never got around to it.

If you are interested, we could use some of your example data to show how to effectively use ObjectsFromPoint, what do you think?

Eric