ExcelにBing Mapsを挿入し、Google Places APIで取得した住所から店舗の分布地図を作成：VBA／マクロ便利Tips（2/3 ページ）

» 2014年09月04日 18時00分公開

住所とコンビニ名の一覧表示

　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　「指定した住所のコンビニを表示する」コード

　リスト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>

リスト2　Geocoodingで取得された、指定した住所の緯度と経度のXMLの構造

　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

リスト1の38行目

　引数「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?２</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>

リスト3　g_doc.LoadXML(googleXmldoc.responseText)で返されるXMLの構造

　50～52行目の繰り返し処理の過程で、「Cells(行番号,列番号)」の位置、この場合は「住所とコンビニ名」のセルに、result要素の1番目の要素の値、つまり「vicinity」要素の内容（住所）を取得し、文字列「：」で、result要素の「0番目の要素」の値、つまり「name」要素の内容（コンビニ名）を連結して表示する。

　最後に54～57行目で「xmldoc」「doc」「googleXmldoc」「g_doc」変数を全ての関連付けから解放する。

　このリスト1を図5の「実行」ボタンに関連付け、住所を入力して「実行」ボタンをクリックすると、図7のように「住所とコンビニ名」が表示される。