Exporting XML From Excel Tables – 2: Object Model

In the previous post, we were looking at how to export a hierarchical structure as XML, from a set of Excel tables, where the rows are hierarchically numbered (1, 1.2, 1.2.3, etc). The approach in our VBA is to build a hierarchical object model from our worksheet tables, and then ask the root of the model for its XML string. Both the building and the string generation proceed recursively down the structure. Note that this approach works for any hierarchical data – in our case it is software test definitions.

There are six levels in our hierarchy. #1 is represented by an entire workbook; #2 to #5 are represented by subsets of (contiguous) table rows; #6 is represented by a single cell in a table row (plus its associated header information). There is a VBA Class Module for levels #1-5.

Level #6 corresponds to a test parameter – a single cell, plus its associated column header. The parameters don’t have their own class, as they are really just a couple of values (data, header). The level #5 class holds this information as arrays (corresponding to the relevant data row and the header row).

The multi-level numerical ID is complex enough to warrant a VBA Class Module of its own. This allows us to do things like asking an ID what level it’s at, or asking for a new ID for its Nth child.

Each of the five classes representing a test level has attributes (Private) to record:

  • the chunk of worksheet to which an instance maps: the table (ListObject), the row within the table, and the row count (i.e. the size of the table chunk)
  • its ID (TestID object)
  • data that applies to that level, such as a description, category and mode
  • a Collection of child objects, representing the next level down

Each of the five classes has a Build method (Sub), which is called by its parent object (or the user interface, for the top level). The Build method takes as parameters:

  • its parent’s ID
  • the chunk of worksheet to which an instance maps: table (ListObject), row within the table, and row count (i.e. the size of the table chunk)

The Build method:

  • stores its parameters
  • gets and stores its table-row information
  • creates a new ID for itself (derived from its parent’s ID)
  • calls a method to BuildChildren

The BuildChildren method scans through the table chunk, finding the sub-chunks corresponding to each of its children. For each sub-chunk, it creates a child object of the appropriate class, and asks the child to Build itself, given the appropriate parameters (see above). Determining which level a particular row represents is just a case of seeing which of its first four cells have numbers, and which blanks. There are utility functions for doing this.

A couple of observations regarding the ‘object oriented-ness’ of this. Firstly, in a fully OO language, the Build method would probably be the constructor for an object. The is preferable because it removes the possibility of having ‘not yet built’ objects. However, in VBA, Sub Class_Initialize can not take parameters, so we need a separate method called immediately after the object creation (Set … New …). Secondly, in a fully OO language, one would be tempted to to abstract some of the structure and processing into an abstract class – say, TestNode. However, our structure is not arbitrarily deep – we’re not going to have 20 levels. Also, the top and bottom levels are special cases – actually, only levels #2 and #3 are substantially the same.

Having built an object model, generating the XML is then very easy. Each of the five classes representing a test level has an AsXml function. Here’s the top-level one, on Test Definition:

Function AsXml() As String
        'Element <tdefn>

    AsXml = "<tdefn id='" & tdefn_id & "'>" & vbCrLf & _
                DescAsXml() & _
                ConditionsAsXml() & _

End Function

Function ConditionsAsXml() As String
        'Element <tcond>*

    ConditionsAsXml = ""
    Dim tc As tcond
    For Each tc In tdefn_conds
        ConditionsAsXml = ConditionsAsXml & tc.AsXml & vbCrLf

End Function

The second function shows the recursion of AsXml to the child level (here, Test Condition). At other levels, there’s a little extra complexity to allow for optionally outputting secondary information, such as Descriptions.

The top-level XML String is then written out to a file via a TextStream (MS Scripting library). This is wrapped in a function XmlOut.

There is a simple ‘user interface’ worksheet, with a Command Button:

Private Sub ExportCommand_Click()
        'Build the TDefn and write its AsXml string to file

    Dim td As TDefn
    Set td = New TDefn
    XmlOut td.AsXml

The same kind of recursion down our hierarchical object structure can be used to make the structure self-checking (up to a point). I’ll talk about this in the next post.

2 Responses to “Exporting XML From Excel Tables – 2: Object Model”

  1. 1 evrentuskan February 13, 2013 at 8:52 am

    Hi Roy,
    i would like to know what is the tool that you are using to paste your vba codes inside your posts?

  1. 1 Exporting XML From Excel Tables – 3: Self-checking Structure « Roy MacLean's VBA Blog Trackback on December 27, 2012 at 1:08 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

December 2012
« Dec    

%d bloggers like this: