- 09 Ago 2019 às 09:53
#46675
Estou precisando convertes informacoes de GPS (latitude e longitude) em endereco (cidade).
Encontrei uma integracao com os APIs do Google por~em ao turilizaro o VBA encontrado o mesmo fica apresentando erro de compilacao, alguem consegue me auxliar? codigo abaixo:
Option Explicit
Function GetAddress(Latitude As Double, Longitude As Double, Optional ResultTypeFilter As String) As String
'-----------------------------------------------------------------------------------------------------------------------
'This function returns the address of a given latitude, longitude pair using the Google (Reverse) Geocoding API.
'The optional paramter ResultTypeFilter is a filter of one or more address types, separated by a pipe (|).
'If the parameter contains multiple address types, the API returns all addresses that match any of the types.
'A note about processing: The result_type parameter does not restrict the search to the specified address type(s).
'Rather, the result_type acts as a post-search filter: the API fetches all results for the specified latlng,
'then discards those results that do not match the specified address type(s).
'The following values are supported:
'street_address: indicates a precise street address.
'route: indicates a named route (such as "US 101").
'intersection: indicates a major intersection, usually of two major roads.
'political: indicates a political entity. Usually, this type indicates a polygon of some civil administration.
'country: indicates the national political entity, and is typically the highest order type returned by the Geocoder.
'administrative_area_level_1: indicates a first-order civil entity below the country level. Within the United States,
'these administrative levels are states. Not all nations exhibit these administrative levels.
'In most cases, administrative_area_level_1 short names will closely match ISO 3166-2 subdivisions and other
'widely circulated lists; however this is not guaranteed as our geocoding results are based on a variety of
'signals and location data.
'administrative_area_level_2: indicates a second-order civil entity below the country level. Within the United States,
'these administrative levels are counties. Not all nations exhibit these administrative levels.
'administrative_area_level_3: indicates a third-order civil entity below the country level.
'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
'administrative_area_level_4: indicates a fourth-order civil entity below the country level.
'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
'administrative_area_level_5: indicates a fifth-order civil entity below the country level.
'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
'colloquial_area" indicates a commonly-used alternative name for the entity.
'locality: indicates an incorporated city or town political entity.
'ward: indicates a specific type of Japanese locality, to facilitate distinction between multiple
'locality components within a Japanese address.
'sublocality: indicates a first-order civil entity below a locality. For some locations may receive one of the
'additional types: sublocality_level_1 to sublocality_level_5. Each sublocality level is a civil entity.
'Larger numbers indicate a smaller geographic area.
'neighborhood: indicates a named neighborhood
'premise: indicates a named location, usually a building or collection of buildings with a common name
'subpremise: indicates a first-order entity below a named location, usually a singular building within
'a collection of buildings with a common name
'postal_code: indicates a postal code as used to address postal mail within the country.
'natural_feature: indicates a prominent natural feature.
'airport: indicates an airport.
'park: indicates a named park.
'point_of_interest: indicates a named point of interest. Typically, these "POI"s are prominent local entities that
'don't easily fit in another category, such as "Empire State Building" or "Statue of Liberty."
'The function ignores parameters such as language and location_type and returns always the FIRST RESULT.
'NOTE: As Google points out, the use of the Google Geocoding API is subject to a limit of 40,000
'requests per month, so be careful not to exceed this limit. For more info check:
'https://cloud.google.com/maps-platform/pricing/sheet
'In order to use this function you must enable the XML, v3.0 library from VBA editor:
'Go to Tools -> References -> check the Microsoft XML, v3.0.
'Moreover, to use this function you will also need a valid API key.
'Check the next link that guides you on how to acquire a free API key:
'https://www.myengineeringworld.net/2018 ... i-key.html
'Written By: Christos Samaras
'Date: 17/03/2018
'Last Updated: 09/08/2018
'E-mail: xristos.samaras@gmail.com
'Site: https://www.myengineeringworld.net
'-----------------------------------------------------------------------------------------------------------------------
'Declaring the necessary variables. Using 30 at the first two variables because it
'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll).
Dim ApiKey As String
Dim Request As New XMLHTTP30
Dim Results As New DOMDocument30
Dim StatusNode As IXMLDOMNode
Dim AddressNode As IXMLDOMNode
'Set your API key in this variable. Check this link for more info:
'https://www.myengineeringworld.net/2018 ... i-key.html
ApiKey = "key google"
'Check that an API key has been provided.
If ApiKey = vbNullString Or ApiKey = "Your API Key goes here!" Then
GetAddress = "Invalid API Key"
Exit Function
End If
'Check the input variables:
'The valid range of latitude in degrees is -90 and +90 for the Southern and Northern hemisphere respectively.
If Latitude < -90 Or Latitude > 90 Then
GetAddress = "Invalid Latitude value"
Exit Function
End If
'Longitude is in the range -180 and +180 specifying coordinates West and East of the Prime Meridian, respectively.
If Longitude < -180 Or Longitude > 180 Then
GetAddress = "Invalid Longitude value"
Exit Function
End If
'Generic error handling.
On Error GoTo errorHandler
'Create the request based on Google's (Reverse) Geocoding API. Parameters:
'- latlng: The latitude and longitude values specifying the location for which you wish to obtain the closest, human-readable address.
'- key: Your application's API key. This key identifies your application for purposes of quota management.
'Differentiate the request if a filter is provided.
If ResultTypeFilter = vbNullString Then
Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _
& "latlng=" & Latitude & "," & Longitude & "&key=" & ApiKey, False
Else
Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _
& "latlng=" & Latitude & "," & Longitude & "&result_type=" & ResultTypeFilter & "&key=" & ApiKey, False
End If
'Send the request to the Google server.
Request.send
'Read the results from the request.
Results.LoadXML Request.responseText
'Get the status node value.
Set StatusNode = Results.SelectSingleNode("//status")
'Based on the status node result, proceed accordingly.
Select Case UCase(StatusNode.Text)
Case "OK" 'The API request was successful. At least one result was returned.
'Get the formatted address of the first result.
Set AddressNode = Results.SelectSingleNode("//result/formatted_address")
'Return the address.
GetAddress = AddressNode.Text
Case "ZERO_RESULTS" 'The geocode was successful but returned no results.
GetAddress = "The address probably not exists"
Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day.
GetAddress = "Requestor has exceeded the server limit"
Case "REQUEST_DENIED" 'The API did not complete the request.
GetAddress = "Server denied the request"
Case "INVALID_REQUEST" 'The API request is empty or is malformed.
GetAddress = "Request was empty or malformed"
Case "UNKNOWN_ERROR" 'Indicates that the request could not be processed due to a server error.
GetAddress = "Unknown error"
Case Else 'Just in case...
GetAddress = "Error"
End Select
'Release the objects.
errorHandler:
Set AddressNode = Nothing
Set StatusNode = Nothing
Set Results = Nothing
Set Request = Nothing
End Function
Encontrei uma integracao com os APIs do Google por~em ao turilizaro o VBA encontrado o mesmo fica apresentando erro de compilacao, alguem consegue me auxliar? codigo abaixo:
Option Explicit
Function GetAddress(Latitude As Double, Longitude As Double, Optional ResultTypeFilter As String) As String
'-----------------------------------------------------------------------------------------------------------------------
'This function returns the address of a given latitude, longitude pair using the Google (Reverse) Geocoding API.
'The optional paramter ResultTypeFilter is a filter of one or more address types, separated by a pipe (|).
'If the parameter contains multiple address types, the API returns all addresses that match any of the types.
'A note about processing: The result_type parameter does not restrict the search to the specified address type(s).
'Rather, the result_type acts as a post-search filter: the API fetches all results for the specified latlng,
'then discards those results that do not match the specified address type(s).
'The following values are supported:
'street_address: indicates a precise street address.
'route: indicates a named route (such as "US 101").
'intersection: indicates a major intersection, usually of two major roads.
'political: indicates a political entity. Usually, this type indicates a polygon of some civil administration.
'country: indicates the national political entity, and is typically the highest order type returned by the Geocoder.
'administrative_area_level_1: indicates a first-order civil entity below the country level. Within the United States,
'these administrative levels are states. Not all nations exhibit these administrative levels.
'In most cases, administrative_area_level_1 short names will closely match ISO 3166-2 subdivisions and other
'widely circulated lists; however this is not guaranteed as our geocoding results are based on a variety of
'signals and location data.
'administrative_area_level_2: indicates a second-order civil entity below the country level. Within the United States,
'these administrative levels are counties. Not all nations exhibit these administrative levels.
'administrative_area_level_3: indicates a third-order civil entity below the country level.
'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
'administrative_area_level_4: indicates a fourth-order civil entity below the country level.
'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
'administrative_area_level_5: indicates a fifth-order civil entity below the country level.
'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
'colloquial_area" indicates a commonly-used alternative name for the entity.
'locality: indicates an incorporated city or town political entity.
'ward: indicates a specific type of Japanese locality, to facilitate distinction between multiple
'locality components within a Japanese address.
'sublocality: indicates a first-order civil entity below a locality. For some locations may receive one of the
'additional types: sublocality_level_1 to sublocality_level_5. Each sublocality level is a civil entity.
'Larger numbers indicate a smaller geographic area.
'neighborhood: indicates a named neighborhood
'premise: indicates a named location, usually a building or collection of buildings with a common name
'subpremise: indicates a first-order entity below a named location, usually a singular building within
'a collection of buildings with a common name
'postal_code: indicates a postal code as used to address postal mail within the country.
'natural_feature: indicates a prominent natural feature.
'airport: indicates an airport.
'park: indicates a named park.
'point_of_interest: indicates a named point of interest. Typically, these "POI"s are prominent local entities that
'don't easily fit in another category, such as "Empire State Building" or "Statue of Liberty."
'The function ignores parameters such as language and location_type and returns always the FIRST RESULT.
'NOTE: As Google points out, the use of the Google Geocoding API is subject to a limit of 40,000
'requests per month, so be careful not to exceed this limit. For more info check:
'https://cloud.google.com/maps-platform/pricing/sheet
'In order to use this function you must enable the XML, v3.0 library from VBA editor:
'Go to Tools -> References -> check the Microsoft XML, v3.0.
'Moreover, to use this function you will also need a valid API key.
'Check the next link that guides you on how to acquire a free API key:
'https://www.myengineeringworld.net/2018 ... i-key.html
'Written By: Christos Samaras
'Date: 17/03/2018
'Last Updated: 09/08/2018
'E-mail: xristos.samaras@gmail.com
'Site: https://www.myengineeringworld.net
'-----------------------------------------------------------------------------------------------------------------------
'Declaring the necessary variables. Using 30 at the first two variables because it
'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll).
Dim ApiKey As String
Dim Request As New XMLHTTP30
Dim Results As New DOMDocument30
Dim StatusNode As IXMLDOMNode
Dim AddressNode As IXMLDOMNode
'Set your API key in this variable. Check this link for more info:
'https://www.myengineeringworld.net/2018 ... i-key.html
ApiKey = "key google"
'Check that an API key has been provided.
If ApiKey = vbNullString Or ApiKey = "Your API Key goes here!" Then
GetAddress = "Invalid API Key"
Exit Function
End If
'Check the input variables:
'The valid range of latitude in degrees is -90 and +90 for the Southern and Northern hemisphere respectively.
If Latitude < -90 Or Latitude > 90 Then
GetAddress = "Invalid Latitude value"
Exit Function
End If
'Longitude is in the range -180 and +180 specifying coordinates West and East of the Prime Meridian, respectively.
If Longitude < -180 Or Longitude > 180 Then
GetAddress = "Invalid Longitude value"
Exit Function
End If
'Generic error handling.
On Error GoTo errorHandler
'Create the request based on Google's (Reverse) Geocoding API. Parameters:
'- latlng: The latitude and longitude values specifying the location for which you wish to obtain the closest, human-readable address.
'- key: Your application's API key. This key identifies your application for purposes of quota management.
'Differentiate the request if a filter is provided.
If ResultTypeFilter = vbNullString Then
Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _
& "latlng=" & Latitude & "," & Longitude & "&key=" & ApiKey, False
Else
Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _
& "latlng=" & Latitude & "," & Longitude & "&result_type=" & ResultTypeFilter & "&key=" & ApiKey, False
End If
'Send the request to the Google server.
Request.send
'Read the results from the request.
Results.LoadXML Request.responseText
'Get the status node value.
Set StatusNode = Results.SelectSingleNode("//status")
'Based on the status node result, proceed accordingly.
Select Case UCase(StatusNode.Text)
Case "OK" 'The API request was successful. At least one result was returned.
'Get the formatted address of the first result.
Set AddressNode = Results.SelectSingleNode("//result/formatted_address")
'Return the address.
GetAddress = AddressNode.Text
Case "ZERO_RESULTS" 'The geocode was successful but returned no results.
GetAddress = "The address probably not exists"
Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day.
GetAddress = "Requestor has exceeded the server limit"
Case "REQUEST_DENIED" 'The API did not complete the request.
GetAddress = "Server denied the request"
Case "INVALID_REQUEST" 'The API request is empty or is malformed.
GetAddress = "Request was empty or malformed"
Case "UNKNOWN_ERROR" 'Indicates that the request could not be processed due to a server error.
GetAddress = "Unknown error"
Case Else 'Just in case...
GetAddress = "Error"
End Select
'Release the objects.
errorHandler:
Set AddressNode = Nothing
Set StatusNode = Nothing
Set Results = Nothing
Set Request = Nothing
End Function
