通勤経路から運賃を自動で取得してみよう【駅すぱあとWebサービス】

連載第2回目の目的

この連載第2回目では、Excel VBAによるWebサービスの呼び出しとXMLデータの解析についての手順を学びます。連載第1回目では、Excelの備える機能にてほぼ自動的にテーブルへの展開を行っていましたが、今回はそれらをVBAで行います。Webサービスでは、取得できるデータをXML形式で提供しているものもありますので、似たようなケースでは同様の手順でのデータ取得などに応用できるでしょう。

題材としては、株式会社ヴァル研究所の提供する「駅すぱあとWebサービス」を用います。このサービスは、Webサイトやスマートフォンアプリで有名な「駅すぱあと 乗換案内」でできることをWebサービスを通して利用できるようにするものです。継続使用には有償の契約が必要ですが、90日間無償で全機能を使用できる無料評価版がありますので、今回はこれを使用して指定区間の運賃を自動取得するExcelワークシートを作成します（図1）。

• 図1：完成サンプル（ekispert_fare.xlsm）

完成サンプルのソースコード
https://github.com/wateryinhare62/mynavi_excelvba_webservice

なお、本連載では動作確認をWindows 10 Pro、Microsoft 365（Excel 16.0、VBA 7.1）で行っています。旧バージョンや単体のExcelで試す場合にはご注意下さい。

APIキーを取得する

最初に、「駅すぱあとWebサービス」を使用するためのAPIキーを取得します。まず、「駅すぱあとWebサービス」のトップページにアクセスします（図2）。

▼駅すぱあとWebサービス - システム連携サービス
https://ekiworld.net/service/sier/webservice/index.html

• 図2：駅すぱあとWebサービス - システム連携サービス

ページ下方にある「90日間無料評価版」を見つけてクリックします（図3）。

• 図3：「90日間無料評価版」をクリック

「駅すぱあとWebサービス スタンダードプラン 評価版お申し込み」のページに移りますので、すべての項目に入力します（図4）。

• 駅すぱあとWebサービス スタンダードプラン 評価版お申し込み駅すぱあとWebサービス スタンダードプラン 評価版お申し込み

・会社名（必須）：法人名を入力します。
・お名前（必須）：利用者名を入力します。
・メールアドレス（必須）：利用者のメールアドレスを入力します。APIキーはこのアドレスに届きますので間違いのないように入力します。
・ご利用ドメイン（必須）：利用者のドメイン名を入力します。アクセス時の認証がこのドメイン名を用いて行われますので、アクセスする組織のドメイン名を指定してください。
・ご利用目的（必須）：利用目的（APIの評価、テストなど）を入力します。
・お申し込みのきっかけ：自由に選択して下さい。
・ガイドライン（必須）：注意事項になりますので、一読した上で問題なければ［同意する］にチェックを入れて下さい。
・個人情報の取り扱い（必須）：個人情報保護法に基づく注意事項です。こちらも一読した上で問題なければ［同意する］にチェックを入れて下さい。

すべて入力した上で［送信する］をクリックすると、送信内容の確認ページが表示されますので、問題なければ［この内容で送信する］をクリックします（図5）。［戻る］をクリックして、内容を修正することもできます。

• 図5：確認画面

このあと、送信したことを示す画面となりますので（図6）、先ほど指定したメールアドレスにAPIキーについての連絡が入るまで待ちます。送信した旨の自動返信メールがきちんと届いているか確認しましょう。

• 図6：送信終了画面

筆者の場合、当日中にAPIキーを知らせるメールが送られてきました。APIキーはサービスを利用する上で重要ですから、外部に漏えいしないように注意しましょう。APIキーが入手できれば、ひとまず「駅すぱあとWebサービス スタンダードプラン 評価版」の利用を開始する準備ができたことになります。

APIの呼び出しをテストする

APIキーを取得したら、APIの呼び出しをテストしてみましょう。WebブラウザのURL欄に、以下のURLを入力します。「APIキー」の箇所は、各自が入手したAPIキーで置き換えて下さい。

https://api.ekispert.jp/v1/xml/dataversion?key=APIキー

たとえばGoogle Chromeの場合、図6のように表示されれば、APIキーは有効で、APIの呼び出しもうまくいっていることがわかります。

• 図6：API呼び出し成功

この回はXMLデータを扱うので、URLはXMLを取得するようになっています（URL中の「xml」の箇所）。「駅すぱあとWebサービス」ではXML形式のほかにJSON形式でもデータを取得できるようになっており、その場合にはURLの［xml」を「json」に変更します。 なお、「駅すぱあと Webサービス」のAPIについての詳細は、下記を参照してください。本記事では、APIについては最小限の解説のみ行います。

