Reading XML Files in VBA
In Creating State Maps… I copied some data out of an XML file and graphed it into a state map. I wanted to try to read the XML file directly in VBA because I’ve never done it before. First, I set a reference to Microsoft XML v6. Six was the newest version I had, but I don’t know what the differences are.
Next I started inspecting the objects via the Object Browser (F2). Definitely DOMDocument was the big one I would need and the Load method took a file name as an argument, so that looked promising. Here’s what I ended up with:
Dim xmlDom As MSXML2.DOMDocument
Dim xmlPlaceMark As MSXML2.IXMLDOMNode
Dim xmlPolygon As MSXML2.IXMLDOMNode
Dim xmlCoord As MSXML2.IXMLDOMNode
Dim sName As String
Dim vaSpace As Variant, vaComma As Variant
Dim i As Long, j As Long
Set xmlDom = New MSXML2.DOMDocument
xmlDom.Load "C:\Downloads\overlay_1198.kml"
For i = 0 To xmlDom.childNodes(1).childNodes(0).childNodes.Length - 1
If xmlDom.childNodes(1).childNodes(0).childNodes.Item(i).nodeName = "Placemark" Then
Set xmlPlaceMark = xmlDom.childNodes(1).childNodes(0).childNodes.Item(i)
Set xmlPolygon = xmlPlaceMark.childNodes(2).childNodes(0)
Set xmlCoord = xmlPolygon.childNodes(0).childNodes(0).childNodes(0)
sName = xmlPlaceMark.childNodes(1).childNodes(5).nodeTypedValue
'Stop
With Sheet4.Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0)
.Value = sName
vaSpace = Split(xmlCoord.childNodes(0).Text, " ")
For j = LBound(vaSpace) To UBound(vaSpace)
vaComma = Split(vaSpace(j), ",")
.Offset(0, 1).Value = vaComma(0)
.Offset(0, 2).Value = vaComma(1)
Next j
End With
End If
Next i
End Sub
It almost gets the job done, but not quite. I use a lot of XMLNode variables so I can keep track of where I am, but the code is hardly self documenting. The childNodes property only takes an integer as an argument, so I can’t call out the nodes name. This is only slightly better than reading it in as a text file. To be fair, I’ve only started messing with this object library, so maybe I can define a schema that makes things easier. I just don’t know.
Based on this initial chart, it looks like a got the coordinates parsed correctly.
There are few dots over at x=0, y=50. Alaska has a few islands that cross the date line and have x coordinates around 179. Rather than try to convert them, I just converted the x’s to zero. Ultimately I deleted those data points. Also, I should have made each polygon its own series. I don’t know the limit on chart series, but I’ll be I’d have reached it. Having only one series doesn’t allow me to connect the dots.
I also added a trend line (in pink). It appears my country is in decline.
Dick,
I’d like to look at the XML file, but you can each node has two methods that I use a lot, SelectNode and SelectSingleNode, you pass an XPATH to each one, and that makes it much easier to find nodes in the DOM tree. For example
Dim nodes as IXMLDOMNodeList
Set nodes = xmlDoc.documentElement.selectNodes(”//Placemark”)
would return a collection of all the nodes names “Placemark” that are descendants of the root node.
As far as I know Microsoft XML v6 gets installed with SQL Server Express 2005. I’ve gone as low as Microsoft XML v3 without seeing a negative impact, even when using XPath calls.
Chapter 12 of the Wrox Excel 2007 VBA book talks about XPath.
The downwards trendline is due to you forgetting to account for Galactic North. Just reverse the signs on the coordinates to remedy.
I handled the offshore islands with the New Zealand map just by plotting the three largest series, to account for our three largest Islands (the 2 largest of which are imaginatively named ‘North Island’ and ‘South Island’. For a great navigator, Captian Cook certainly had a lousy imagination. And he obviously knew nothing about Galactic North).
I also took out some sqiggly fiords and skinny harbours by identifying their coordinates when mousing over the map, and then hiding the series. Makes the map outline more uniformly thin. Bit of a bummer if you want to use the map to plot a sailing course into the fiords, mind.
Sorry, forgot to mention that I was plotting data pulled from the site that John Walkenbach mentioned in your previous post on this. That site sorts the data into discrete shapes - I think that’s what you describe as polygons??
Here’s an example, with the ‘1′ denoting that this is a discrete shape (in fact continuing the Captain Cook flavour above, it’s somewhere in the Cook Islands. I always wanted to visit, and now I kinda have):
1
-0.1573318E+03 -0.2015436E+02
-0.1573225E+03 -0.2017928E+02
-0.1573295E+03 -0.2018168E+02
-0.1573393E+03 -0.2017769E+02
-0.1573423E+03 -0.2017521E+02
-0.1573477E+03 -0.2017001E+02
-0.1573504E+03 -0.2015857E+02
-0.1573518E+03 -0.2014664E+02
-0.1573481E+03 -0.2013648E+02
-0.1573367E+03 -0.2013309E+02
-0.1573203E+03 -0.2013859E+02
-0.1573146E+03 -0.2014332E+02
-0.1573121E+03 -0.2015730E+02
-0.1573182E+03 -0.2017347E+02
-0.1573225E+03 -0.2017928E+02
END
You don’t need a trend line to tell you what’s happening to the USA!
Hi Dick,
Another powerful method is getElementsByTagName. It allows you to -well- get all elements by their tag name:
Dim nNode as MSXML2.IXMLDOMNode
Set nNodeList = oDOM.getElementsByTagName("Customer")
For Each nNode in nNodeList
'Do stuff here
Next
Dick,
As pointed out by others your use of XML has room for improvement. But on a site dedicated to Excel I’m amazed that you (and others) still write crappy code.
IMHO Variants are only used when there’s no alternative, code should run for non US separators and I never pump data to a sheet on a cell by cell basis. Following code does the same as yours, but is tidy. Example is latebound, so a reference to XML 3 or 6 is not needed.
Dim adList() As Double
Dim asText() As String
Dim sFile As String
Dim sXpath As String
Dim i As Long
Dim j As Long
sFile = "C:\Users\xxx\Documents\1198.kml"
'Reminder: XML is caseSensitive!
sXpath = "//Placemark/MultiGeometry/Point/coordinates"
With CreateObject("MSXML2.DOMDocument")
Debug.Assert Len(Dir(sFile)) 'Check the file exists
.Load sFile
Debug.Assert .parseError.errorCode = 0
With .documentElement.selectNodes(sXpath)
Debug.Assert .Length
ReDim adList(.Length - 1, 1)
For i = 0 To .Length - 1
asText = Split(.Item(i).Text, ",")
For j = 0 To 1
'Use Val not Cstr
adList(i, j) = Val(asText(j))
Next
Next
Range("A2").Resize(.Length, 2) = adList
End With
End With
End Sub
You’re surprised I write crappy code? You must be some kind of idealist.
I thought I had to use a Variant for Split. That’s a new one on me. As for writing to cells in a loop, I never do that when it counts - I swear.
keepITCool: on a site dedicated to teaching, sharing, and community; I’m surprised you write ‘crappy’. Guess I must be an idealist too…
I would have just said “code like mine”.
I also thought I needed a variant for Split. I had to test it just now to check. No day is wasted if I learn something.