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.
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, …
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:
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: