Hello. I have a similar need; since I'm now a 1099 on-my-own "consultant", I need to prove my mileage to the IRS. I have addresses in Google Calendar for each of my appointments, and my starting point is set.

Using Excel 2010, I'm trying to use the above code w/no luck. I've gone into VBA & done an INSERT MODULE with the info above. I then put two different addresses into A1 & A2, made B1 & B2 the same as listed, but they keep coming back with #VALUE! .

I'm a Newb for programming in Excel, but this is getting exciting.

I've included the code info, but I don't see as it's any different from above.

Suggestions?

'shred dude vbax
Public Function getGoogDistanceTime(rngSAdd As Range, rngEAdd As Range, Optional strReturn As String = "distance") As String
    Dim sURL As String
    Dim BodyTxt As String
    sURL = "http://maps.google.com/maps?f=d&source=s_d"
        sURL = sURL & "&saddr=" & Replace(rngSAdd(1).Value, " ", "+")
        sURL = sURL & "&daddr=" & Replace(rngEAdd(1).Value, " ", "+")
        sURL = sURL & "&hl=en"
    BodyTxt = getHTML(sURL)
    If InStr(1, BodyTxt, strReturn, vbTextCompare) = 0 Then
        getGoogDistanceTime = "Error"
    Else
        getGoogDistanceTime = parseGoog(strReturn, BodyTxt)
    End If
End Function
 
Public Function getHTML(strURL As String) As String
     'Returns the HTML code underlying a given URL
    Dim oXH As Object
    Set oXH = CreateObject("msxml2.xmlhttp")
    With oXH
        .Open "get", strURL, False
        .send
        getHTML = .responseText
    End With
    Set oXH = Nothing
End Function

Public Function parseGoog(strSearch As String, strHTML As String) As String
strSearch = strSearch & ":"""
If InStr(1, strHTML, strSearch) = 0 Then parseGoog = "Not Found": Exit Function
parseGoog = Mid(strHTML, InStr(1, strHTML, strSearch) + Len(strSearch))
parseGoog = Mid(parseGoog, 1, InStr(1, parseGoog, """") - 1)
End Function
and this...
B2=GetGoogDistanceTime(#REF!,#REF!,"distance")
Thanks!
scott
mailto:sellen@aol.com