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



    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?

  • 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


    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



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

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



    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


  • 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.