Google Maps API を使って2点間の距離を求めるユーザー定義関数を作りたいのですが、画像のようなエラーになり進めません!ご教授願いたいです。当方はVBAずぶの素人であり、以下のサイトを参考にしています。
https://www.excel-pitin.net/post/2018/01/19/google-maps-api-%E3%82%92%E4%BD%BF%E3%81%A3%E3%81%A62%E7%82%B9%E9%96%93%E3%81%AE%E8%B7%9D%E9%9B%A2%E3%82%92%E6%B1%82%E3%82%81%E3%82%8B%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E5%AE%9A%E7%BE%A9%E9%96%A2%E6%95%B0%E3%82%92%E4%BD%9C%E3%82%8B
VBA
1Function DGMAP(origin As String, destination As String, distance As Boolean) As String 2 3 'http://stackoverflow.com/questions/10116301/google-maps-api-for-time-and-distance-data-access-vba 4 5 6 Dim sXMLURL As String 7 sXMLURL = "https://maps.googleapis.com/maps/api/directions/xml?origin=" & origin & "&destination=" & destination & "&sensor=false&key=APIキー" 8 9 10 Dim objXMLHTTP As MSXML2.ServerXMLHTTP60 11 12 Set objXMLHTTP = New MSXML2.ServerXMLHTTP60 13 14 15 With objXMLHTTP 16 17 .Open "GET", sXMLURL, False 18 19 .setRequestHeader "Content-Type", "application/x-www-form-URLEncoded" 20 21 .Send 22 23 End With 24 25 26 'Debug.Print objXMLHTTP.ResponseText 27 28 29 Dim domResponse As DOMDocument60 30 31 Set domResponse = New DOMDocument60 32 33 domResponse.LoadXML objXMLHTTP.ResponseText 34 35 Dim ixnStatus 36 37 Set ixnStatus = domResponse.SelectSingleNode("//status") 38 39 40 If ixnStatus.Text = "OK" Then 41 42 Dim ixnDistance, ixnDuration 43 44 Set ixnDistance = domResponse.SelectSingleNode("/DirectionsResponse/route/leg/distance/text") 45 46 Set ixnDuration = domResponse.SelectSingleNode("/DirectionsResponse/route/leg/duration/text") 47 48 End If 49 50 If IsEmpty(ixnDistance) Then 51 52 DGMAP = "Empty" 53 54 Exit Function 55 56 End If 57 '距離(True) または時間(False) 58 59 If distance = True Then 60 61 DGMAP = left(ixnDistance.Text, InStr(1, ixnDistance.Text, " ") - 1) 62 63 Else 64 65 DGMAP = ixnDuration.Text 66 67 End If 68 69 Set domResponse = Nothing 70 71 Set objXMLHTTP = Nothing 72 73End Function
回答2件
あなたの回答
tips
プレビュー