Obtain current geolocation data using VBA

isladogs · May 6, 2018

This is another of my 'unusual' questions.
Can current location data (latitude/longitude) be extracted using VBA?
I feel I should already know how to do this ... but its eluding me :banghead:

Background:
A recent thread by Dick7Access was asking how to get exact location using GPS for an unusual reason
See https://www.access-programmers.co.uk/forums/showthread.php?t=299459
The suggestion was to use one of the many phone apps e.g. LatLong (for iPhone)

This got me thinking about ways of pulling the geolocation data that many Windows 8 & 10 devices can obtain (providing users give permission)
Both my laptop & Windows tablet can obtain this info accurately
My desktop just gets the approximate location specified by my IP address (approx 100 miles from me) though I can specify the actual location manually

This info (accurate or otherwise) can be displayed using various free apps from the Microsoft Store such as Geolocator & SensorList:

What I want to do is use VBA to extract this geolocation data in Access rather than type it in manually
The geolocation is then used to get the nearest postcodes

However I'm getting nowhere with doing this using code
My only ideas so far are to try & adapt GetElementByID HTML code - see https://www.w3schools.com/html/html5_geolocation.asp

Code:

var x = document.getElementById("demo");
function getLocation() {
    if (navigator.geolocation) {
        navigator.geolocation.getCurrentPosition(showPosition);
    } else {
        x.innerHTML = "Geolocation is not supported by this browser.";
    }
}
function showPosition(position) {
    x.innerHTML = "Latitude: " + position.coords.latitude + 
    "<br>Longitude: " + position.coords.longitude; 
}

or adapt Windows UWP code - see https://docs.microsoft.com/en-us/uwp/api/Windows.Devices.Geolocation

Can anyone help me get this info using VBA?

NauticalGent · May 6, 2018

Hey there Col,

The Total Source book I purchased from FMS does have a few modules regarding this idea which I would be more than happy to share with you, but I am Stateside at the moment and won’t be back until the 21st.

You’ll probably have this figured out by then but I will check in on you then...

isladogs · May 6, 2018

Thanks NG - enjoy your time back in the U.S. - holiday or work?

NauticalGent · May 6, 2018

Holiday! My Pebbles(Kayla, my daughter) got married yesterday. Not too fond of her husband yet but that is to be expected.

House was full of 20-something’s with their rug-rats and the chaos was unbearable. I would have retreated to the back porch for a scotch and stogie, but that kind of activity at my in-laws is forbidden.

When did I become a grouchy old man?!?

isladogs · May 6, 2018

Hi
I know exactly how you feel about daughters and partner choices!

Just had a quick look at the FMS Total Source Book synopsis on the FMS website. Lots of geospatial items listed but most of these seem to be for items I already use in various databases e.g. postcode from address using Google maps. Will have a more thorough look later today

What I'm trying to do is use code to extract the geolocation data as measured by the Windows computer / tablet itself and use that in Access.

One possible work-round may be to take a picture with the computer's camera and extract the GPS location data from that into Access. It should work but it seems a very convoluted approach to getting the info

There is a Windows Geolocation Service VBA library but I get an error when I try to load it in the VBE. Will research further
What I really need is the name of an API I can call upon for the task

The_Doc_Man · May 6, 2018

Ridders, here's a thought for you. Somehow, Google Maps can determine your location if you have Windows Geosense enabled. That means that the web-page code "knows" what interface to use. If worst comes to worse, open up Google Maps and switch to look at the underlying HTML or Java whatever it is that they use. You might see the name of the call entry point and get a hint of how to use that code.

Also, maybe you might follow this link:

https://stackoverflow.com/questions/24114415/any-api-to-read-geo-location-data-from-windows-7

Here is an article about web pages that use the service, by someone who called in to MSDN about it.

https://social.msdn.microsoft.com/F...orking?forum=windowssensorandlocationplatform

isladogs · May 6, 2018

The_Doc_Man said:
Ridders, here's a thought for you. Somehow, Google Maps can determine your location if you have Windows Geosense enabled. That means that the web-page code "knows" what interface to use. If worst comes to worse, open up Google Maps and switch to look at the underlying HTML or Java whatever it is that they use. You might see the name of the call entry point and get a hint of how to use that code.

