I want to parse the latitude/longitude of a given address in excel, using xmlhttp. I have the following code that does not work. Can anyone help?
Sub Macro1()
'Create the xmlhttp object
Dim xmlhttp
Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
'Open a connection and send a request to the server in the form of an XML fragment
Call xmlhttp.Open("POST", "http://maps.google.com/maps/geo?q=Rua+Candido+Espinheira+540,Sao+Paulo,Brazil&output=xml&key=entergoogleapikeyhere", False)
Call xmlhttp.send("")
'Create an xml document object, and load the server's response
Dim xmldoc
Set xmldoc = CreateObject("Microsoft.XMLDOM")
xmldoc.async = False
'Note: the ResponseXml property parses the server's response, responsetext doesn't
xmldoc.loadxml (xmlhttp.responsexml)
'Use the server's information to update the spreadsheet
Worksheets("TimeSheet").Range("A1").Value = xmldoc.documentelement.getAttribute("coordinates")
End Sub
the errors in on the xmldoc.loadxml (xmlhttp.responsexml) line
the xml file (if I use response text) follows:
<?xml version="1.0" encoding="UTF-8"?><kml xmlns="http://earth.google.com/kml/2.0"><Response><name>Rua Candido Espinheira 540,Sao Paulo,Brazil</name><Status><code>200</code><request>geocode</request></Status><Placemark id="p1"><address>R. Dr. Cândido Espinheira 540, Barra Funda, São Paulo, 05004-000, Brazil</address><AddressDetails Accuracy="8" xmlns="urn
asis:names:tc:ciq:xsdschema:xAL:2.0"><Country><CountryNameCode>BR</CountryNameCode><AdministrativeArea><AdministrativeAreaName>SP</AdministrativeAreaName><Locality><LocalityName>São Paulo</LocalityName><DependentLocality><DependentLocalityName>Barra Funda</DependentLocalityName><Thoroughfare><ThoroughfareName>R. Dr. Cândido Espinheira 540</ThoroughfareName></Thoroughfare><PostalCode><PostalCodeNumber>05004-000</PostalCodeNumber></PostalCode></DependentLocality></Locality></AdministrativeArea></Country></AddressDetails><Point><coordinates>-46.664448,-23.532852,0</coordinates></Point></Placemark></Response></kml>
I have, of course, my own google maps api key, which I've erased on this post.
Sub Macro1()
'Create the xmlhttp object
Dim xmlhttp
Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
'Open a connection and send a request to the server in the form of an XML fragment
Call xmlhttp.Open("POST", "http://maps.google.com/maps/geo?q=Rua+Candido+Espinheira+540,Sao+Paulo,Brazil&output=xml&key=entergoogleapikeyhere", False)
Call xmlhttp.send("")
'Create an xml document object, and load the server's response
Dim xmldoc
Set xmldoc = CreateObject("Microsoft.XMLDOM")
xmldoc.async = False
'Note: the ResponseXml property parses the server's response, responsetext doesn't
xmldoc.loadxml (xmlhttp.responsexml)
'Use the server's information to update the spreadsheet
Worksheets("TimeSheet").Range("A1").Value = xmldoc.documentelement.getAttribute("coordinates")
End Sub
the errors in on the xmldoc.loadxml (xmlhttp.responsexml) line
the xml file (if I use response text) follows:
<?xml version="1.0" encoding="UTF-8"?><kml xmlns="http://earth.google.com/kml/2.0"><Response><name>Rua Candido Espinheira 540,Sao Paulo,Brazil</name><Status><code>200</code><request>geocode</request></Status><Placemark id="p1"><address>R. Dr. Cândido Espinheira 540, Barra Funda, São Paulo, 05004-000, Brazil</address><AddressDetails Accuracy="8" xmlns="urn
asis:names:tc:ciq:xsdschema:xAL:2.0"><Country><CountryNameCode>BR</CountryNameCode><AdministrativeArea><AdministrativeAreaName>SP</AdministrativeAreaName><Locality><LocalityName>São Paulo</LocalityName><DependentLocality><DependentLocalityName>Barra Funda</DependentLocalityName><Thoroughfare><ThoroughfareName>R. Dr. Cândido Espinheira 540</ThoroughfareName></Thoroughfare><PostalCode><PostalCodeNumber>05004-000</PostalCodeNumber></PostalCode></DependentLocality></Locality></AdministrativeArea></Country></AddressDetails><Point><coordinates>-46.664448,-23.532852,0</coordinates></Point></Placemark></Response></kml>I have, of course, my own google maps api key, which I've erased on this post.
