This article explains how to get data from a KML file using VBA for Access. KML files are basically XML files, therefor the process is basically the same as getting data from any other XML File.

The method uses the MSXML object. I’ve explained how you can get data from an XML file on the web using the MSXML object in the article below:

• Excel VBA, Get Data From Web Using MSXML

The same concept can be used to get data from the KML File. The tricky part is figuring out which child node contains the data we are after. Fortunately I’ve already done that for you in the code below.

Note: In order for the code to work you will need to add reference to the Microsoft XML Library (Microsoft XML V6.0 was installed on my system):

Sub main()
Dim dblLongitude As Double
Dim dblLatitude As Double
Dim dblAltitude As Double
Dim dblHeading As Double
Dim dblTilt As Double
Dim dblrange As Double
Dim objXML As MSXML2.DOMDocument60
Dim objNode As MSXML2.IXMLDOMNode

Set objXML = New MSXML2.DOMDocument60
'load the KML File
objXML.Load ("D:StuffBusinessTempLocation.KML")
'get the node with the required data
Set objNode = objXML.ChildNodes.Item(1).ChildNodes.Item( _
0).ChildNodes.Item(4).ChildNodes.Item(1)
'get the required data
dblLongitude = objNode.ChildNodes.Item(0).Text
dblLatitude = objNode.ChildNodes.Item(1).Text
dblAltitude = objNode.ChildNodes.Item(2).Text
dblHeading = objNode.ChildNodes.Item(3).Text
dblTilt = objNode.ChildNodes.Item(4).Text
dblrange = objNode.ChildNodes.Item(5).Text
End Sub


You can download the sample file used in this article from the link below:

• Get Data From KML File.Zip