駅すぱあとWebサービス Documents 駅データ・路線検索のWebAPI https://docs.ekispert.com/v1/

ワークシートの準備

APIが使えることを確認できたら、計算開始のボタン、運賃を取得したい区間のリストなど、ワークシートを準備します。

取得開始のボタンを配置する

ワークシートは、「空白のブック」で始めます。このワークシートの上部に、運賃取得を開始するボタンを配置します。［開発］タブをクリックし、［コントロール］グループの［挿入］ボタンをクリックすると、コントロール一覧がメニューとして表示されます（［開発］タブが表示されていない場合は連載第1回を参照）。ここから、「フォームコントロール」カテゴリにある［ボタン（フォームコントロール）］をクリックします。マウスカーソルの形状が「＋」に変わって、好きな位置に好きな大きさのボタンを配置できるようになります。

マウスボタンのリリースと同時に「マクロの登録」ダイアログが開きますので、［新規作成］ボタンをクリックして、ボタンクリック時に呼び出されるイベントハンドラを作成します（図7）。

• 図7：「マクロの登録」ダイアログ

イベントハンドラ名の初期値は「ボタン1_Click」ですが、［マクロ名］欄で名称を変更することもできます。Visual Basic Editorが起動して、イベントハンドラが作成されたことを確認できます（図8）。

• 図8：イベントハンドラが作成された

ワークシート画面に戻ると、ボタンも作成されていることが確認できます（図9）。

• 図9：計算開始ボタンが配置された

ここで、ワークシートを保存しておきます。ワークシート名は何でもよいですが、形式を「Excel マクロ有効ブック (*.xlsm)」にしてください。マクロを有効にしておかないと、VBAのスクリプトを実行できないからです。

運賃取得区間のリストを準備する

次に、運賃取得区間のリストをワークシートに作成します。リストは単純で、出発駅、到着駅を記載するだけです。記載した分だけ繰り返し、片道の運賃と往復の運賃をAPIを通じて取得し、ワークシートに反映させます。

なお、リストは出発駅、到着駅、片道運賃、往復運賃の4列がメインですが、デバッグ用に出発駅コード、到着駅コードを埋め込む列を設けており、計6列のリストになっています。開発中はすべての列を表示させておき、開発が終了したらコードの入る列は隠してしまうことにしまうことにして、ここでは列の幅を調整するだけにします（図10）。

• 図10：運賃取得区間のリスト

駅コードを取得する

ここからスクリプトを書いていきますが、まずは駅コードを取得するところからやっていきます。なぜなら、経路や運賃を取得する場合には、駅は名前ではなくコードで指定するからです。出発駅、到着駅のそれぞれについて駅コードを取得し、駅名の隣の列にセットしていきます。

Microsoft XML v6.0を準備する

VBAでXMLデータを使いますので、そのために必要なライブラリへの参照を有効にしておきます。Visual Basic Editorにて、［ツール］メニューの［参照設定］から「参照設定 - VBA Project」を開きます。［参照可能なライブラリ ファイル］から「Microsoft XML v6.0」を見つけて、チェックが入っていなければチェックを入れます。そして［OK］ボタンをクリックすれば終了です（図11）。

• 図11：「Microsoft XML v6.0」を有効化

Webサービスにアクセスする

今回は、駅コードを取得するだけではなく、それをもとに運賃を取得することも行います。取得できる情報はまだまだあるので、拡張性を考慮してWebサービスへのアクセスは共通化しましょう。まずは、そのスクリプトを書いていきます。ボタンの配置でスクリプトを関連付けた時点で、すでに標準モジュールのModule1が作成されていますので、ここにスクリプトを追加していきます。Webサービスにアクセスするスクリプトは、KickWebServiceという関数にします。それをリスト1に示します。「End Sub」の下に追加します。

［リスト1］KickWebService関数

Private Function KickWebService(ByVal Api As String, ByVal Param As String) As String
    Dim ApiKey As String, Url As String
    ApiKey = "test_xxxxxxxxxxx" (1)
    Url = "https://api.ekispert.jp/v1/xml/" & Api & "?key=" & ApiKey & Param
    Dim http As Object  (2)
    Set http = CreateObject("MSXML2.XMLHTTP")
    With http
        .Open "GET", Url, False
        .send
        KickWebService = .responseText  (3)
    End With
End Function

