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.DOMDocument60
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.DOMDocument60
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
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)
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.