0

I have an excel program that sends an address to google maps geocoder and it receives an xml response in which I pull out the lat/long. I am running into some difficulty in dealing with the locale of both excel and I believe whatever browser the end user is using. Currently any request sent to the API in english receives a valid response. It works exceptionally well when the request is sent in english for a US address. The program does not seem to work when sending an API request in a foreign language. Is it possible to change my API request so that google knows the input is in a foreign language? I would still like to receive the xml response in english however.

Also related: The address string that I build in excel is based on the US format - Address Number, Address Street, City, State, Zip, Country. When a foreign user is sending this to the API, they will not get the correct response because their country uses a different address format. Is there a universal address format that google can recognize?

Thank you. Let me know if I need to provide additional info.

Edit: Here is an example of what I am talking about.

Request 1 - Address: 81241 München Germany - (Fails)

Request 1 - API Request: https://maps.googleapis.com/maps/api/geocode/xml?address=81241%20M%FCnchen%20Germany&sensor=false

Request 2 - Address: 81241 Munchen Germany - (Succeeds)

Request 2 - API Request: https://maps.googleapis.com/maps/api/geocode/xml?address=81241%20Munchen%20Germany&sensor=false

This is an example for Germany, but it could potentially be issues elsewhere that uses alternative characters. The URL encoding function I am using is below. I am thinking this may be part of the issue.

Now that I am reviewing this... perhaps it is because this function does not support UTF-8?

Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String

Dim StringLen As Long: StringLen = Len(StringVal)

If StringLen > 0 Then
ReDim result(StringLen) As String
Dim i As Long, CharCode As Integer
Dim Char As String, Space As String

If SpaceAsPlus Then Space = "+" Else Space = "%20"

For i = 1 To StringLen
  Char = Mid$(StringVal, i, 1)
  CharCode = Asc(Char)
  Select Case CharCode
    Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
      result(i) = Char
    Case 32
      result(i) = Space
    Case 0 To 15
      result(i) = "%0" & Hex(CharCode)
    Case Else
      result(i) = "%" & Hex(CharCode)
  End Select
Next i
URLEncode = Join(result, "")
End If
End Function
Community
  • 1
  • 1
Citanaf
  • 445
  • 2
  • 7
  • 22
  • What does the geocoder request look like? Are you using the web service or the Google Maps Javascript API v3? – geocodezip Jul 30 '15 at 21:05
  • I dont use stackoverflow often.. having comment issues. I removed my key from the above request. I believe I am using the web service – Citanaf Jul 30 '15 at 21:55
  • Please address comments by editing the question to include the requested information. – geocodezip Jul 30 '15 at 22:08
  • I think your guess is correct(UTF-8), because in UTF-8 you'll get the result: [`https://maps.googleapis.com/maps/api/geocode/xml?address=81241%20M%C3%BCnchen%20Germany`](https://maps.googleapis.com/maps/api/geocode/xml?address=81241%20M%C3%BCnchen%20Germany) Maybe you'll find a solution here: http://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba – Dr.Molle Jul 30 '15 at 23:07

0 Answers0