Read XML files using DOM

In Create XML files using DOM we reviewed a method for creating XML files using worksheet data. Now let's go over a method for importing them back into Excel.

Unfortunately this method is a bit coupled to the input XML, but my understanding is that any XML parser depends on the input being a certain format, so I'm not too concerned. It's simple to adapt this function to other XML formats, anyway.

Function to Read XML back onto worksheet

The following function will read the XML file we creating using the previous code and return the XML as an array.

Function ReadXML(fileName As String) As String()

  Dim xmlDoc As Object ' MSXML2.DOMDocument60
  Dim myvalues As Object ' MSXML2.IXMLDOMNode
  Dim values As Object ' MSXML2.IXMLDOMNode
  Dim value As Object ' MSXML2.IXMLDOMNode
  Dim tempString() As String
  Dim numRows As Long, numColumns As Long
  Dim i As Long, j As Long

  ' check if file exists
  If Len(Dir(fileName)) = 0 Then Exit Function

  ' create MSXML 6.0 document and load existing file
  Set xmlDoc = GetDomDoc
  If xmlDoc Is Nothing Then Exit Function
  xmlDoc.Load fileName
  If LoadError(xmlDoc) Then Exit Function

  ' second node starts the node tree
  Set myvalues = GetNode(xmlDoc, 2)
  ' array size? add +1 for header row
  numColumns = myvalues.childNodes.Length
  numRows = GetNode(myvalues, 1).childNodes.Length + 1
  ReDim tempString(1 To numColumns, 1 To numRows)
    
  For i = 1 To numColumns
    Set values = GetNode(myvalues, i)
    ' first value in every column is node name
    tempString(i, 1) = values.nodeName
    
    For j = 1 To numRows - 1
      tempString(i, j + 1) = GetNode(values, j).nodeTypedValue
    Next j

  Next i

  ReadXML = tempString

End Function

First we create a MSXML DOM Document to hold the XML, then load the XML and check for parsing errors. We add one row for the header values, then use an inner loop so we can loop through as many nodes as necessary. It doesn't matter if there is one column or multiple columns, the function will parse them all and return the XML values as an array.

GetDomDoc, LoadError and GetNode may be found at MSXML Object Library Functions.

The code makes a lot of assumptions. It validates the XML and makes sure the file exists, but doesn't check that you passed it an actual .xml file (except by trying to parse the input file). It assumes that there are an equal number of child nodes for each "Values" node. Basically, I would not use this code without modification unless you were using the code in the previous article to generate the XML in the first place.

Sample Usage

Sub TestReadXML()
  Dim values() As String
  values = ReadXML("C:\Myxmlfile.xml")
  
  Range("A1").Resize(UBound(values, 2), UBound(values)).value = _
    Application.Transpose(values)
End Sub

Our sample function creates a range the same size as the array, then pastes in the values.