以前、「Google MapのKMLファイルをAccessにインポートする」という記事を書きました。
この記事では、KMLファイルを手動でアクセスのテーブルにインポートしています。
あの後色々調べて、ようやくテーブルに取り込むことなくKMLファイルの読み込みの方法が分かりましたので、自分への備忘録を兼ねて記事にします。
KMLファイルを読み込む
JR須磨駅から神戸須磨シーワールドへのルート
KMLファイル
上記地図のルートは、GoogleMAPから出力した次のKMLファイルを読み込んでいます。
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
<name>KML変換TEST</name>
<description/>
<Style id="icon-1899-DB4436-nodesc-normal">
<IconStyle>
<color>ff3644db</color>
<scale>1</scale>
<Icon>
<href>https://www.gstatic.com/mapspro/images/stock/503-wht-blank_maps.png</href>
</Icon>
<hotSpot x="32" xunits="pixels" y="64" yunits="insetPixels"/>
</IconStyle>
<LabelStyle>
<scale>0</scale>
</LabelStyle>
<BalloonStyle>
<text><![CDATA[<h3>$[name]</h3>]]></text>
</BalloonStyle>
</Style>
<Style id="icon-1899-DB4436-nodesc-highlight">
<IconStyle>
<color>ff3644db</color>
<scale>1</scale>
<Icon>
<href>https://www.gstatic.com/mapspro/images/stock/503-wht-blank_maps.png</href>
</Icon>
<hotSpot x="32" xunits="pixels" y="64" yunits="insetPixels"/>
</IconStyle>
<LabelStyle>
<scale>1</scale>
</LabelStyle>
<BalloonStyle>
<text><![CDATA[<h3>$[name]</h3>]]></text>
</BalloonStyle>
</Style>
<StyleMap id="icon-1899-DB4436-nodesc">
<Pair>
<key>normal</key>
<styleUrl>#icon-1899-DB4436-nodesc-normal</styleUrl>
</Pair>
<Pair>
<key>highlight</key>
<styleUrl>#icon-1899-DB4436-nodesc-highlight</styleUrl>
</Pair>
</StyleMap>
<Style id="line-1267FF-5000-nodesc-normal">
<LineStyle>
<color>ffff6712</color>
<width>5</width>
</LineStyle>
<BalloonStyle>
<text><![CDATA[<h3>$[name]</h3>]]></text>
</BalloonStyle>
</Style>
<Style id="line-1267FF-5000-nodesc-highlight">
<LineStyle>
<color>ffff6712</color>
<width>7.5</width>
</LineStyle>
<BalloonStyle>
<text><![CDATA[<h3>$[name]</h3>]]></text>
</BalloonStyle>
</Style>
<StyleMap id="line-1267FF-5000-nodesc">
<Pair>
<key>normal</key>
<styleUrl>#line-1267FF-5000-nodesc-normal</styleUrl>
</Pair>
<Pair>
<key>highlight</key>
<styleUrl>#line-1267FF-5000-nodesc-highlight</styleUrl>
</Pair>
</StyleMap>
<Folder>
<name>兵庫県神戸市須磨区須磨浦通４丁目２ 須磨駅 から 日本、〒654-0047 兵庫県神戸市須磨区磯馴町６丁目１−１１ へのルート</name>
<Placemark>
<name>兵庫県神戸市須磨区須磨浦通４丁目２ 須磨駅 から 日本、〒654-0047 兵庫県神戸市須磨区磯馴町６丁目１−１１ へのルート</name>
<styleUrl>#line-1267FF-5000-nodesc</styleUrl>
<LineString>
<tessellate>1</tessellate>
<coordinates>
135.11275,34.64251,0
135.11274,34.64257,0
135.11274,34.64258,0
135.11275,34.64258,0
135.11276,34.64258,0
135.11277,34.64259,0
135.1128,34.6426,0
135.11312,34.64265,0
135.11319,34.64266,0
135.11323,34.64265,0
135.11326,34.64265,0
135.11328,34.64265,0
135.11331,34.64265,0
135.11336,34.64265,0
135.11351,34.64267,0
135.11352,34.64267,0
135.11353,34.64267,0
135.11354,34.64268,0
135.11354,34.64269,0
135.11355,34.64271,0
135.11356,34.64273,0
135.11356,34.64275,0
135.11356,34.64276,0
135.11357,34.64276,0
135.11358,34.64277,0
135.11359,34.64277,0
135.1136,34.64277,0
135.11361,34.64277,0
135.11381,34.64277,0
135.11394,34.64277,0
135.11422,34.64276,0
135.11432,34.64277,0
135.11453,34.6428,0
135.11487,34.64284,0
135.11489,34.64284,0
135.1149,34.64284,0
135.11491,34.64284,0
135.11492,34.64283,0
135.11493,34.64282,0
135.11494,34.64282,0
135.11495,34.64281,0
135.11496,34.64281,0
135.11497,34.6428,0
135.11498,34.6428,0
135.11499,34.6428,0
135.11507,34.6428,0
135.11522,34.64282,0
135.11548,34.64287,0
135.11536,34.64332,0
135.11535,34.64334,0
135.11525,34.64363,0
135.11537,34.64364,0
135.11602,34.64368,0
135.11636,34.6437,0
135.11664,34.64372,0
135.11705,34.64375,0
135.11756,34.64378,0
135.11762,34.64378,0
135.11828,34.64383,0
135.11863,34.64385,0
135.11874,34.64389,0
135.11876,34.6439,0
135.11886,34.6439,0
135.11885,34.64396,0
135.11885,34.64397,0
135.1191,34.64399,0
135.11971,34.64404,0
135.11991,34.64405,0
135.1203,34.64408,0
135.12044,34.64408,0
135.12047,34.64408,0
135.12049,34.64408,0
135.1205,34.64409,0
135.12074,34.64419,0
135.12085,34.64423,0
135.12096,34.64428,0
135.12112,34.64434,0
135.12146,34.64448,0
135.1215,34.6445,0
135.12155,34.64453,0
135.1216,34.64456,0
135.12165,34.64459,0
135.12176,34.64466,0
135.12183,34.6447,0
135.12195,34.64475,0
135.12206,34.64481,0
135.12225,34.64491,0
135.12233,34.64494,0
135.12239,34.64498,0
135.12251,34.64505,0
135.12267,34.64515,0
135.12367,34.64575,0
135.1238,34.64583,0
135.12397,34.64593,0
135.12439,34.64619,0
135.12457,34.64629,0
135.12463,34.64623,0
135.12471,34.64615,0
135.12478,34.64608,0
135.12482,34.64604,0
135.12485,34.64601,0
135.12492,34.64594,0
135.12499,34.64587,0
135.12512,34.64573,0
135.12521,34.64563,0
135.12526,34.64559,0
135.1255,34.64534,0
135.1258,34.64503,0
135.12589,34.64494,0
135.12623,34.64458,0
135.12619,34.64452,0
135.12617,34.6445,0
</coordinates>
</LineString>
</Placemark>
<Placemark>
<name>兵庫県神戸市須磨区須磨浦通４丁目２ 須磨駅</name>
<styleUrl>#icon-1899-DB4436-nodesc</styleUrl>
<Point>
<coordinates>
135.112751,34.6425081,0
</coordinates>
</Point>
</Placemark>
<Placemark>
<name>日本、〒654-0047 兵庫県神戸市須磨区磯馴町６丁目１−１１</name>
<styleUrl>#icon-1899-DB4436-nodesc</styleUrl>
<Point>
<coordinates>
135.1261725,34.6445007,0
</coordinates>
</Point>
</Placemark>
</Folder>
</Document>
</kml>
VBAコード
Sub Read_KML_Coordinates()
Dim strTgTFldNM As String 'ターゲットフォルダを格納する
Dim strTgTFleNM As String 'ターゲットファイルを格納する
'アクセスのあるフォルダのパスを取得
strTgTFldNM = Application.CurrentProject.Path
'読み込むKMLファイル名を設定
strTgTFleNM = "須磨駅から神戸須磨シーワールドまでのルート.kml"
Dim Objkml As Object 'xml操作用オブジェクト
'要素格納用変数
Dim youso_LineString As Object
Dim youso_coordinates As Object
Set Objkml = CreateObject("MSXML2.DOMDocument")
With Objkml
'KMLファイル読み込み
.Load strTgTFldNM & "\" & strTgTFleNM
Set youso_LineString = .getElementsByTagName("LineString").Item(0)
For Each youso_coordinates In youso_LineString.getElementsByTagName("coordinates")
Debug.Print Replace(youso_coordinates.Text, " ", "")
Next youso_coordinates
End With
End Sub
アクセスが置いてあるフォルダ内に、読み込ませるKMLを置き、10行目のコードを、読み込ませたいKMLファイル名に置き換えてください。
このVBAコードを実行すると、イミディエイトウィンドウに、次のように出力されます。
イミディエイトウィンドウに出力された結果
135.11275,34.64251,0 135.11274,34.64257,0 135.11274,34.64258,0 135.11275,34.64258,0 135.11276,34.64258,0 135.11277,34.64259,0 135.1128,34.6426,0 135.11312,34.64265,0 135.11319,34.64266,0 135.11323,34.64265,0 135.11326,34.64265,0 135.11328,34.64265,0 135.11331,34.64265,0 135.11336,34.64265,0 135.11351,34.64267,0 135.11352,34.64267,0 135.11353,34.64267,0 135.11354,34.64268,0 135.11354,34.64269,0 135.11355,34.64271,0 135.11356,34.64273,0 135.11356,34.64275,0 135.11356,34.64276,0 135.11357,34.64276,0 135.11358,34.64277,0 135.11359,34.64277,0 135.1136,34.64277,0 135.11361,34.64277,0 135.11381,34.64277,0 135.11394,34.64277,0 135.11422,34.64276,0 135.11432,34.64277,0 135.11453,34.6428,0 135.11487,34.64284,0 135.11489,34.64284,0 135.1149,34.64284,0 135.11491,34.64284,0 135.11492,34.64283,0 135.11493,34.64282,0 135.11494,34.64282,0 135.11495,34.64281,0 135.11496,34.64281,0 135.11497,34.6428,0 135.11498,34.6428,0 135.11499,34.6428,0 135.11507,34.6428,0 135.11522,34.64282,0 135.11548,34.64287,0 135.11536,34.64332,0 135.11535,34.64334,0 135.11525,34.64363,0 135.11537,34.64364,0 135.11602,34.64368,0 135.11636,34.6437,0 135.11664,34.64372,0 135.11705,34.64375,0 135.11756,34.64378,0 135.11762,34.64378,0 135.11828,34.64383,0 135.11863,34.64385,0 135.11874,34.64389,0 135.11876,34.6439,0 135.11886,34.6439,0 135.11885,34.64396,0 135.11885,34.64397,0 135.1191,34.64399,0 135.11971,34.64404,0 135.11991,34.64405,0 135.1203,34.64408,0 135.12044,34.64408,0 135.12047,34.64408,0 135.12049,34.64408,0 135.1205,34.64409,0 135.12074,34.64419,0 135.12085,34.64423,0 135.12096,34.64428,0 135.12112,34.64434,0 135.12146,34.64448,0 135.1215,34.6445,0 135.12155,34.64453,0 135.1216,34.64456,0 135.12165,34.64459,0 135.12176,34.64466,0 135.12183,34.6447,0 135.12195,34.64475,0 135.12206,34.64481,0 135.12225,34.64491,0 135.12233,34.64494,0 135.12239,34.64498,0 135.12251,34.64505,0 135.12267,34.64515,0 135.12367,34.64575,0 135.1238,34.64583,0 135.12397,34.64593,0 135.12439,34.64619,0 135.12457,34.64629,0 135.12463,34.64623,0 135.12471,34.64615,0 135.12478,34.64608,0 135.12482,34.64604,0 135.12485,34.64601,0 135.12492,34.64594,0 135.12499,34.64587,0 135.12512,34.64573,0 135.12521,34.64563,0 135.12526,34.64559,0 135.1255,34.64534,0 135.1258,34.64503,0 135.12589,34.64494,0 135.12623,34.64458,0 135.12619,34.64452,0 135.12617,34.6445,0
次回、このVBAコードを使って、CSVなど他のファイルに変換してみたいと思います。
コメントをどうぞ！