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