Custom Function to get Drive Time between two points

[randolphoralph]

I ran across a great article on performing Drive Time Calculations between two addresses. The only problem is that I can not seem to get it to work properly. Here is a link to the article cause I want full credit to be given to the person that created it.

http://guj800.wordpress.com/2011/04/27/drive-time-calculations-in-excel/

Here are the steps I have taken so far.

1. Downloaded Microsoft Internet Transfer Control reference (msinet.ocx) per http://guj800.wordpress.com/2008/06/30/getting-information-from-the-web-using-excel-vba/
2. Registered the msinet.ocx
3. Selected Microsoft Internet Transfer Control as a Reference in Visual Basic.
4. Copied the custom function for DriveTime into a module.
5. Went to sheet that contained addresses (Column A contains PointA, Column B contains PointB)
6. In C2 I entered the formula =DriveTime(A2,B2)
7. After entering the formula I get a error (#VALUE)

I am not sure where I have gone wrong and was hoping someone maybe able to point me in the right direction.

Any help would be greatly appreciated.

 

[John_w]

Have you Googled "Error 429 – cannot create Activex object Inet"?

For example, the last post in http://www.vbaexpress.com/forum/archive/index.php/t-960.html suggests a licensing issue.

Also, compare your msinet.ocx file (in C:\Windows\system32) with mine - I gave date and size details - or other known versions. Maybe you have a corrupted copy.

You could try XMLhttp instead of the Inet control, as shown in the following code. This requires a reference to Microsoft XML (v6.0).

Note that because the forum renders HTML tags (between < and > characters) in a forum message I've had to prevent this by adding '|' characters to the findHTML strings and using Replace to remove them.

Public Function XMLDriveTime(PointA As String, PointB As String)
    
    Static XMLhttp As XMLhttp
    Dim URL As String
    Dim HTML As String
    Dim findHTML As String
    Dim start As Long, length As Long
    
    URL = "http://maps.google.com/maps?q=from: " & PointA & " to: " & PointB
    
    If XMLhttp Is Nothing Then Set XMLhttp = New XMLhttp
    
    With XMLhttp
        .Open "GET", URL, False
        .send
        HTML = .responseText
    End With
        
    findHTML = Replace("<|div class=""altroute-rcol altroute-info""|>", "|", "")
    start = InStr(HTML, findHTML) + Len(findHTML)
    findHTML = Replace("<|/div|>", "|", "")
    length = InStr(start, HTML, findHTML) - start
    XMLDriveTime = Mid(HTML, start, length)
  
End Function

 

[randolphoralph]

Thanks John_w that worked perfectly. A+ for your continued efforts

 

[Roli001]

Been looking for this solution for a long time. Thx.

 

[beastman59]

Thanks John_w after trying your suggestion I was able to determine where the problem is

I am now getting an error “Error 429 – cannot create Activex object” when I issue the command:

Set inet1 = New Inet

Not sure what this means.

See How To Fix (Error 429) ? or use Google and search for other error 429 sites