I have a long list of Zip Codes (approximately 350) that I need to generate mileage to about 65 locations. I found VBA code that works perfectly to generate the mileage; however, the Google API limits make it where about 1/3 of the cells end up returning zero when I try to AutoFill the formulas. Is there a VBA code that would add some time between sending the request to Google?
Here is the current VBA code:
Function G_DISTANCE(Origin As String, Destination As String) As Double
Dim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim distanceNode As IXMLDOMNode
G_DISTANCE = 0
On Error GoTo exitRoute
Origin = Replace(Origin, " ", "%20")
Destination = Replace(Destination, " ", "%20")
Set myRequest = New XMLHTTP60
myRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
& Origin & "&destination=" & Destination & "&sensor=false", False
myRequest.send
Set myDomDoc = New DOMDocument60
myDomDoc.LoadXML myRequest.responseText
Set distanceNode = myDomDoc.SelectSingleNode("//leg/distance/value")
If Not distanceNode Is Nothing Then G_DISTANCE = (distanceNode.Text / 1000) * 0.62137119 'convert from km to miles
exitRoute:
Set distanceNode = Nothing
Set myDomDoc = Nothing
Set myRequest = Nothing
End Function
And the array formula I am using to calculate mileage:
=ROUND(G_DISTANCE($D3,L$2),0)
I am wanting to AutoFill from L3:BW350 without having to fill only a few cells at a time.
I would really appreciate any ideas for this!
Here is the current VBA code:
Function G_DISTANCE(Origin As String, Destination As String) As Double
Dim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim distanceNode As IXMLDOMNode
G_DISTANCE = 0
On Error GoTo exitRoute
Origin = Replace(Origin, " ", "%20")
Destination = Replace(Destination, " ", "%20")
Set myRequest = New XMLHTTP60
myRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
& Origin & "&destination=" & Destination & "&sensor=false", False
myRequest.send
Set myDomDoc = New DOMDocument60
myDomDoc.LoadXML myRequest.responseText
Set distanceNode = myDomDoc.SelectSingleNode("//leg/distance/value")
If Not distanceNode Is Nothing Then G_DISTANCE = (distanceNode.Text / 1000) * 0.62137119 'convert from km to miles
exitRoute:
Set distanceNode = Nothing
Set myDomDoc = Nothing
Set myRequest = Nothing
End Function
And the array formula I am using to calculate mileage:
=ROUND(G_DISTANCE($D3,L$2),0)
I am wanting to AutoFill from L3:BW350 without having to fill only a few cells at a time.
I would really appreciate any ideas for this!