Also, maybe you might follow this link:

https://stackoverflow.com/questions/24114415/any-api-to-read-geo-location-data-from-windows-7

Here is an article about web pages that use the service, by someone who called in to MSDN about it.

https://social.msdn.microsoft.com/F...orking?forum=windowssensorandlocationplatform

Hi Doc
Thanks for your thoughts
I already know how to get lat/long data using the Google Geocoding API for a specified address. In fact I'm already using that.

What I'm trying to do now is get that data for the current location but without needing to specify the address.
I've got 3 possible ways forward:

1. Using the same Google Geocoding API
I've found some code at UA that supposedly can do this but so far I can't get it to work.
The link is: http://www.utteraccess.com/forum/index.php?showtopic=2047473
The link includes 2 attachments but as I'm no longer a UA member (since they booted me out), I can't download them

I'd already seen the SO link you quoted. Unfortunately the link that contains no longer works
The second link was new to me but I didn't find anything useful

2. Taking a photo with the PC camera (or webcam) then extracting the GPS data saved as part of the photos extended properties. That should work as I can see the GPS properties in Explorer but when I download the file's extended properties, the GPS values are not included

EDIT: Oct 2019 - I've now solved that issue - see this example database: https://www.access-programmers.co.uk/forums/showthread.php?t=307479

3. Using the data obtained from the Windows geolocation sensor.
This link from post #1 gives some info https://docs.microsoft.com/en-us/uwp/api/Windows.Devices.Geolocation.
This would be my favoured approach but no idea as yet how to use it.

MrHans · May 7, 2018

I'm very interested to see if you find a solution for this, could be usefull for everybody.

Attached are the files from UA, hope it helps! View attachment geolocal.zip View attachment geolocal2.zip

isladogs · May 8, 2018

Thanks very much MrHans.
The 2 files just turned out to be html files to produce a map & give required permission for get location data. However that got me started along with a modified version of the code from UA

Anyway, I've got it working! Hooray!

See attached for an example database together with a video showing this in use.
Its not yet perfect. At the moment you have to give permission every time you click 'Get Current Location'. I will look for a way of making the setting 'stick'
I may also spend more time trying to solve the other approaches later.

However, a slightly different version will be included in the next version of my UK Postal Address Finder database to locate the postcode for the current location (or any other geolocation). That info is then used to find nearby places within a specified radius e.g. restaurants within 500 metres

The example database was created in Access 2010 32-bit and Windows 10.
I would be very grateful for feedback on this, particularly from anyone using Windows 7 or 8 or other versions of Access.

NOTE:
1. If the IE window shows an Allow Blocked Content button, you may need to adjust your IE security settings
2. The database includes an ActiveX slider control - this will not work if you have disabled ActiveX completely in Access settings

Once I get feedback from other forum members, I will post it to the example databases area together with an explanation of how the code works

IMPORTANT NOTE:
The main Google map with a centre marker requires a Google Maps API key. Please obtain your own free API key from https://developers.google.com/maps/documentation/maps-static/get-api-key and use this in place of the existing key in tblProgramSettings (record with ID = 7)

The supplied API key will stop working on (or before) 11 June 2018 when Google will require users to enable billing and associate it with all Google Maps Platform projects. However there is a fairly generous monthly free allowance & small/medium users of Google Map APIs should still be able to download various maps without incurring charges

UPDATE
I've just added the geolocal.html file to the GetLocation.zip.
See post #3 for further comment

NauticalGent · May 8, 2018

‘Purt near impressive Col...

isladogs · May 8, 2018

UPDATE:
I've just replaced the GetLocation.zip file in post 1.
It now includes the geolocal.html file which is also required for this to work
Please save that to the same folder as the ACCDB file

isladogs · May 8, 2018

FURTHER UPDATE:
Perhaps no longer SOLVED & I'm somewhat confused.

The example database works perfectly for me if both ACCDB and HTML files are in the folder where I created it: C:\Programs\MendipDataSystems\UKPAF.

