I have been searching around for tips on using the google maps api's to get data into a workbook. Basically what I'm trying to do is, based on an address (or part of an address) input by the workbook user, get details of the Town/City, country etc.

I've found all this information is provided by googleapis, using the http://maps.googleapis.com/maps/api/geocode api, but am having difficulty in getting the info back out of the xml file.

This is the code i'm using (pieced together from various sources):

Function GoogleGeocode(address As String) As String
Dim strAddress As String
Dim strQuery As String
Dim strLatitude As String
Dim strLongitude As String

strAddress = URLEncode(address)

strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
strQuery = strQuery & "address=" & strAddress
strQuery = strQuery & "&sensor=false"
Dim googleResult As New MSXML2.DOMDocument
Dim googleService As New MSXML2.XMLHTTP

googleService.Open "GET", strQuery, False
googleService.send
googleResult.LoadXML (googleService.responseText)

Dim oNodes As MSXML2.IXMLDOMNodeList
Dim oNode As MSXML2.IXMLDOMNode
Set oNodes = googleResult.getElementsByTagName("address_component")
For Each oNode In oNodes
If Not oNode Is Nothing Then
Debug.Print oNode.Text
End If
Next
End Function

Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
Dim StringLen As Long: StringLen = Len(StringVal)

If StringLen > 0 Then
ReDim result(StringLen) As String
Dim i As Long, CharCode As Integer
Dim Char As String, Space As String

If SpaceAsPlus Then Space = "+" Else Space = "%20"

For i = 1 To StringLen
Char = Mid$(StringVal, i, 1)
CharCode = Asc(Char)

Select Case CharCode
Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
result(i) = Char
Case 32
result(i) = Space
Case 0 To 15
result(i) = "%0" & Hex(CharCode)
Case Else
result(i) = "%" & Hex(CharCode)
End Select
Next i
URLEncode = Join(result, "")
End If
End Function

Basically I've got this far, just need some tips on converting the data back into excel, especially when Google provides details for more than one match (which it does if the address is unclear)

Try http://maps.googleapis.com/maps/api/...n&sensor=false - this gives an idea of the response that Google provides

Really really appreciate any tips - I'm fairly experienced in VBA, but XML and tags etc. is all new to me