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

Method 1:

This method is the same as the manual method explained in the article Excel, Get KML File Properties, Google Earth API, only this time we will automate the process using VBA. The code below gets the Longitude, Latitude, Altitude, … properties from the KML file located in the path “D:StuffBusinessTempLocation.kml”:

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

ActiveWorkbook.XmlImport URL:="D:StuffBusinessTempLocation.kml", _
    ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")
dblLongitude = Range("M2")
dblLatitude = Range("N2")
dblAltitude = Range("O2")
dblHeading = Range("P2")
dblTilt = Range("Q2")
dblRange = Range("R2")
End Sub


KML Files are basically XML Files. The code imports the KML as an XML File:

It then stores the values it needs in the variables dblLongitude, dblLatitude, …

Method 2:

This 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 main2()
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 KML Properties.xlsm