Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

[ExcelVBa] Vie tramite coordinate

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

[ExcelVBa] Vie tramite coordinate

Postdi saxas » 21/10/16 00:32

Salve a tutti,
ho fatto varie ricerche per trovare un metodo per recuperare tramite coordinate la via di un dato comune cui fa riferimento,
ed ho trovato il seguente codice
Codice: Seleziona tutto
Sub indirizzi_excel()

   
Dim FileXml, indirizzo, localita, CAP, URL As String
Dim c As Integer
Dim z As Long
Dim GeoDati As Collection
   

Application.ScreenUpdating = False
   
   
FileXml = "IMPORTAZIONE XML.xls"

FileXml = ActiveWorkbook.Name
ActiveWorkbook.Sheets("INDIRIZZI").Activate
Range("D2").Select ' SI POSIZIONA SULLA PRIMA VIA
Selection.End(xlDown).Select
z = ActiveCell.Row ' prende il numero dell'ultima riga
If z = 65536 Then z = 2
Range("D2").Select ' SI POSIZIONA SULLA PRIMA VIA


For c = 2 To z Step 1
 If ActiveCell.Value <> "" Then indirizzo = "address=" & ActiveCell.Value Else indirizzo = ""
 If ActiveCell.Offset(0, -1).Value <> "" Then localita = "|locality:" & ActiveCell.Offset(0, -1).Value Else localita = ""
 If ActiveCell.Offset(0, -2).Value <> "" Then CAP = "|postal_code:" & ActiveCell.Offset(0, -2).Value Else CAP = ""
 
'Geocoding in formato XML di Google API V3
URL = "http://maps.googleapis.com/maps/api/geocode/xml?" & indirizzo & "&sensor=false&language=it&components=Country:it" & localita & CAP

ActiveWorkbook.XmlMaps(1).Import URL:=URL
   
    Sheets("IMPORTAZIONE XML").Select
    Range("A2:C2").Select
    Selection.Copy
    Sheets("INDIRIZZI").Select
    ActiveCell.Offset(0, 1).Select
   
   
   
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   
    Application.StatusBar = "Progress... " & Int(c / z * 100) & "%"


ActiveCell.Offset(1, -1).Range("A1").Select


Next c

   
Workbooks(FileXml).Activate
ActiveWorkbook.Save
' ActiveWorkbook.SaveAs Filename:= _
'        "C:\Documents and Settings\renzgi\Desktop\indirizzi csv.csv", FileFormat:= _
'        xlCSV, CreateBackup:=False
   Application.StatusBar = ""

Application.ScreenUpdating = True


End Sub

Per quello che ho potuto capire tramite il CAP la via il numero civico ed il comune, si riesce ad individuare la coordinata corrispondente, tramite i comandi
Codice: Seleziona tutto
'Geocoding in formato XML di Google API V3
URL = "http://maps.googleapis.com/maps/api/geocode/xml?" & indirizzo & "&sensor=false&language=it&components=Country:it" & localita & CAP

ActiveWorkbook.XmlMaps(1).Import URL:=URL


Vorrei sapere se è possibile, modificando il comando in questione, recuperare la via corrispondente ed il comune tramite le coordinate
allego link da cui prelevare il file originale,
https://www.dropbox.com/s/srnft4xm031rtmn/Geocoding.xlsm?dl=0
Win10 - Office 2016 Ita
saxas
Utente Junior
 
Post: 55
Iscritto il: 14/11/13 16:58

Sponsor
 

Re: [ExcelVBa] Vie tramite coordinate

Postdi Anthony47 » 21/10/16 00:50

Per questo avevo messo a punto questa function:
Codice: Seleziona tutto
Function myAddress(ByVal myLat As Double, myLong As Double) As Variant
'byAnthony47
'Vedi anche https://developers.google.com/maps/documentation/geocoding/?csw=1#ReverseGeocoding
'
    Dim Request         As New XMLHTTP30
    Dim Results         As New DOMDocument30
    Dim StatusNode      As IXMLDOMNode
    Dim LatitudeNode    As IXMLDOMNode
    Dim LongitudeNode   As IXMLDOMNode

    'Checking if the latitude value is within the acceptable limits [-90, 90].
    If Latitude < -90 Or Latitude > 90 Then
        myAddress = "Errata latitudine (+90/-90) "
        Exit Function
    End If
   
    'Checking if the longitude value is within the acceptable limits [-180, 180].
    If Longitude < -180 Or Longitude > 180 Then
        myAddress = "Errata longitudine (+180/-180)"
        Exit Function
    End If
