I found this fantastic vba script which takes an address and returns the latitude and longitude using google's maps API. However, google has rate-limiting features, and I want to do this for a large number of addresses (approx 3 mil). The script, on the other hand, has no rate-limiting, making them rather incompatible. Is it possible to edit this script so that it doesn't move on to the next cell until the previous request has been completed?
Function MyGeocode(address As String) As String Dim strAddress As String Dim strQuery As String Dim strLatitude As String Dim strLongitude As String strAddress = URLEncode(address) 'Assemble the query string strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?" strQuery = strQuery & "address=" & strAddress strQuery = strQuery & "&sensor=false" 'define XML and HTTP components Dim googleResult As New MSXML2.DOMDocument60 Dim googleService As New MSXML2.XMLHTTP60 Dim oNodes As MSXML2.IXMLDOMNodeList Dim oNode As MSXML2.IXMLDOMNode 'create HTTP request to query URL - make sure to have 'that last "False" there for synchronous operation googleService.Open "GET", strQuery, False googleService.send googleResult.LoadXML (googleService.responseText) Set oNodes = googleResult.getElementsByTagName("geometry") If oNodes.Length = 1 Then For Each oNode In oNodes strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text MyGeocode = strLatitude & "," & strLongitude Next oNode Else MyGeocode = "Not Found (try again, you may have done too many too fast)" End If 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
Any help would be greatly appreciated, you guys are the best!