If I move both to another folder, the html window first shows an Allow Blocked Content message.
Click that and the Allow Once button appears but even after I click that as well, the remaining code fails with an automation error

After clicking that, the form map appears but is for somewhere in the USA and the form shows my deliberately incorrect Nz 'alternative' values of Lat=100, Long =180

However, the code does work if I move the ACCDB file but leave the HTML file in the original folder.
You just need to change the HTML file path in the function below (its in the form module):

Code:

Private Function GrabGeoCoordinates()

    ...code here
   
    With ie
        .visible = True 'False
    '    .Navigate2 CurrentProject.Path & "\geolocal.html" 'disable this & add next line
      [COLOR="Red"] .Navigate2 "C:\Programs\MendipDataSystems\UKPAF\geolocal.html"[/COLOR]
 
... rest of code

So there seems to be something hard coded in the HTML file or the associated files it creates.

I've viewed the HTML file in various apps including Dreamweaver, Notepad++ in browsers but can't see any reason for this.

I assume the HTML file gets 'detached' from one or more of the files in the geolocal_files folder that should be created when the file is run. However its been a long time since I did anything major with HTML files and I'm unclear what the issue may be.

I've tried starting again with the HTML file in geolocal2.zip from UA (see post #8) but doing that didn't help

However, I then downloaded my own files from the earlier post onto a Windows tablet and ran the database from the default downloads folder.
It worked perfectly and without creating the separate geolocal_files folder.
So it may well work for others after all.
As previously requested...feedback would be much appreciated.

Attached are the HTML file (again) together with the contents of the geolocal_files folder.
Any ideas for explaining this welcomed. Perhaps I've just messed up my IE settings

Gasman · May 8, 2018

Colin,
You asked me to try out your Geolocal DB.
I got my lat and long from Google maps and entered them into the form, then pressed Search. It came up with my location after I noticed that I had missed the hyphen off the long. Before noticing that, it was giving me some location in Holland.

Is that enough, or do you want me to try something else as well.?

Access 2007 and Win10 Pro V1709

HTH

isladogs · May 8, 2018

Gasman said:
Colin,
You asked me to try out your Geolocal DB.
I got my lat and long from Google maps and entered them into the form, then pressed Search. It came up with my location after I noticed that I had missed the hyphen off the long. Before noticing that, it was giving me some location in Holland.

Is that enough, or do you want me to try something else as well.?

Access 2007 and Win10 Pro V1709

HTH

Hi
Thanks
I think you mean you forgot the minus sign in the longitude.
I know it works if you enter the lat and long values though good to know A2007 played nicely.
What I want people to test is whether clicking the Get Current Location button correctly populated that data automatically. Have a look at the video in the other zip file to see what I mean

Gasman · May 8, 2018

Colin,
Just watched your video.
If I select Get Current Location, I get asked the confirmation message, but get the attached picture.
Trying again I noticed that it started IE minimised to the taskbar. Opening that gave me the html file and after allowing blocked content etc I finally got the IE pic, but noting was showing in the DB?
Also got the error message (GEO error) when I came back to the DB after leaving IE and the Google map.
Just got another error (error2) after coming back to DB and it has put 100 and 180 in the lat and long controls.

Chrome is my main browser. If I zoom right in on the IE browser that is my location.

Edit.
Just gone back into the extracted folder and there is a new file called Map.bmp, however opening it with Irfanview, it complained it had the incorrect extension.? This appears to be because the first few characters in the file show PNG?

Beetle · May 8, 2018

@Colin

I tested using Windows 7 and A2010. It works and accurately gets my current location (in Denver, CO, USA). I also tested moving the accdb and the html file to a different folder from where I originally saved it and it still works. I am not experiencing the same error you are when changing the folder location.

Edit: I'm using IE 11, may or may not be relevant.

isladogs · May 8, 2018

Thanks to you both for your help

@Beetle
That's excellent news. Thanks. Good to see it works in Win7 as well

Although I have Win10, it also comes with IE 11 and many VBA functions that pull data from websites have to use that rather than Edge or Chrome etc

