+ Reply to Thread
Results 1 to 4 of 4

Geocoding

  1. #1
    Registered User
    Join Date
    02-15-2017
    Location
    PA
    MS-Off Ver
    2017
    Posts
    2

    Geocoding

    Hi Guys,

    I am trying to retrieve longitude and latitudes for set addresses. I came across this module which works perfectly! The only problem is, I am limited to 2500 entries a day. I have set a billing record and received an API so that I can increase my quota, however I have no idea how to adjust the code to insert my API and have it work. I have copied the code under the text, any help will be greatly appreciated!


    Option Explicit

    Function GetCoordinates(Address As String) As String

    '-----------------------------------------------------------------------------------------------------
    'This function returns the latitude and longitude of a given address using the Google Geocoding API.
    'The function uses the "simplest" form of Google Geocoding API (sending only the address parameter),
    'so, optional parameters such as bounds, key, language, region and components are NOT used.
    'In case of multiple results (for example two cities sharing the same name), the function
    'returns the FIRST OCCURRENCE, so be careful in the input address (tip: use the city name and the
    'postal code if they are available).

    'NOTE: As Google points out, the use of the Google Geocoding API is subject to a limit of 2500
    'requests per day, so be careful not to exceed this limit.
    'For more info check: https://developers.google.com/maps/d...tion/geocoding

    'In order to use this function you must enable the XML, v3.0 library from VBA editor:
    'Go to Tools -> References -> check the Microsoft XML, v3.0.

    'Written by: Christos Samaras
    'Date: 12/06/2014
    'e-mail: [email protected]
    'site: http://www.myengineeringworld.net
    '-----------------------------------------------------------------------------------------------------

    'Declaring the necessary variables. Using 30 at the first two variables because it
    'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll).
    Dim Request As New XMLHTTP30
    Dim Results As New DOMDocument30
    Dim StatusNode As IXMLDOMNode
    Dim LatitudeNode As IXMLDOMNode
    Dim LongitudeNode As IXMLDOMNode

    On Error GoTo errorHandler

    'Create the request based on Google Geocoding API. Parameters (from Google page):
    '- Address: The address that you want to geocode.
    '- Sensor: Indicates whether your application used a sensor to determine the user's location.
    'This parameter is no longer required.
    Request.Open "GET", "http://maps.googleapis.com/maps/api/geocode/xml?" _
    & "&address=" & Address & "&sensor=false", False

    'Send the request to the Google server.
    Request.send

    'Read the results from the request.
    Results.LoadXML Request.responseText

    'Get the status node value.
    Set StatusNode = Results.SelectSingleNode("//status")

    'Based on the status node result, proceed accordingly.
    Select Case UCase(StatusNode.Text)

    Case "OK" 'The API request was successful. At least one geocode was returned.

    'Get the latitdue and longitude node values of the first geocode.
    Set LatitudeNode = Results.SelectSingleNode("//result/geometry/location/lat")
    Set LongitudeNode = Results.SelectSingleNode("//result/geometry/location/lng")

    'Return the coordinates as string (latitude, longitude).
    GetCoordinates = LatitudeNode.Text & ", " & LongitudeNode.Text

    Case "ZERO_RESULTS" 'The geocode was successful but returned no results.
    GetCoordinates = "The address probably not exists"

    Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day.
    GetCoordinates = "Requestor has exceeded the server limit"

    Case "REQUEST_DENIED" 'The API did not complete the request.
    GetCoordinates = "Server denied the request"

    Case "INVALID_REQUEST" 'The API request is empty or is malformed.
    GetCoordinates = "Request was empty or malformed"

    Case "UNKNOWN_ERROR" 'Indicates that the request could not be processed due to a server error.
    GetCoordinates = "Unknown error"

    Case Else 'Just in case...
    GetCoordinates = "Error"

    End Select

    'In case of error, release the objects.
    errorHandler:
    Set StatusNode = Nothing
    Set LatitudeNode = Nothing
    Set LongitudeNode = Nothing
    Set Results = Nothing
    Set Request = Nothing

    End Function

    '--------------------------------------------------------------------------
    'The next two functions using the GetCoordinates function in order to get
    'the latitude and the longitude correspondingly of a given address.
    '--------------------------------------------------------------------------

    Function GetLatidue(Address As String) As Double

    Dim Coordinates As String

    'Get the coordinates for the given address.
    Coordinates = GetCoordinates(Address)

    'Return the latitude as number (double).
    If Coordinates <> "" Then
    GetLatidue = CDbl(Left(Coordinates, WorksheetFunction.Find(",", Coordinates) - 1))
    End If

    End Function

    Function GetLongitude(Address As String) As Double

    Dim Coordinates As String

    'Get the coordinates for the given address.
    Coordinates = GetCoordinates(Address)

    'Return the longitude as number (double).
    If Coordinates <> "" Then
    GetLongitude = CDbl(Right(Coordinates, Len(Coordinates) - WorksheetFunction.Find(",", Coordinates)))
    End If

    End Function

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,471

    Re: Geocoding

    http://www.myengineeringworld.net/20...oogle-api.html

  3. #3
    Registered User
    Join Date
    02-15-2017
    Location
    PA
    MS-Off Ver
    2017
    Posts
    2

    Re: Geocoding

    Thank you so much Logit for taking the time to answer. I am still not able to find out how to fix the problem from the website you provided. I need to add my own API in order to overide the daily limit. Any idea on where and how I should input my own API into the code.

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,471

    Re: Geocoding

    Sorry . ... but no, I can't assist you with your question. Insufficient knowledge in that area.

    I recall seeing several posts on a few Excel Forums regarding this area. Suggest a search of each of those forums.

    VBA Express

    Excel Guru

    Mr. Excel

    Chandoo.org

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Reverse Geocoding in Excel, not just for the address
    By angomera in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-12-2017, 12:49 PM
  2. Reverse Geocoding in Excel With Google API
    By angomera in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2017, 06:03 PM
  3. Help woth geocoding, or reverse geocoding
    By margal in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-19-2016, 11:30 AM
  4. [SOLVED] GeoCoding - Find the closest point
    By ZiadzExcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-14-2015, 03:44 PM
  5. [SOLVED] Using the Google Geocoding API in Excel
    By russkris in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-12-2014, 01:17 AM
  6. Reverse Geocoding Excel Help (see attachment)
    By zsemago in forum Excel General
    Replies: 1
    Last Post: 02-28-2012, 03:40 PM
  7. [SOLVED] Neat tool for mapping (geocoding) addresses straight from Excel
    By Phillip Holmstrand in forum Excel General
    Replies: 0
    Last Post: 01-23-2006, 08:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1