vba-计算两地间距离及耗时

2020/7/17 15:15
jimmy

效果图如下：

VBA代码下如：

Sub Main()

Dim strText, URL, CityFrom, CityTo As String
Dim dis, mtime
CityFrom = Sheet1.Range("B2").Value '假想值为江门
CityTo = Sheet1.Range("C2").Value ''假想值为深圳

If CityFrom = "" Or CityTo = "" Then
MsgBox "开始或终点地址为空！"
Else

URL = "http://map.baidu.com/?"
URL = URL & "qt=nav"
URL = URL & "&c=223"
URL = URL & "&sn=2$$$$$$" & CityFrom & "$$0$$$$"
URL = URL & "&en=2$$$$$$" & CityTo & "$$0$$$$"

With CreateObject("MSXML2.XMLHTTP")
.Open "GET", URL, False
.Send
strText = .responsetext
dis = Val(Split(strText, """dis"":")(1))
mtime = Val(Mid(strText, InStrRev(strText, """time"":") + 7))
MsgBox "约" & Format(dis / 1000, "0.00公里/") & Format(mtime / 86400, "hh小时mm分钟")
End With
Sheet1.Range("D2").Value = Format(dis / 1000, "0.00公里")
Sheet1.Range("E2").Value = Format(mtime / 86400, "hh:mm:ss")
End If

End Sub