引数に、「駅すぱあと Webサービス」で使用できるAPIの名称（URLパスの一部）、そしてクエリ引数（「&name=value…」の形式）を受け取って、結果としてXML文字列を返す関数です。ApiKey変数の値は、各自が入手したAPIキーで置き換えて下さい（1）。 VBAでは、WebサービスなどHTTPでアクセスする必要のあるとき、MSXML2.XMLHTTPというモジュールのオブジェクトを使用します。オブジェクト作成後、HTTPメソッドとURLを指定してOpenメソッドを呼び出し、必要なリクエストパラメータを設定した後（ここでは不要なのでやっていない）、sendメソッドで実際の呼び出しを行います（2)）。 呼び出した結果はResponseTextプロパティで取得できます。これが空なら何らかのエラーが発生していますが、これをそのまま関数の戻り値として返します（3）。

Webサービス呼び出しをテストする

KickWebService関数が問題なく動くか確認するためのスクリプトを書きます。KickWebService関数の下に、リスト2のTestWebServiceプロシージャを追加します。

［リスト2］テストのためのプロシージャ

Sub TestWebService()
    Dim Result As String
    Result = KickWebService("station/light", "&name=横浜")
    If Result = "" Then
        MsgBox "取得失敗!"
    Else
        MsgBox Result
    End If
End Sub

そして、ボタン1_Clickの中に、リスト3の1行を追加します。

［リスト3］ボタン1_ClickにTestWebService呼び出しを追加

Sub ボタン1_Click()
    TestWebService     '追加
End Sub

ワークシートに戻ってボタンをクリックして、図12のように表示されれば関数はうまく動作しています。「テスト失敗!」でない場合でも、図12のように駅名などが混じった長いXML文字列でなくエラーとか含まれる場合は、呼び出し方に何らかの問題があります。スクリプトが正しく書けているか確認しましょう。

• 図12：KickWebService関数呼び出し成功

駅コード取得関数を書く

テストがうまくいったら、駅コードの取得スクリプトを書いていきましょう。先ほどのテストスクリプトでは横浜駅の「駅簡易情報」を取得していました。駅コードは、この駅簡易情報の中に入っていますが、XMLデータから探し出してあげる必要があります。リスト4のParseStationCode関数を追加します。

［リスト4］ParseStationCode関数

Private Function ParseStationCode(xmlDom As MSXML2.DOMDocument60) As Integer
    Dim ResultSet As MSXML2.IXMLDOMNode, Point As MSXML2.IXMLDOMNode, Station As MSXML2.IXMLDOMNode
    Set ResultSet = xmlDom.ChildNodes(1)    (1)
    Dim i As Integer, j As Integer, k As Integer, Code As Integer
    Code = 0
    For i = 0 To ResultSet.ChildNodes.Length - 1
        Set Point = ResultSet.ChildNodes.Item(i)    (2)
        For j = 0 To Point.ChildNodes.Length - 1
            If Point.ChildNodes.Item(j).nodeName = "Station" Then   (3)
                Set Station = Point.ChildNodes.Item(j)
                For k = 0 To Station.ChildNodes.Length - 1
                    If Station.ChildNodes(k).nodeName = "Type" Then (4)
                        If Station.ChildNodes(k).nodeTypedValue = "train" Then
                            Code = Val(Station.Attributes(0).Text)  (5)
                            Exit For
                        End If
                    End If
                Next k
            End If
        Next j
    Next i
    ParseStationCode = Code
End Function

このスクリプトを理解するには、駅簡易情報のXMLデータの形式を知っておく必要があります。図13のようになっています。

• 図13：駅簡易情報のデータ形式

ルート要素であるResultSetの中に、拠点の情報であるPoint要素が複数個含まれるという構造です。Point要素の下には、駅の名前と種類を持つStation要素、そして補助的な情報として県名を持つPrefecture要素が含まれます。これを踏まえてリスト3を見てみると、最初に(1)でDOMツリーからルート要素ResultSetをChildNodesメソッドで取り出し、(2)でResultSetの下のPoint要素をその個数回処理し、さらに(3)でPoint要素の下のStation要素からType要素が「train」すなわち鉄道駅であるものを見つけて（4）、その駅コードを取得しています（5）。駅コードは、要素の値ではなくStation要素の属性に含まれるため、Attributesプロパティから取得しています。XMLのツリーの各要素に的確にアクセスするのは面倒なのですが、図があると迷いにくくなるでしょう。

取得＆書き込みスクリプトを書く

駅コードを取得できる下準備ができましたので、ワークシートから駅名を取り出し、取得した駅コードをワークシートに書き込むスクリプトをボタン1_Clickの中に追加します（リスト5）。テストに使用したTestWebServiceプロシージャの呼び出しは不要なので、'でコメントアウトするか削除してください。

［リスト5］駅コード取得＆書き込み

