MapForums

Hi All,

I'm in the process of creating a simple geocoder in Excel 2007 using data obtained from MapPoint 2009 (NA version). I'm using the code below to pass a row of data containing ID, Address, City, "", State, Zip/Postal Code, and Country to MapPoint to create a pushpin. Once created, I'm retrieving the Lat & Lon properties of the pushpin and placing them beside the Country value on the row.

The code works great for US locations whether or not all fields are filled in. The problem is with Canadian locations. For example, 506 Woodcrest Avenue, Oshawa, "", ON, L1J-2T8, CANADA returns the coordinates (38.37860067, -75.54357243) which is located in Salisbury, MD 21804. If I simply type the address info in the MapPoint Advanced toolbar to search for the address, it finds it instantly.

If I enter only a Canadian city-state combination, it returns the city center. If I add anything else, especially postal code, I get all kinds of weird results.

Any suggestions on how I can obtain proper coordinates for Canadian sites?

Thanks

Edit: Here's what I'm passing from Excel

ID Address City State Zip/Postal Code CountryCode
C1 5039 Rue St. Dominique Montreal PQ H2K-4J7 CANADA
C2 506 Woodcrest Avenue Oshawa ON L1J-2T8 CANADA
C3 Oshawa ON L1J-2T8 CANADA


Dim oApp As MapPoint.Application
Private Sub CommandButton1()
'Capture Start Time
[f2] = Time

Set objApp = CreateObject("MapPoint.Application.NA.16")
objApp.Visible = True
Set objMap = objApp.NewMap

'Variable to house starting row number
NRow = 4

'Loop through records until break in column A
Do
On Error Resume Next

Set objloc = _
objMap.FindAddressResults( _
Worksheets("Sheet1").Cells(NRow, 2), _
Worksheets("Sheet1").Cells(NRow, 3), , _
Worksheets("Sheet1").Cells(NRow, 4), _
Worksheets("Sheet1").Cells(NRow, 5), _
Worksheets("Sheet1").Cells(NRow, 6))(1)

objMap.AddPushpin objloc, _
Worksheets("Sheet1").Cells(NRow, 1)
objMap.DataSets.ZoomTo
Lat = objloc.Latitude
Lon = objloc.Longitude
Worksheets("Sheet1").Cells(NRow, 7) = Lat
Worksheets("Sheet1").Cells(NRow, 8) = Lon
NRow = NRow + 1
Loop While Worksheets("Sheet1").Cells(NRow, 1) <> ""

'Capture End Time
[g2] = Time
'Exit out of MapPoint
objApp.ActiveMap.Saved = True
objApp.Quit
End Sub

I don't think if this could be a solution, but I have heard there is a glitch with Canada and it is better to specify "Multi-Country" instead of "Canada" sometimes.. see if that helps.
Eric

__________________

Thanks for the quick response Eric. Within the Country field I tried "Multi-Country" in place of "CANADA", but no joy.

Of the example rows provided earlier, my Canadian sites with addresses are showing up in MD and GA. The one without an address is geocoding in Oshawa, ON; but just the centroid, not the postal code location.

I've worked with MapPoint for a while, but am somewhat new to VBA. Are there other VBA statements I could try using that might allow me to obtain these coordinates?

Andy

I am a beginner in MapPoint 10 and was trying to see if I could obtain geocodes for a list of US addresses provided to me in Excel. I have some programming background, but I am definitely not a pro.

I came across this link that you may find helpful.

Automate Excel Canadian Google Maps In Excel

Thanks.

The code I provided does well geocoding US addresses. You might try using it for your purposes.

Thanks for the suggestion, but connecting to Google Maps or an online service requires internet connectivity and that may not always be practical.

Shouldn't the country parameter in FindAddressResults be a GeoCountry enum, and NOT a string??

So here, you should set it to GeoCountry.geoCountryCanada


If you know it is definitely Canada, you could hard code this.
Or you could have a series of IF statements mapping strings (Canada, USA, US,etc) into GeoCountry enum codes.


