Windows 10 + Excel2016_x86 VBA で XML の基本的なことを少し触ってみる

目的

Excelから緯度、経度が取得できるのかを調べる機会があってついでに試したことをメモっておく
Excel の WEBSERVICE 関数でジオコーディングを参考に取得したXMLファイルを使用する

サンプルコード

緯度経度の取得

以下のコードで取得する

    Dim url     As String
    Dim xml     As String

    url = "http://geocode.csis.u-tokyo.ac.jp/cgi-bin/simple_geocode.cgi?charset=UTF8&addr="
    url = url & WorksheetFunction.EncodeURL("池尻4-35-25")
    xml = WorksheetFunction.WebService(url)

    Debug.Print xml ' をファイルに保存する
    '
    ' 取得したデータをそのまま使用する場合は以下で読み込んで流す
    '
    xmlObj.LoadXML xml
    Debug.Print xmlObj.xml

取得したXMLデータ

<?xml version="1.0" encoding="UTF-8" ?>
<results>
    <query>池尻4-35-25 </query>
    <geodetic>wgs1984</geodetic>
    <iConf>4</iConf>
    <converted>池尻4-35-</converted>
    <candidate>
        <address>東京都/世田谷区/池尻/四丁目/３５番</address>
        <longitude>139.673965</longitude>
        <latitude>35.654259</latitude>
        <iLvl>7</iLvl>
    </candidate>
    <candidate>
        <address>兵庫県/伊丹市/池尻/四丁目/３５番地</address>
        <longitude>135.381805</longitude>
        <latitude>34.784988</latitude>
        <iLvl>7</iLvl>
    </candidate>
</results>

VBAのサンプルコード

基本的な機能を確認してみる
詳細はコメント参照

    Dim xmlObj  As MSXML2.DOMDocument60
    Dim nlst    As MSXML2.IXMLDOMNodeList
    Dim ndlt    As MSXML2.IXMLDOMNodeList
    Dim node    As MSXML2.IXMLDOMNode
    Dim elem    As MSXML2.IXMLDOMElement

    Set xmlObj = New MSXML2.DOMDocument60
    xmlObj.async = False
    xmlObj.validateOnParse = False

    xmlObj.Load ("path\to\addr2.xml")     ' 池尻4-35-25で取得
    '
    ' 読み込んだXMLファイルをそのまま出力する
    '
    Debug.Print xmlObj.xml
    '
    ' 以下は同じ結果が返ってくる
    ' 池尻4-35-25
    '
    Debug.Print WorksheetFunction.FilterXML(xmlObj.xml, "//results//query")
    Debug.Print WorksheetFunction.FilterXML(xmlObj.xml, "//query")
    '
    ' 今回のデータは <candidate>～</candidate>
    ' が2個あるため nlst.Length=2 となる
    '
    Set nlst = xmlObj.SelectNodes("//candidate")
    Debug.Print nlst.Length
    '
    ' 今回のデータは <candidate>～</candidate>
    ' が2個あるためエラーになる
    '
    Debug.Print WorksheetFunction.FilterXML(xmlObj.xml, "//latitude")
    '
    ' SelectSingleNode は1個めの <candidate>～</candidate> を取得する
    '
    Set node = xmlObj.SelectSingleNode("//candidate")
    Debug.Print node.ChildNodes(0).nodeName     ' address
    Debug.Print node.ChildNodes(0).Text         ' 東京都/世田谷区/池尻/四丁目/３５番
    Debug.Print node.ChildNodes(1).nodeName     ' longitude
    Debug.Print node.ChildNodes(1).Text         ' 139.673965
    Debug.Print node.ChildNodes(2).nodeName     ' latitude
    Debug.Print node.ChildNodes(2).Text         ' 35.654259
    Debug.Print node.ChildNodes(3).nodeName     ' iLvl
    Debug.Print node.ChildNodes(3).Text         ' 7
    '
    ' 返却値は .nodeTypeString=element だが
    ' Set elem = xmlObj.getElementsByTagName("candidate")
    ' はエラーになる?
    '
    Set nlst = xmlObj.getElementsByTagName("candidate")
    Debug.Print nlst.Length                             ' 2
    Debug.Print nlst(0).ChildNodes(0).nodeTypeString    ' element
    Debug.Print nlst(0).ChildNodes(0).nodeTypedValue    ' 東京都/世田谷区/池尻/四丁目/３５番
    Debug.Print nlst(0).ChildNodes(0).NodeType          ' 1
    Debug.Print nlst(0).ChildNodes(0).NodeValue         ' Null
    Debug.Print nlst(0).ChildNodes(0).nodeName          ' address
    Debug.Print nlst(0).ChildNodes(0).Text              ' 東京都/世田谷区/池尻/四丁目/３５番
    '
    ' ループで処理する
    '
    Set nlst = xmlObj.SelectNodes("//results//candidate")

    For Each node In nlst
        Debug.Print node.ChildNodes(0).nodeName
        Debug.Print node.ChildNodes(0).Text
        Debug.Print node.ChildNodes(1).nodeName
        Debug.Print node.ChildNodes(1).Text
        Debug.Print node.ChildNodes(2).nodeName
        Debug.Print node.ChildNodes(2).Text
        Debug.Print node.ChildNodes(3).nodeName
        Debug.Print node.ChildNodes(3).Text
    Next
    '
    ' BaseNameの表示
    '
    Set node = xmlObj.SelectSingleNode("//results//candidate")
    Debug.Print node.BaseName                   ' candidate
    Debug.Print node.ChildNodes(0).BaseName     ' address
    Debug.Print node.ChildNodes(1).BaseName     ' longitude
    Debug.Print node.ChildNodes(2).BaseName     ' latitude
    Debug.Print node.ChildNodes(3).BaseName     ' iLvl

    Set nlst = xmlObj.SelectNodes("//results//candidate")
    Debug.Print nlst(0).ChildNodes(0).BaseName  ' address
    Debug.Print nlst(0).ChildNodes(1).BaseName  ' longitude
    Debug.Print nlst(0).ChildNodes(2).BaseName  ' latitude
    Debug.Print nlst(0).ChildNodes(3).BaseName  ' latitude

参考にしたのは以下のサイト

Excel の WEBSERVICE 関数でジオコーディング
XMLをエクセルに取り込むマクロ
XPathの動作確認（Excel VBA編）
VBA で XML 読み込み (だれ需要やねん・・・)
経度,緯度の変化から距離を計算する。