aaaa = "http://maps.google.com/maps/api/geocode/xml?latlng=" & Replace(myLat, ",", ".") & "," & Replace(myLong, ",", ".") & "&sensor=false"

Request.Open "GET", aaaa
    'Send the request to the Google server.
    Request.send
   
    'Read the results from the request.
    myStTiM = Timer
    Do          'wait myStab
        DoEvents
        If Timer > myStTiM + 0.5 Or Timer < myStTiM Then Exit Do
    Loop
    Results.LoadXML Request.responseText
Set StatusNode = Results.SelectSingleNode("//status")
If StatusNode.Text <> "OK" Then
    myAddress = "Status = " & StatusNode.Text
    Exit Function
End If
'Debug.Print StatusNode.Text
Set StatusNode = Results.SelectSingleNode("//result/formatted_address")
'Debug.Print StatusNode.Text
myAddress = StatusNode.Text
End Function

Poi si richiama con una formula del tipo
Codice: Seleziona tutto
=myAddress(Latitud;Longitud)

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 17646
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [ExcelVBa] Vie tramite coordinate

Postdi saxas » 21/10/16 09:23

Grazie Anthony47
ma mi da errore in
Codice: Seleziona tutto
=myAddress(Latitud;Longitud)

in pratica rimane evidenziato rosso e mi dice errore di sintassi
Win10 - Office 2016 Ita
saxas
Utente Junior
 
Post: 55
Iscritto il: 14/11/13 16:58

Re: [ExcelVBa] Vie tramite coordinate

Postdi Anthony47 » 21/10/16 19:02

Quella che citi e' una formula da mettere su un foglio di lavoro...
All'interno del vba userai qualcosa del tipo
Codice: Seleziona tutto
myVariabile = myAddress(Latid, Longit)

Latid e Longit devono essere espressi in gradi decimali (es 45,5 e non in gradi.primi.secondi)

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 17646
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [ExcelVBa] Vie tramite coordinate

Postdi saxas » 21/10/16 21:04

Si avevo provato anche nel modo che hai menzionato, ma mi da "errore di compilazione Tipo definito dall'utente non definito"
e viene evidenziata la prima riga "Dim Request As New XMLHTTP30".
Il tipo di coordinate che utilizzo sono come i seguenti 43.667278, 11.578791
Win10 - Office 2016 Ita
saxas
Utente Junior
 
Post: 55
Iscritto il: 14/11/13 16:58

Re: [ExcelVBa] Vie tramite coordinate

Postdi Anthony47 » 22/10/16 01:57

Eh eh, perche' ho dimenticato di dirti che devi inserire nel vba un riferimento alla libreria "Microsoft XML": Menu /Strumenti /Riferimenti; scorri l'elenco, trova e spunta quello richiesto, Ok

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 17646
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [ExcelVBa] Vie tramite coordinate

Postdi saxas » 22/10/16 06:56

Grazi mille per l'aiuto e le spiegazioni,
ci siamo quasi,
Latid e Longit devono essere espressi in gradi decimali (es 45,5 e non in gradi.primi.secondi)


avendo io coordinate prese da GoogleMaps o cmq le coordinate che mi forniscono sono nel formato
Il tipo di coordinate che utilizzo sono come i seguenti 43.667278, 11.578791


mi da un "errore" in quanto
Codice: Seleziona tutto
    'Checking if the latitude value is within the acceptable limits [-90, 90].
    If Latitude < -90 Or Latitude > 90 Then
        myAddress = "Errata latitudine (+90/-90) "
        Exit Function
    End If
   
    'Checking if the longitude value is within the acceptable limits [-180, 180].
    If Longitude < -180 Or Longitude > 180 Then
        myAddress = "Errata longitudine (+180/-180)"
        Exit Function
    End If