Dim Row As Integer
Dim xmlDom As MSXML2.DOMDocument60
Set xmlDom = New MSXML2.DOMDocument60
Row = 4
While Cells(Row, 1) <> ""   (1)
    Dim StartCode As Integer, DestCode As Integer
    xmlDom.LoadXML (KickWebService("station/light", "&name=" + Cells(Row, 1)))  （2）
    StartCode = ParseStationCode(xmlDom)
    Cells(Row, 2) = StartCode
    xmlDom.LoadXML (KickWebService("station/light", "&name=" + Cells(Row, 3)))  （2）
    DestCode = ParseStationCode(xmlDom)
    Cells(Row, 4) = DestCode
    Row = Row + 1
Wend

この流れは非常に単純です。ワークシートを参照し、この場合は4行目から出発駅のある列を順番に読み取り、空になったらそこで処理を終えます（1）。空でない場合には、出発駅、到着駅の順にWebサービスを呼び出し、その結果から駅コードを取得してワークシートに書き込みます（2）。ここまでの状態でボタンクリックすると、5個のリストに対して駅コードを取得して、図14のように書き込まれます。ここまでで、必要な情報は揃いました。

• 図14：駅コードが取得できた

経路と運賃を取得する

駅コードが取得できましたので、それをもとに経路と運賃を取得します。

経路取得プロシージャを書く

経路は今回の目的ではないのですが、経路の情報に運賃が含まれるので、経路を取得するという形になります。経路も、Webサービスで取得するのは変わりないので、考え方はこれまでと同じです。新たにParseCourseFareというプロシージャを作ります（リスト6）。

［リスト6］ParseCourseFareプロシージャ

Private Sub ParseCourseFare(xmlDom As MSXML2.DOMDocument60, ByRef Oneway As Integer, ByRef Round As Integer)    (1)
    Dim ResultSet As MSXML2.IXMLDOMNode, Course As MSXML2.IXMLDOMNode, Price As MSXML2.IXMLDOMNode
    Set ResultSet = xmlDom.ChildNodes(1)
    Dim i As Integer, j As Integer, k As Integer
    For i = 0 To ResultSet.ChildNodes.Length - 1
        Set Course = ResultSet.ChildNodes.Item(i)
        For j = 0 To Course.ChildNodes.Length - 1
            If Course.ChildNodes.Item(j).nodeName = "Price" Then
                Set Price = Course.ChildNodes.Item(j)   (2)
                For k = 0 To Price.Attributes.Length - 1
                    If Price.Attributes.Item(k).nodeName = "kind" Then  (3)
                        If Price.Attributes.Item(k).nodeTypedValue = "FareSummary" Then
                            Oneway = Val(Price.ChildNodes.Item(0).nodeTypedValue)   (4)
                            Round = Val(Price.ChildNodes.Item(1).nodeTypedValue)
                            Exit For
                        End If
                    End If
                Next k
            End If
        Next j
    Next i
End Sub

やや長いですが、やっていることはParseStationCode関数とほぼ同じです。片道運賃と往復運賃という2つの値が欲しいので、関数ではなく参照渡しによるプロシージャとしています（1）。これを理解するために、経路探索のXMLデータの形式を知っておく必要があります。図15のようになっています。

• 図15：経路探索のデータ形式

重要なのはPrice要素です（2）。この要素の属性kindがFareSummaryであるものを探しだし（3）、その下にあるOneway要素とRound要素から片道運賃と往復運賃を取得します（4）。

金額をセットするスクリプトを書く

完成は目前です。あとは、経路探索のAPIの呼び出しと、ParseCourseFare関数を呼び出してワークシートに書き込むスクリプトをボタン1_Clickに追加します。リスト7がその内容です。

［リスト7］運賃書き込み

Dim Oneway As Integer, Round As Integer
Oneway = 0
Round = 0
xmlDom.LoadXML (KickWebService("search/course", _
    "&from=" & CStr(StartCode) & "&to=" & CStr(DestCode)))
Call ParseCourseFare(xmlDom, Oneway, Round)
Cells(Row, 5) = Oneway
Cells(Row, 6) = Round

これで、冒頭の図1のような状態になれば完成です。駅名をいろいろ変えて試してみて下さい。

まとめ

今回は、「駅すぱあと Webサービス」の無料試用版を使って、Webサービスの呼び出しやXMLの解析の手順を紹介しました。ほとんどがVBAによるスクリプトでしたので難しかったかも知れませんが、ワークシートへのアクセスもデータの処理も自由なので、応用が利くと思います。次回は、JSONのWebサービスの事例として、気象庁の天気情報を取り上げたいと思います。

WINGSプロジェクト 山内直著/山田祥寛監修
＜WINGSプロジェクトについて＞テクニカル執筆プロジェクト(代表山田祥寛)。海外記事の翻訳から、主にWeb開発分野の書籍・雑誌/Web記事の執筆、講演等を幅広く手がける。一緒に執筆をできる有志を募集中