Richard

__________________
Winwaed Software Technology LLC
http://www.winwaed.com
See http://www.mapping-tools.com for MapPoint Tools
See the Geoweb Guru for online mapping

Richard,

Thanks for the response. I've been playing around with GeoCountry.geoCountryCaanada and GeoCountry.geoCountryUnitedStates and have had some success. If I change Worksheets("Sheet1").Cells(NRow,6) in my initial code to one of the GeoCountry options above, it will geocode for that one particular country.

However, when I use it within an if-statement, I can't get it to work properly (seems to only want to do Canada if any geocoding is done). I'm not too big into VBA yet, so I'm sure I've either not declared something or have the if-statement in a bind somehow. Here's my latest code.


Dim objApp As MapPoint.Application
Private Sub CommandButton1()
'Capture Start Time
[f2] = Time
Set objApp = CreateObject("MapPoint.Application.NA.16")
objApp.Visible = True
Set objMap = objApp.NewMap

'Variable to house starting row number
NRow = 4
'Loop through records until break in column A
Do
On Error Resume Next


xlCountry = Worksheets("Sheet1").Cells(NRow, 6)
If xlCountry = "USA" Or "US" Then
Set objLoc = _
objMap.FindAddressResults( _
Worksheets("Sheet1").Cells(NRow, 2), _
Worksheets("Sheet1").Cells(NRow, 3), , _
Worksheets("Sheet1").Cells(NRow, 4), _
Worksheets("Sheet1").Cells(NRow, 5), _
GeoCountry.geoCountryUnitedStates)(1)
End If
If xlCountry = "CANADA" Or "CAN" Then
Set objLoc = _
objMap.FindAddressResults( _
Worksheets("Sheet1").Cells(NRow, 2), _
Worksheets("Sheet1").Cells(NRow, 3), , _
Worksheets("Sheet1").Cells(NRow, 4), _
Worksheets("Sheet1").Cells(NRow, 5), _
GeoCountry.geoCountryCanada)(1)
End If

objMap.AddPushpin objLoc, _
Worksheets("Sheet1").Cells(NRow, 1)
objMap.DataSets.ZoomTo
Lat = objLoc.Latitude
Lon = objLoc.Longitude
Worksheets("Sheet1").Cells(NRow, 7) = Lat
Worksheets("Sheet1").Cells(NRow, 8) = Lon
NRow = NRow + 1
Loop While Worksheets("Sheet1").Cells(NRow, 1) <> ""

'Capture End Time
[g2] = Time
'Exit out of MapPoint
objApp.ActiveMap.Saved = True
objApp.Quit
End Sub

I would have defined a country enum variable, set that with the if statement, then pass it in as a variable.

That is shorter than what you have, but I'd still expect your code to work. Is there a problem with case, possibly?


Richard

__________________
Winwaed Software Technology LLC
http://www.winwaed.com
See http://www.mapping-tools.com for MapPoint Tools
See the Geoweb Guru for online mapping

Richard,

Thanks again for the reply. I've looked into the possibility of wrong case, but that is not the issue as I entered the country code value in all caps and the code is set for all caps.

As a temporary workaround, I've gone ahead and created two worksheets -- one for Canadian locations, the other for US locations.


My next goal will be to look at the error handling process so it flags locations that were not found (currently it uses the same coordinates as the last geocoded site) or where it chose the first location (which may not always be the correct location).

Andy

For anybody else having this issue, I've found the solution is to-
1. Check the ResultsQuality of the best match.
2. If it's not an exact match, only pass the first 3 digits of the postal code.

That got our success rate from something like < 20% to closer to 70% on at least 1 customer's data.

You can also try passing the postal code with the city parameter blank, or 3 digits with the city blank, and see what you get.

The reason we frequently didn't get an exact match for rural addresses in Canada is because MapPoint uses the name of the township (or whatever it's called up there) as the city for the pin, instead of the postal city.


Why MapPoint automatically picks the first result as being correct and changes the city name on its own but doesn't when using the API is beyond me.