Scrape GPS Coordinates in to Excel?

Google Maps API is your friend: http://code.google.com/apis/maps/

Thanks @gt4329b. I’ll check it out, but there might be more of a learning curve on my part than I want for this type of solution.

What all is involved? Java Script, HTML, etc?

try looking for a RESTful interface. geocoder.us has one. Basically you tell excel to get data from somewhere like:

“http://username:password@geocoder.us/member/service/csv/geocode?address=1600+Pennsylvania+Ave,+Washington+DC”

which returns this:

“38.898748,-77.037684,1600 Pennsylvania Ave NW,Washington,DC,20502”

So now the first two values of that CSV list are your lat and long (or long and lat, I’m not sure on the order offhand).

I would definitely do it in javascript, because that would be easiest for me, but I’m fairly certain that excel can get a url and have the data be what’s returned. From there, you just need to separate the CSV.

BTW: more info here and search for “REST-CSV”

Ok! I seemed to get it to work using @reactor5‘s advice and this article

If anyone is trying to do something similar I’ve included my code below (Excel VBA). Basically I have a worksheet with “Address, City, State, Zip, Latitude, Longitude” in cells A1 to F1 and Below “Address, City, State, Zip” is where the user would type in the address. The Latitude and Longitude are then put in the cells below their respective headings. I probably need to add some error handling if the address doesn’t come up right on the website, but this does the trick for a good address.

The Macro Looks Like this:

Sub GetLatLong()
’
’ GetLatLong
’
Dim iAddr As String
Dim iCity As String
Dim iST As String
Dim iZip As String
Dim WebAddr As String
Dim WebRslt As String
Dim Pos1 As Long
Dim Pos2 As Long
’
iAddr = WorksheetFunction.Substitute(Trim(Range(“A2”)), ” ”, ”+”)
iCity = WorksheetFunction.Substitute(Trim(Range(“B2”)), ” ”, ”+”)
iST = Trim(Range(“C2”))
iZip = Trim(Range(“D2”))
WebAddr = “http://rpc.geocoder.us/service/csv?address=” & iAddr & ”,” & iCity & ”+” & iST & ”+” & iZip

With ActiveSheet.QueryTables.Add(Connection:=“URL;” & WebAddr, Destination:=Range(”$H$1”))
.Name = “Link”
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

WebRslt = Range(“H1”)
Pos1 = WorksheetFunction.Find(”,”, WebRslt)
‘MsgBox Pos1
Pos2 = WorksheetFunction.Find(”,”, WebRslt, Pos1 + 1)
‘MsgBox Pos2

Range(“E2”) = Left(WebRslt, Pos1 – 1)
Range(“F2”) = Mid(WebRslt, Pos1 + 1, Pos2 – Pos1 – 1)

Range(“H1”, “J1”).Columns.Delete
Range(“H1”).Select
Selection.QueryTable.Delete
Selection.ClearContents
End Sub

What you want sounds like a simple web app. Why do you need to involve Excel?

Mostly because I’m not that familiar with creating web apps, and this small of a solution isn’t worth learning how to create a web app at the time. I know excel pretty well, and it can do what I need it to…so why not? :)

fair enough

I’ve developed an addin for Excel which does forward/reverse geocoding and GCD with GoogleMaps. Freeware: http://www.calvert.ch/geodesix/