excel web query

Crazyivan 1979 · 31-03-2016 11:16AM #1

not sure if web query is even the right way to go about this but here it goes.

I have 2 columns in excel for GPS coordinates; 1 giving lat, the other giving long.

There is a website that can can convert decimal degrees to irish grid coordinates with 2 search boxes (long & lat) and a result box (Irish grid)

What i want is to set something up in the spreadsheet that will input the lat and the lat into the 2 search boxes and give me the resulting irish grid in another cell.

I have searched web queries and other ways, but nothing seems to fit right. I have used simple macros in excel. But am new at it.

Can anyone help?

Buford T Justice · 31-03-2016 05:28PM

Are you looking to do the conversions yourself or are you looking to get a wep app to do it for you via an api?

pillphil · 31-03-2016 06:04PM

http://ww2.scenic-tours.co.uk/serve.php?t=WoNlbJvoVlhuJL5405objaa8jVO8atNuwZV

Which irish grid exactly are you looking for?

Crazyivan 1979 · 31-03-2016 09:02PM

i do alot of hiking. What im trying to do is make a quicker way of producing route cards for myself.

I save a KML file from google earth within a series of placemarks on a route, make it an XML file and open it on another tab on my route card template.

The coordinates on the kml/ xml file are in decimal degrees but id prefer them in the irish grid system (e.g. T 079 939). Doubt there is a way of using a formula in excel. So I was trying to use web queries but think i am out of my depth.

Id like my route card to automatically convert the coordinates that I dump into it.

pillphil · 01-04-2016 07:36AM

This almost works, I'm not sure how accurate it is. It isn't a ggreat way of doing it wither, it opens new browser window for each calculation. Maybe someone here can convert it to use arrays?

Function OpenWebPageAndConvert(Decimal_Degrees As String) As String
Dim Lat As String
Dim Lon As String
Dim coord As Variant

coord = Split(Decimal_Degrees, ",")

Lat = Trim(coord(0))
Lon = Trim(coord(1))

Dim Inx As Long

Dim ObjCollection As Object
Dim i As Long
Dim objElement As Object
Dim doc As Object
Dim form As Object

Dim ie As Object
Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")
ie.Navigate "http://www.fieldenmaps.info/cconv/cconv_ie.html"
ie.Visible = True
While ie.Busy
DoEvents
Wend

Application.Wait Now + TimeValue("00:00:01")

ie.Document.getElementById("txtiGPSLat").DefaultValue = Lat
ie.Document.getElementById("txtiGPSLon").DefaultValue = Lon
ie.Document.getElementById("txtiGPSLat").Value = Lat
ie.Document.getElementById("txtiGPSLon").Value = Lon
Dim result As String

Application.Wait Now + TimeValue("00:00:01")

Dim CurrentWindow As HTMLWindowProxy: Set CurrentWindow = ie.Document.parentWindow
Call CurrentWindow.execScript("cmdiGPSGeoD()")

result = ie.Document.getElementById("txtiNGSq").Value + " " + ie.Document.getElementById("txtiNGSqE").Value + " " + ie.Document.getElementById("txtiNGSqN").Value
'result = "4.2"

OpenWebPageAndConvert = result

'ie.Quit

End Function

You can see how to call it here:

The lat long string has to be separated by a comma.

I just did this for fun, there's probably an easier way to do it

Crazyivan 1979 · 01-04-2016 07:47AM

Cheers Phil, will give it a try.

pillphil · 03-04-2016 12:28AM

Did it work for you? I didn't have time to test it much.
The first "Application.Wait Now + TimeValue("00:00:01")" line may need to be changed if the page is slow loading.

excel web query

Comments