Using DOM with ADO to Convert Excel Data to XML

A useful aspect of a DOMDocument object is that it can serve as the container for any hierarchical XML structure. This allows you to load any valid XML construct into a DOMDocument object. Coincidentally, ADO has an XML persistence constant that enables any recordset to persist in an XML stream.

ADO (ActiveX Data Objects) is a data access technology that is installed with Microsoft Data Access Components, and it's covered in detail in Chapter 20.

To use the procedure demonstrated here, you will need to first set a reference to Microsoft ActiveX Data Access 2.6 Library (any later version is also valid).

To help demonstrate this, open the Programming XML.xlsm file, found in the XMLSampleFiles folder in this chapter's download page at In this file, you will find the following procedure. This procedure loads an Excel range (in this case range A1:D43) into an ADO recordset, and then saves the recordset into the DOMDocument object, which is then output to an XML file:

Sub Convert_Excel_Data_to_XML()

Dim oMyconnection As Connection Dim oMyrecordset As Recordset Dim oMyXML As DOMDocument Dim oMyWorkbook As String

Set oMyconnection = New Connection Set oMyrecordset = New Recordset Set oMyXML = New DOMDocument

'Identify the workbook you are referencing oMyWorkbook = Application.ThisWorkbook.FullName

'Open connection to the workbook oMyconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & oMyWorkbook & ";" & _ "Extended Properties=excel 8.0;" & _ "Persist Security Info=False"

'Load the selected range into the recordset oMyrecordset.Open "Select * from [Sheet1$A1:D43]", oMyconnection, adOpenStatic

'Load the recordset into the DOM Document oMyrecordset.Save oMyXML, adPersistXML

'Save DOM Document to an xml file oMyXML.Save (ThisWorkbook.Path & "\Output.xml")

'Clean up oMyrecordset.Close Set oMyconnection = Nothing Set oMyrecordset = Nothing Set oMyXML = Nothing

End Sub

Once the procedure is run, you will find the output XML file in the same directory as the Programming XML.xlsm file. When you open the output XML documents, you will notice that it does not look like the ones you have experienced here so far. This is because ADO produces attribute-based XML. An attribute-based XML document is almost exclusively made up of attribute nodes and is self-describing—they contain metadata that describes both the structure of the recordset and the data inside the recordset.

An XML file generated by ADO typically contains one root element and two child nodes: Schema and Data. The Schema node contains information about the recordset structure: field names, data type, field length, position, and so on. The Data node contains the actual data. Although attribute-based documents are difficult for humans to read, they are well formed and pose no problem for Excel.

When mapping an ADO-produced XML document, the XML Source pane will look similar to Figure 12-10. Remember to map the Data node to your spreadsheet.

3 SM niZ:Schema

J id r lns2:ElementType _J name U content +: I j ns£:AttributeType i+i L_v ns2:extends 3 b ns3;daca £-■ ns4:fow _l Mafkfit _l 5ales_PeMod _J Revenus _l Units_3old

Figure 12-10

0 0

Post a comment