come posso convertire le coordinate che ho con le coordinate richieste? esiste una formula?
Win10 - Office 2016 Ita
saxas
Utente Junior
 
Post: 55
Iscritto il: 14/11/13 16:58

Re: [ExcelVBa] Vie tramite coordinate

Postdi Anthony47 » 22/10/16 20:09

Non vedo come il codice che hai evidenziato conflittui con i tuoi dati.
OVVIAMENTE devi rispettare le convenzioni di Excel e le tue impostazioni internazionali.
Cosi' 43.667278 sara' considerato da Excel un Numero se le tue impostazioni prevedono il "punto" come separatore decimale, ma sara' considerata una stringa se il separatore che hai impostato e' la "virgola".
Discorso analogo va fatto per il vba, ma devi ricordare che il separatore da usare all'interno dei vba e' sempre il "punto".

Se dopo questi chiarimenti ancora non ne vieni fuori devi pero' dire come hai provato a utilizzare la funzione, seno' perdo tempo a fare ipotesi 1 e ipotesi 2, variante a e variante b.

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 17646
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [ExcelVBa] Vie tramite coordinate

Postdi saxas » 23/10/16 08:56

Scusami Anthony47, e grazie per la tua disponibilità, ma non ne sto venendo a capo,
in pratica se sostituisco il "." con la "," manualmente tutto funziona, se invece utilizzo il comando "replace", o peggio ancora ancora con una macro registrata sostituisco il "." con la "," non funziona più nulla,
inoltre si tratta di migliaia e migliaia di coordinate, ed utilizzando la formula da mettere all'interno del foglio di lavoro
Codice: Seleziona tutto
myVariabile = myAddress(Latid, Longit)

Si blocca il pc

https://www.dropbox.com/s/qojr9j7wkmf7ige/PP.xlsm?dl=0
ti allego il file con una ventina di coordinate
Grazie
Win10 - Office 2016 Ita
saxas
Utente Junior
 
Post: 55
Iscritto il: 14/11/13 16:58

Re: [ExcelVBa] Vie tramite coordinate

Postdi patel » 23/10/16 09:29

perché fare la sostituzione 2 volte ? già la funzione di Anthony converte la virgola in punto quindi è inutile fare l'opposto, basta modificare la funzione in questo modo lasciando intatte le tue colonne di lat e long
Codice: Seleziona tutto
Function via(ByVal myLat As String, myLong As String) As Variant
    Dim Request         As New XMLHTTP30
    Dim Results         As New DOMDocument30
    Dim StatusNode      As IXMLDOMNode
    Dim LatitudeNode    As IXMLDOMNode
    Dim LongitudeNode   As IXMLDOMNode
   aaaa = "http://maps.google.com/maps/api/geocode/xml?latlng=" & Replace(myLat, ",", ".") & "," & Replace(myLong, ",", ".") & "&sensor=false"

Request.Open "GET", aaaa
    Request.send
    myStTiM = Timer
    Do          'wait myStab
        DoEvents
        If Timer > myStTiM + 0.5 Or Timer < myStTiM Then Exit Do
    Loop
    Results.LoadXML Request.responseText
    Set StatusNode = Results.SelectSingleNode("//status")
    If StatusNode.Text <> "OK" Then
       via = "Status = " & StatusNode.Text
       Exit Function
    End If
   Set StatusNode = Results.SelectSingleNode("//result/formatted_address")
   via = StatusNode.Text
End Function
patel
Utente Senior
 
Post: 309
Iscritto il: 24/04/12 16:03

Re: [ExcelVBa] Vie tramite coordinate

Postdi saxas » 23/10/16 11:15

Ho risolto tutto, in pratica impostavo le variabili Latitude e Longitude come Double, cambiandola a String funziona tutto....
grazie mille ad entrambi
Win10 - Office 2016 Ita
saxas
Utente Junior
 
Post: 55
Iscritto il: 14/11/13 16:58

Re: [ExcelVBa] Vie tramite coordinate

Postdi saxas » 23/10/16 14:28

