Looking everywhere for VBA to do reverse geocoding, not just for address

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Quote

The part I understood is you attached the file with corrections. I download the file you posted here, run the macro and it didnt populate.

Am I missing something?

So no fields (columns C to O) from the three set of coordinates populated? If not I can't explain it I'm afraid

Perhaps if other readers of this thread could download the latest workbook and see if it works for them it would be appreciated.

Robert

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

robert, I guess it might be the issue of regional settings. Pass the long and lat as a string by replacing the Application.International(xlDecimalSeparator) with . (dot)

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Hi Krishnakumar,

Thanks for the advice. I have changed the coordinate variables to string on the attached workbook but as I haven't used the Application.International function before I didn't know how to incorporate it as you've said. Hopefully just changing the latitude and longitude variable types will do the job.

I have attached the workbook again with the revised macro which is working for me and at least one other super moderator here at Ozgrid so if if still doesn't work for angomera it must be something minor like Krishnakumar has said. As such, angomera if no data is still returned, click anywhere into the ReverseGeocodingLB macro and step through the code (by pressing F8 on each line) to try and see what's happening - particularly around what latitude and longitude values are being passed to the XMLerverReadLB macro. Check also that once the strURL variable has populated (once you've stepped passed it) you can manually put its string value into your web browser and it returns a XML file in the format you showed us earlier.

Regards,

Robert

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

[vb]Sub ReverseGeocodingLB()

Dim lngLastRow As Long
Dim lngMyRow As Long

Dim strLati As String
Dim strLong As String
Dim strDecSep As String

strDecSep = Application.International(xlDecimalSeparator)

On Error Resume Next
lngLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If lngLastRow = 0 Then
MsgBox "There is no data on the tab to work with!!", vbExclamation
Exit Sub
End If
On Error GoTo 0

Application.ScreenUpdating = False

For lngMyRow = 2 To lngLastRow

strLati = Range("A" & lngMyRow).Value2
strLong = Range("B" & lngMyRow).Value2

If Not strDecSep = "." Then
strLati = Replace(strLati, strDecSep, ".")
strLong = Replace(strLong, strDecSep, ".")
End If

Call XMLerverReadLB(strLati, strLong, lngMyRow)

Next lngMyRow

Application.ScreenUpdating = True

MsgBox "Address components for the latitutde and longitude coordinates in columns A and B have now been returned.", vbInformation

End Sub[/vb]

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Hi Krishnakumar,

Probably just me but I can't make the If statement execute regardless of what I put in columns A or B :confused

I haven't heard from angomera so here's hoping we've sorted it!!

Regards,

Robert

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Quote from Trebor76;785156

Hi Krishnakumar,

Probably just me but I can't make the If statement execute regardless of what I put in columns A or B :confused

I haven't heard from angomera so here's hoping we've sorted it!!

Regards,

Robert

Display More

You just need to change your regional settings to Spanish or any other country where the decimal separator is not dot(.). Hope this make sense

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

Quote

Trebor76 the solution doesn't work for me. Can you be more precise? Can you send me a new file I can download?

The latitude and longitude coordinates for the Statue of Liberty are 40.71277200 and -74.00605800 respectively. This is exactly how they need to look (formatted) when they are passed to the XMLerverReadLB macro for it to work.

Spanish regional settings would return 40,71277200 and -74,00605800 (note the comma as opposed to a dot). You did not notice this???

I have incorporated Krishnakumar's nifty patch in the attached workbook.

Robert

Quote from Trebor76

Re: Looking everywhere for VBA to do reverse geocoding, not just for address

The latitude and longitude coordinates for the Statue of Liberty are 40.71277200 and -74.00605800 respectively. This is exactly how they need to look (formatted) when they are passed to the XMLerverReadLB macro for it to work.

Spanish regional settings would return 40,71277200 and -74,00605800 (note the comma as opposed to a dot). You did not notice this???

I have incorporated Krishnakumar's nifty patch in the attached workbook.

Robert

Display More

Hi Trevor,
I was looking for the exact same question and download your file to test, but upon runnin the reversegeo code vba module, it took forever and crashed
Am I missing something? I havent changed anything in the workbook you [provided

luuminhvuong93 welcome to the board,

Please review the rules you agreed to when you joined this site.
You posted a question in a thread started by someone else, this is referred to as thread hijacking.
I have closed the thread, feel free to start your own thread(its' free) and link to this thread if you so desire.