@Gasman
As you say, IE opens in the taskbar, you need to open it and click the Allow Once button within about 3 seconds. If you can't see the button, open the file direct in IE & increase the window height.
Once you click the button, the IE window closes after a couple of seconds, the Access form should be populated & the Google static map centred on your location should appear (like in the video).
The Lat/Long 'values' of 100/180 are dummy values to help manage any issues if the process fails & yes I know there is no such thing as Lat = 100 (that's the point)

Unfortunately this method currently requires the use of IE no matter which browser is your default

As for your error 438 can you please break & tell me which line triggers it.
It may be another A2007 issue but check the VBA references as I made it in A2010.
Also please try modifying the error handler in GrabCoordinates adding the part in RED & see what happens

Code:

Err_Handler:
    If Err = 13 [COLOR="Red"]Or Err = 438[/COLOR] Then Resume Next

Finally, the map.bmp file is created to hold the Google static map image. If you type in different coords & click Search it will automatically update for the new location
Variations on the GetMapImage code are used throughout my UK Postal Address Finder app. It used to create a PNG file then I started getting odd error messages so I changed the extension but possibly not the code. I'll try swopping it back to PNG

Gasman · May 9, 2018

Good morning Colin,
Just downloaded it in work (Access 2007 & Win10 Home ) and just tried the Get Location
For some reason it cannot find my location. I got two errors, pics attached.

I will try your mods at home tonight.

Also I left IE open when I tried a second time, but the DB just starts another instance of IE.? Would it be possible to use an instance that already exists?, or are you concerned that you would overwrite a current users webpage.? If that is the case would it be possible to start in a new Tab.?

FWIW no missing references here in work.

isladogs · May 9, 2018

Gasman said:
Good morning Colin,
Just downloaded it in work (Access 2007 & Win10 Home ) and just tried the Get Location
For some reason it cannot find my location. I got two errors, pics attached.

I will try your mods at home tonight.

Also I left IE open when I tried a second time, but the DB just starts another instance of IE.? Would it be possible to use an instance that already exists?, or are you concerned that you would overwrite a current users webpage.? If that is the case would it be possible to start in a new Tab.?

FWIW no missing references here in work.

@Gasman
If you go back to post 12, I mentioned the automation error I got in some circumstances.

Once I get feedback from a number of people, I will decide what, if any, changes to make.
As you say, at the moment it opens a new instance of IE each time.
Overwriting isn't really a concern

From previous experience, some things fail in A2007 without necessarily triggering errors

Some things you could check:
a) are you using IE11?
b) is Location services switched on in Windows settings - it needs to be ...though you still have to give permission each time
c) try putting the app & HTML file in this folder C:\Programs\MendipDataSystems\UKPAF
d) try opening the HTML file direct in your default browser.
Click F12 to open Developer mode.
Change something minor in the code & save it.
Change it back & save it again
Close & try running from the app again

Gasman · May 9, 2018

Colin,
My location services was switched off. Switching it back on, it finds a location, however a good few miles from where I am working.
Still get the automation error.

FWIW I am currently at 51.6487294,-3.9959057, IE11 thinks I am at 51.6567,-3.9404

a) are you using IE11? Yes
b) is Location services switched on in Windows settings - it needs to be ...though you still have to give permission each time Now it is
c) try putting the app & HTML file in this folder C:\Programs\MendipDataSystems\UKPAF Have done so
d) try opening the HTML file direct in your default browser.
Click F12 to open Developer mode.
Change something minor in the code & save it.
Change it back & save it again
Close & try running from the app again Have done so.

Obtain current geolocation data using VBA

MVP / VIP

Attachments

Ignore List Poster Boy

MVP / VIP

Ignore List Poster Boy

MVP / VIP

Immoderate Moderator

MVP / VIP

Registered User

MVP / VIP

Attachments

Ignore List Poster Boy

MVP / VIP

MVP / VIP

Attachments

Enthusiastic Amateur

MVP / VIP

Enthusiastic Amateur

Attachments

Duly Registered Boozer

MVP / VIP

Enthusiastic Amateur

Attachments

MVP / VIP

Enthusiastic Amateur

Similar threads

Users who are viewing this thread