+ Reply to Thread
Results 1 to 10 of 10

Analyze Google API for latitude & longitude of adress - Regex.pattern issue

  1. #1
    Registered User
    Join Date
    10-03-2017
    Location
    Hamburg
    MS-Off Ver
    2007
    Posts
    3

    Analyze Google API for latitude & longitude of adress - Regex.pattern issue

    Hi all!

    I'm currently stuck with this Google API Query.
    As you may know you can get information about longitude, latitude etc. via Google API. This is done via this web query: "https://maps.googleapis.com/maps/api/geocode/json?address=NewYorkUSA" (example for New York).

    But in my excel sheet I have many other cities left, therefore I'd like to do this automatically. It actually works nearly perfect with a slight modification of this guys' code: http://analystcave.com/excel-calcula...een-addresses/
    And yes, I have seen his solution to getting the longitudes and latitudes. But that is written as a SUB and not as a function. Unfortunately I need this as a function for my specific usage :/


    My modified code:

    Please Login or Register  to view this content.
    But if I then start this function as following, the value of the function is "40".
    But the difference of one degree of latitude equals 110km, while I need an exact analysis of all digits.

    City Latitude
    New York =GetLatitude(A2)=40

    Therefore I fooled a bit around with following passage:
    Please Login or Register  to view this content.
    In my understanding the issue here is the search pattern which only gives whole numbers as output, therefore I changed it to:
    Please Login or Register  to view this content.
    I even checked it with a regex-analyzer and it worked. Unfortunately not with Excel 2007.


    Has someone maybe an idea, how Excel could deliver the output with the whole decimal number?

    Many thanks!!

    Best Regards,
    Lasse
    Last edited by Lasse97; 10-04-2017 at 04:36 PM.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Analyze Google API for latitude & longitude of adress - Regex.pattern issue

    1)
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    2) Try change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-03-2017
    Location
    Hamburg
    MS-Off Ver
    2007
    Posts
    3

    Re: Analyze Google API for latitude & longitude of adress - Regex.pattern issue

    Thanks for your answer!

    Just edited the thread to comply with the forum rules - sorry about that!

    Unfortunately I do still get an error with this pattern. To make it a bit easier, I've attached an little example with the city New York.
    The VBA macro is in module 1 - just as mentioned in this thread. Nothing else attached.
    Attached Files Attached Files

  4. #4
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Analyze Google API for latitude & longitude of adress - Regex.pattern issue

    I don't know about an error. Maybe not getting the answer that you want.

    Just what part of this did you want?
    "formatted_address" : "New York, NY, USA",
    "geometry" : {
    "bounds" : {
    "northeast" : {
    "lat" : 40.9175771,
    "lng" : -73.70027209999999
    },
    "southwest" : {
    "lat" : 40.4773991,
    "lng" : -74.25908989999999
    }
    },
    "location" : {
    "lat" : 40.7127837,
    "lng" : -74.0059413
    },
    "location_type" : "APPROXIMATE",
    "viewport" : {
    "northeast" : {
    "lat" : 40.9175771,
    "lng" : -73.70027209999999
    },
    "southwest" : {
    "lat" : 40.4773991,
    "lng" : -74.25908989999999

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Analyze Google API for latitude & longitude of adress - Regex.pattern issue

    This works for me.

    Please Login or Register  to view this content.
    Last edited by bakerman2; 10-05-2017 at 12:56 AM.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Analyze Google API for latitude & longitude of adress - Regex.pattern issue

    Lasse97,

    I would go like this.

    B2:
    =GetLatLng(A2,"lat")
    C2:
    =GetLatLng(A2,"lng")
    Please Login or Register  to view this content.

  7. #7
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Analyze Google API for latitude & longitude of adress - Regex.pattern issue

    @ jindon

    Typo.

    Please Login or Register  to view this content.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Analyze Google API for latitude & longitude of adress - Regex.pattern issue

    Lasse97

    I don't think you need an error trap.
    Try the attached.
    Please Login or Register  to view this content.
    Edit:
    Replaced xlListSeparator with xlDecimalSeparator
    Attached Files Attached Files
    Last edited by jindon; 10-05-2017 at 06:25 AM. Reason: Replaced xlListSeparator with xlDecimalSeparator

  9. #9
    Registered User
    Join Date
    10-03-2017
    Location
    Hamburg
    MS-Off Ver
    2007
    Posts
    3

    Re: Analyze Google API for latitude & longitude of adress - Regex.pattern issue

    Hi there,

    the solution of jindon works just perfectly!
    Many thanks to you all. Definitely have to check out these patterns and trap issues in more detail.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Analyze Google API for latitude & longitude of adress - Regex.pattern issue

    Lasse9,

    I've just found the symbolic figures.
    If you put any city in A2, it points to the City hall in most cases.
    And also fixed the error when exceeds the connection limit.

    Try
    Please Login or Register  to view this content.

+ 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. Replies: 0
    Last Post: 03-03-2014, 04:42 AM
  2. Broken Formula to Hyperlink longitude and latitude cells to google maps
    By Nola ADA in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2013, 09:48 AM
  3. Latitude & Longitude processing help please
    By Pilot_Greg in forum Excel General
    Replies: 11
    Last Post: 12-14-2009, 03:31 PM
  4. MGRS to Longitude/Latitude
    By ajocius in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2009, 11:26 AM
  5. Latitude/Longitude Porblem
    By edwardtong694 in forum Excel General
    Replies: 3
    Last Post: 05-20-2009, 12:23 PM
  6. [SOLVED] formula for longitude/latitude
    By Leo in forum Excel General
    Replies: 2
    Last Post: 06-03-2005, 02:05 PM
  7. [SOLVED] Help with Latitude and Longitude.
    By LadiFireBug in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-22-2005, 05:09 AM

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