ExcelにBing Mapsを挿入し、Google Places APIで取得した住所から店舗の分布地図を作成:VBA/マクロ便利Tips(2/3 ページ)
業務効率化に役立つVBA/マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、Geocoding APIとGoogle Places APIを利用して、指定した住所から半径1Km範囲内にあるコンビニエンスストアを表示する方法などを解説。
住所とコンビニ名の一覧表示
VBEのメニューから、[挿入]→[標準モジュール]と選択する。プロジェクトにModule1が追加されるので、Module1をダブルクリックして、表示されるエディター画面内にリスト1のコードを記述する。
Option Explicit Sub コンビニの表示() Dim googleAppID As String googleAppID = "取得したAPIキー" Dim xmldoc As MSXML2.XMLHTTP Dim myUri As String Set xmldoc = CreateObject("MSXML2.XMLHTTP") If Range("C2").Value = "" Then MsgBox "正確な住所を入力してください。" Exit Sub End If myUri = "http://www.geocoding.jp/api?v=1.1&q=" & Range("C2").Value xmldoc.Open "POST", myUri, False xmldoc.send (Null) Dim doc As MSXML2.DOMDocument Set doc = CreateObject("MSXML2.DOMDocument") doc.LoadXML(xmldoc.responseText) Dim myNodes As MSXML2.IXMLDOMNodeList Dim myLatitude As String Dim myLongitude As String Set myNodes = doc.SelectNodes("result/coordinate") Dim i As Integer For i = 0 To myNodes.Length - 1 myLatitude = myNodes(i).ChildNodes(0).Text myLongitude = myNodes(i).ChildNodes(1).Text Next Dim myAddressCoodinatePosition As String myAddressCoodinatePosition = myLatitude & "," & myLongitude Dim googleUri As String Dim googleXmldoc As MSXML2.XMLHTTP Set googleXmldoc = CreateObject("MSXML2.XMLHTTP") googleUri = "https://maps.googleapis.com/maps/api/place/nearbysearch/xml?location=" & myAddressCoodinatePosition & "&radius=1000&types=convenience_store&sensor=true&language=ja&key=" & googleAppID googleXmldoc.Open "POST", googleUri, False googleXmldoc.send (Null) Dim g_doc As MSXML2.DOMDocument Set g_doc = CreateObject("MSXML2.DOMDocument") g_doc.LoadXML (googleXmldoc.responseText) Dim g_myNodes As MSXML2.IXMLDOMNodeList Dim j As Integer Set g_myNodes = g_doc.SelectNodes("//result") Range("A2:A30").Value = "" For j = 0 To g_myNodes.Length - 1 Cells(j + 2, 1) = g_myNodes(j).ChildNodes(1).Text & ":" & g_myNodes(j).ChildNodes(0).Text Next Set xmldoc = Nothing Set doc = Nothing Set googleXmldoc = Nothing Set g_doc = Nothing End Sub
リスト1の中身を順に解説していこう。
3〜4行目で文字列型の変数「googleAppID」を宣言し、取得した「APIキー」を格納しておく。
5〜6行目でMSXML2.XMLHTT型の変数「xmldoc」と文字列型の変数「myUri」を宣言する。7行目では、CreateObject関数で「MSXML2.XMLHTTP」ActiveXオブジェクトへの参照を作成し、オブジェクト型変数「xmldoc」に代入する。
9行目では、住所を入力するセル「C2」が未入力かどうか判定し、未入力の場合は、警告メッセージを発して処理を抜けるようにしている。
14行目で、変数「myUri」に、GeocodingのリクエストURIを格納する。引数「q」には住所を入力したセル「C2」の値を指定する。
15行目では、xmldocのOpenメソッドでmyUriに格納されたリクエストURIをPOSTする。その際、「async」に「False」を指定する意味で、「xmldoc.Open "POST", myUri, False」と記述する。「MSXML2.XMLHTTP」はデフォルトでは非同期であるため、ここで「False」を指定して同期させている。
16行目では、SendメソッドでリクエストURIを送っている。
17行目でMSXML2.DOMDocument型の変数「doc」を宣言し、18行目ではCreateObject関数で、「MSXML2.DOMDocument」ActiveXオブジェクトへの参照を作成し、オブジェクト型変数「doc」に代入する。19行目ではLoadXMLメソッドで、GeocodingリクエストURIからレスポンスされたXMLの内容を文字列として読み込んでいる。
21〜23行目で、MSXML2.IXMLDOMNodeList型の変数「myNodes」、文字列型の変数「myLatitude」と「myLongitude」を宣言しておく。
25行目のSelectNodesメソッドで、指定したXPathであるresult要素内のcoordinate要素にアクセスする。SelectNodesメソッドは、XPath式と一致するノードのリストを選択するメソッドだ。
26行目でループカウンター用のInteger型変数「i」を宣言し、27〜30行目で「0」から変数「myNodes」のノードのリストが格納している要素の分だけ繰り返し処理を行う。「-1」しているのは、lengthは「1」からカウントし、変数「myNodes」のノードのリストのインデックスは「0」から開始するためだ。
19行目のdoc.LoadXML(xmldoc.responseText)で返されるXMLの構造はリスト2のようになっている。
<?xml version=""1.0""?> <result> <version>1.1</version> <address>愛媛県松山市道後緑台</address> <coordinate> <lat>33.853187</lat> <lng>132.780416</lng> <lat_dms>33,51,11.473</lat_dms> <lng_dms>132,46,49.498</lng_dms> </coordinate> <url>http://www.geocoding.jp/?q=%E6%84%9B%E5%AA%9B%E7%9C%8C%E6%9D%BE%E5%B1%B1%E5%B8%82%E9%81%93%E5%BE%8C%E7%B7%91%E5%8F%B0</url> <needs_to_verify>no</needs_to_verify> <google_maps>愛媛県松山市道後緑台</google_maps> </result>
27〜30行目の繰り返し処理の過程で、変数「myLatitude」にcoordinate要素内の、最初のChildNodesである子要素、インデックスは「0」の内容(lat)を取得する。これで「緯度」が取得できる。次にcoordinate要素の二番目のChildNodesである子要素、インデックスは「1」の内容(lng)を取得する。これで「経度」が取得できる。
次に32〜33行目では、文字列型変数「myAddressCoodinatePosition」を宣言し、取得した緯度と経度を「,(カンマ)」で区切って格納しておく。
35行目では、文字列型の変数「googleUri」を宣言し、36行目では「MSXML2.XMLHTTP」型の変数「googleXmldoc」を宣言する。37行目では、CreateObject関数で「MSXML2.XMLHTTP」ActiveXオブジェクトへの参照を作成し、オブジェクト型変数「googleXmldoc」に代入する。
38行目では、変数「googleUri」にGoogleのPlaces APIであるリクエストURIを格納している。1行が長いので、下記に抜粋して再掲しておく。
googleUri = "https://maps.googleapis.com/maps/api/place/nearbysearch/xml?location=" & myAddressCoodinatePosition & "&radius=1000&types=convenience_store&sensor=true&language=ja&key=" & googleAppID
引数「location」には緯度と経度をカンマで区切った値を格納している変数、「myAddressCoodinatePosition」を指定する。「radius」には「1000」m(1Km)を指定、「types」にはコンビニエンスストアを表す「convenience_store」を指定している。「sensor」には「true」を指定しているが、PCからの利用なので「false」でも問題はない。「language」には「ja」を指定し、「key」には取得したAPIキーを格納している変数「googleAppID」を指定する。
なお「types」には「convenience_store」以外にもいろいろな施設を指定できる。指定できるtypesについては「サポートされるプレイス タイプ - Google Places API - Google Developers」を参照してほしい。
39行目のgoogleXmldocのOpenメソッドで、googleUriに格納されたリクエストURIをPOSTする。その際、asyncにFalseを指定する意味で、「googleXmldoc.Open "POST", googleUri, False」と記述する。40行目では、SendメソッドでリクエストURIを送っている。
42行目では、MSXML2.DOMDocument型の変数「g_doc」を宣言。43行目ではCreateObject関数で、「MSXML2.DOMDocument」ActiveXオブジェクトへの参照を作成し、オブジェクト型変数「g_doc」に代入する。
44行目のLoadXMLメソッドで、「Google Places API」のリクエストURIから、レスポンスされたXMLの内容を文字列として読み込んでおく。
45行目でMSXML2.IXMLDOMNodeList型の変数「g_myNodes」を、46行目で Integer型変数「j」を宣言しておく。
47行目のSelectNodesメソッドで、指定したXPathであるresult要素内にアクセスする。
48行目で「住所とコンビニ名」を表示する「A2:A30」までの範囲をクリアしておく。「Google Places API」で取得できる施設の最大件数は20件までなので、今回は念のためセル「A30」までをクリアしておいた。実際にはセル「A21」までで問題はないが、用心のため多くクリアしておけば問題発生を抑えることができるだろう。
50〜52行目で、変数「j」を「0」から変数「g_myNodes」のノードのリストが格納している要素の分だけ、繰り返し処理を行う。「-1」しているのは、lengthは「1」からカウントし、変数「g_myNodes」のノードのリストのインデックスは「0」から開始するためだ。
43行目のg_doc.LoadXML(googleXmldoc.responseText)で返されるXMLの構造はリスト3のような構造になっている。
<?xml version=""1.0""?> <PlaceSearchResponse> <status>OK</status> <result> <name>ローソン 松山道後樋又店</name> <vicinity>松山市道後樋又1182?2</vicinity> <type>convenience_store</type> <type>food</type> <type>store</type> <type>establishment</type> <geometry> <location> <lat>33.8526150</lat> <lng>132.7701400</lng> </location> </geometry> <icon>http://maps.gstatic.com/mapfiles/place_api/icons/shopping-71.png</icon> <reference>CpQBhAAAAMw4KanLyHd8cUYI60zrDjiIFa1AlBH7ZkhtX9NYnv6ljhCJwl3EGRTixd9yCyYpHc7Kvj5zAcjXLRpWEzKVbSKL9zO8aDoYIhueHL-b3op0k6MERML5a6EoI8lJrfKrFZWhn-934NCNDOqypBE40GLTDsSZDwnMS-PZkWGW2tN6Gz7Re7jRvQcUHLoVANfgsRIQI6jGXU_XqWFUYQWVsiqmGhoUD5pPjdkInYWBI10ogvzmeH8mB5s</reference> <id>16374648e8ac5fc5c91d34f295d57bae4f912c50</id> <price_level>1</price_level> </result> 〜<result></result>繰り返し〜 </PlaceSearchResponse>
50〜52行目の繰り返し処理の過程で、「Cells(行番号,列番号)」の位置、この場合は「住所とコンビニ名」のセルに、result要素の1番目の要素の値、つまり「vicinity」要素の内容(住所)を取得し、文字列「:」で、result要素の「0番目の要素」の値、つまり「name」要素の内容(コンビニ名)を連結して表示する。
最後に54〜57行目で「xmldoc」「doc」「googleXmldoc」「g_doc」変数を全ての関連付けから解放する。
このリスト1を図5の「実行」ボタンに関連付け、住所を入力して「実行」ボタンをクリックすると、図7のように「住所とコンビニ名」が表示される。
Copyright © ITmedia, Inc. All Rights Reserved.