'
' must set references to Microsoft VBScript Regular Expressions, Internet Controls & HTML Object Library
' before running this script
'
'
Public Function GetDistance(startAddr As String, startCity As String, startState As String, startZip As String, _
endAddr As String, endCity As String, endState As String, endZip As String) As String

Dim sURL As String
Dim appIE As InternetExplorer
Dim regex As RegExp, Regmatch As MatchCollection
Dim BodyTxt As String
Dim GetFirstPos As Long

sURL = "http://www.mapquest.com/directions/main.adp?go=1&do=nw&1a="
sURL = sURL & startAddr & "&1c=" & startCity & "&1s=" & startState & "&1z="
sURL = sURL & startZip & "&2a=" & endAddr & "&2c=" & endCity & "&2s="
sURL = sURL & endState & "&2z=" & endZip & "&1y=US&2y=US&cid=lfddlink"

Set appIE = New InternetExplorer
'Set appIE = CreateObject("Internetexplorer.application")

appIE.navigate sURL
appIE.Visible = False
    Do
        DoEvents
    Loop Until appIE.readyState = READYSTATE_COMPLETE

Set regex = New RegExp
With regex
    .pattern = "Total Est. Distance"
    .MultiLine = False
End With

BodyTxt = appIE.document.Body.innerText
Set Regmatch = regex.Execute(BodyTxt)

If Regmatch.count > 0 Then
    GetFirstPos = WorksheetFunction.Find("Total Est. Distance", BodyTxt, 1)
    
    GetDistance = Mid$(BodyTxt, GetFirstPos, 31)
 
    Else
    GetDistance = "Address Error, fix and try again"
End If

appIE.Quit
Set appIE = Nothing
Set regex = Nothing
Set Regmatch = Nothing

End Function