Credo ci sia un limite nelle richiesta adesso la macro non mi permette di procedere dando come risultato "Status = OVER_QUERY_LIMIT"
Win10 - Office 2016 Ita
saxas
Utente Junior
 
Post: 55
Iscritto il: 14/11/13 16:58

Re: [ExcelVBa] Vie tramite coordinate

Postdi Anthony47 » 24/10/16 01:05

Hai aggirato, ma credo sia meglio usare una function in grado di gestire sia il "punto" che la "virgola"; ho quindi modificato il codice come segue:
Codice: Seleziona tutto
Function myStrAddress(ByVal Latid As String, Longit As String) As Variant     'Parametri Stringa
'byAnthony47
'Vedi anche https://developers.google.com/maps/documentation/geocoding/?csw=1#ReverseGeocoding
'
    Dim Request         As New XMLHTTP30
    Dim Results         As New DOMDocument30
    Dim StatusNode      As IXMLDOMNode
    Dim LatitudeNode    As IXMLDOMNode
    Dim LongitudeNode   As IXMLDOMNode
Dim myLat As Double, myLong As Double, myReq As String

myLat = CDbl(Replace(Latid, ".", ","))
myLong = CDbl(Replace(Longit, ".", ","))

    'Checking if the latitude value is within the acceptable limits [-90, 90].
    If myLat < -90 Or myLat > 90 Then
        myStrAddress = "Errata latitudine (+90/-90) "
        Exit Function
    End If
   
    'Checking if the longitude value is within the acceptable limits [-180, 180].
    If myLong < -180 Or myLong > 180 Then
        myStrAddress = "Errata longitudine (+180/-180)"
        Exit Function
    End If
myReq = "http://maps.google.com/maps/api/geocode/xml?latlng=" & Replace(myLat, ",", ".") & "," & Replace(myLong, ",", ".") & "&sensor=false"

Request.Open "GET", myReq
    'Send the request to the Google server.
    Request.send
   
    'Read the results from the request.
    myStTiM = Timer
    Do          'wait myStab
        DoEvents
        If Timer > myStTiM + 0.5 Or Timer < myStTiM Then Exit Do
    Loop
    Results.LoadXML Request.responseText
Set StatusNode = Results.SelectSingleNode("//status")
If StatusNode.Text <> "OK" Then
    myStrAddress = "Status = " & StatusNode.Text
    Exit Function
End If
'Debug.Print StatusNode.Text
Set StatusNode = Results.SelectSingleNode("//result/formatted_address")
'Debug.Print StatusNode.Text
myStrAddress = StatusNode.Text
End Function

Ora i parametri Latitud e Longitud sono presi come stringa, che puo' usare sia il "punto" che la "virgola".
Ricorda che bisogna inserire nel vba un riferimento alla libreria "Microsoft XML": Menu /Strumenti /Riferimenti; scorri l'elenco, trova e spunta quello richiesto, Ok

La macro di CercaVia puo' quindi diventare:
Codice: Seleziona tutto
Dim Latitude As String           'Usa Public se serve
Dim Longitude As String          'Usa Public se serve

 
Sub CercaVia()
'
Sheets("PP").Select
EndRow_Via = Cells(Rows.Count, 1).End(xlUp).Row
'
'Duplica l'area delle coordinate (CREDO CHE ORA SIA INUTILE):
Range(Cells(2, 1), Cells(EndRow_Via, 2)).Copy Cells(2, 11)
'Cerco gli indirizzi:
For Cerca_Via = 2 To EndRow_Via
    Latitude = Worksheets("PP").Cells(Cerca_Via, 11)
    Longitude = Worksheets("PP").Cells(Cerca_Via, 12)
    Worksheets("PP").Cells(Cerca_Via, 10) = myStrAddress(Latitude, Longitude)
Next Cerca_Via
'
End Sub

Contiene delle semplificazione al tuo codice che vanno sempre bene.

Quanto al limite delle query, e' descritto nelle specifiche google; a pagamento si puo' ottenere di piu'.

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 17646
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "[ExcelVBa] Vie tramite coordinate":


Chi c’è in linea

Visitano il forum: Bing [Bot] e 6 ospiti