Exporting XML From Excel Tables – 3: Self-checking Structure

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.

Having built a hierarchical object structure from our worksheet data, there are a number of other things we might want to do:

• check the test definitions for structural validity
• generate output in other formats, such as plain text (really plain, not XML)

I’ll talk a bit more about the first of these, here.

The basic idea is that of a class invariant – a property (which can be a conjunction of simpler properties) that always applies to any instance of the class. This idea is, of course, part of Bertrand Meyer’s Design By Contract approach.

Writing good invariants is hard, particularly for objects that are essentially reactive in their behavior. However, in our case here, we have a static hierarchical object structure, and there are some fairly obvious relationships between a node in our hierarchy and its child nodes:

• there is a least one child node
• the ID of the current node is a prefix of each child node ID (for example, 2.3 –> 2.3.1)
• the number of the table rows representing the current node = the sum of the rows for the child nodes + 1 row for the current node

These properties help to ensure that the test data worksheets are ‘well formed’ – with no extra or missing rows, and with proper hierarchical nesting. You can see an example of the last property in this screen shot:

• test condition 101 is represented by rows 3:9 = 7 rows
• test scenario 101.1 is represented by rows 4:6 = 3 rows
• test scenario 101.2 is represented by rows 7:9 = 3 rows
• so 7 = 3 + 3 + 1 row for the test condition itself

The Eiffel language has full support for invariants and other assertions, and it would be the job of the class constructor to establish the invariant. With VBA, we have to take a less rigorous approach, and have the invariant as a normal Boolean-returning function, which should return True any time after the Build procedure has been called. In Eiffel, failure of an invariant or other assertion raises an exception; in VBA, we’ll just put up an error MsgBox.

Here’s the invariant function for a test condition (level #2 in the hierarchy):

Function Invariant() As Boolean
    Invariant = _
            tcond_tid.IsCondition And _
            tcond_scenarios.Count > 0 And _
            tcond_scenarios(1).ScenNum = "1" And _
            ChildrenPartitionRows
    If Not Invariant Then MsgBox "Problem with Condition " & Me.Tid

    Dim ts As TScen
    For Each ts In tcond_scenarios
        Invariant = Invariant And IsChild(ts.Tid, Me.Tid)
        If Not Invariant Then MsgBox ts.Tid & " is not a child number of " & Me.Tid
        Invariant = Invariant And ts.Invariant
    Next
End Function

The first part checks:

• the Condition’s ID is at the right level for a Condition
• there is at least one child Scenario, and that the numbering starts from 1
• that the child Scenarios cover all of the Condition’s rows (minus 1)

The second part iterates over the child Scenarios:

• checking that the child IDs extend the Condition’s ID (there’s an IsChild relationship between the two IDs)
• recursively evaluating the invariants for the child Scenarios (and thence recursively down the whole structure)

The ChildrenPartitionRows function is a simple iteration over the child Scenarios:

Function ChildrenPartitionRows() As Boolean
        'Do the rows of my children + 1 = my rows?
    Dim childrenrows As Integer
    Dim ts As TScen
    For Each ts In tcond_scens
        childrenrows = childrenrows + ts.Rowcount
    Next
    ChildrenPartitionRows = (childrenrows = tcond_tblrowcount - 1)

End Function

It might seem that we’re making extra work in writing these invariant and helper functions, but it’s a useful exercise in understanding how our worksheet tables represent a hierarchical structure, and what rules this implies for the worksheet/table layout. Further, we can make the checking directly accessible to a user via a Command Button: the handler simply builds the structure and calls the top-level invariant (on the Test Definition):

Private Sub CheckCommand_Click()
        'Build the TDefn, then evaluate its Invariant (and recursively down the structure)

    Dim td As TDefn
    Set td = New TDefn
    td.Build
    If td.Invariant Then
        MsgBox "Check Complete"
    Else
        MsgBox "Some problems were found"
    End If
End Sub

Note that we’re not being very informative about the precise location of an invariant failure. However, it would be simple enough to record an invariant failure on the object concerned, and then interrogate the structure subsequently to find out the ID of that object.

Finally, as an example of ‘plain text’ output (as opposed to XML), we can have objects provide a simple text representation of themselves – say just ID and Description, with vbTab and vbCrLf. This can be displayed in a TextBox (with property MultiLine = True), to give us a simple Table of Contents. Done within the handler above, this would be:

Worksheets("Front").TocText.value = td.Contents

For a simple one-level ToC, the Test Definition would simply iterate over the child Conditions, concatenating their individual Contents strings.

Advertisement

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() & _
            "</tdefn>"

End Function

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

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

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
    td.Build
    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.

Back, Briefly: Exporting XML From Excel Tables

It’s been a while since I posted anything to this VBA blog, since my main field of work is XML based documentation (see previous posts). However, I recently did an Excel VBA micro-project, which I think is worth sharing. I’ll describe the problem and outline the solution in this post, and add some details in a couple of further posts.

The overall aim is to provide a way of creating and editing software test definitions, while also making those definitions accessible to a test harness for actually executing the tests. (The solution I’m going to describe is, with appropriate modifications, more widely applicable than just for test definitions).

Since the test definitions fall naturally into a tabular form, Excel is an obvious front-end tool. The problem is how to make the data accessible to a testing harness. One way is to use Apache POI, a Java API for accessing Microsoft Office files – and our developer was keen to use this. However, there are benefits to having a plain text – that is, XML – representation of the data:

• It can be stored (alongside the Excel workbook) in a version-control repository, and different versions differenced, using a plain text or (better) XML-aware diff tool
• A body of test definitions can be analyzed and reported on using XQuery
• Any scripting language with an XML library can access the test data

So, the requirement is for a VBA export facility that takes the test definitions in a workbook, and exports it as a single XML file.

Now, if the test definitions were simply a list of tests, it would be very simple – we might be able to use Excel’s built-in XML facilities. However, our test definitions are quite deeply hierarchical:

1. Test Definition (for a system component or capability)
2. Test Condition (a functional area to be tested)
3. Test Scenario (a particular test configuration)
4. Test (an atomic test)
5. Test Step (one or more steps within a Test – typically only one)
6. Test Parameter (property, value and purpose – in, out, environment)

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

#2 to #5 have hierarchical, numeric IDs, spread over the first 4 columns of a table. Where (typically) a Test has only a single Step, they are represented by a single row (that is, the Step is implicit). For example:

• 101 is a Condition,represented by rows 3:9
• 101.2 is a Scenario,represented by rows 7:9
• 101.2.1 is a Test, represented by row 8

(each Test here has a single implicit Step). The columns on the right-hand half of the table represent the Parameters. The greying-out of ID numbers is just conditional formatting.

The XML looks much like any XML:

The point to note is that we have a six-level hierarchical structure, with each node in the hierarchy mapping on to a chunk of Excel table.

Now, the solution, in outline. We could pass through the tables and rows, spitting out XML strings as we go. However, this approach is rather limited, as there are a number of other things we might want to do:

• check the test definitions for structural validity
• generate output in other formats, such as plain text (really plain, not XML)

As earlier posts have illustrated, I’m keen on building object models that ‘sit behind’ the worksheets, and provide the application functionality. This seems to be a classic application for this. Each item in our test definition hierarchy is represented by an object (of the appropriate class), and is built from a chunk of worksheet table.

We start by creating a Test Definition object, and asking it to build itself. It goes through the worksheets and tables, finding the chunks that correspond to Conditions (using the ID level). It creates Test Condition objects, tells them to build themselves from the appropriate chunks, and adds them into a Collection.

Each Test Condition object goes through its chunk, finding the sub-chunks that correspond to Scenarios (using the ID level). It creates Test Scenario objects, tells them to build themselves from the appropriate sub-chunks, and adds them into a Collection. And so on, down the levels…

Having built the object hierarchy, we can do various things with it. Primarily, we want an XML string, which we can write out to a file. We simply ask the top-level Test Definition object for its XML string. This asks each of its Test Conditions for their XML strings, concatenates them successively, and wraps the result in its own Definition-level XML element (<tdefn> – see above).

Each Test Condition asks its Test Scenarios for their XML strings, concatenates them successively, and wraps the result in its own Condition-level XML element (<tcond> – see above). And so on, down the levels… A few other elements, such as descriptions, get mixed in, as we go.

Note that the object hierarchy is built anew, each time we invoke an operation – there’s no persistence other than through the table rows. This might seem wasteful, but there’s really no performance issue.

In the next post, I’ll say a bit more about the actual code involved, and I’ll say something about the self-checking functionality in a third post.

Signing Off…

Thanks to all of you who have visited this blog in 2011, and have continued to make comments. However, I need to point out that I’m no longer actively blogging here, so this site is now just for reference. I hope you continue to find it useful.

At the start of the year I started a new  job which has taken me away from Excel, Access and VBA. As some of my later posts indicated (see the tag cloud), I am now in the world of DITA XML documentation, in both a technical and an authoring capacity. My current investigations concern:

• storing document components in an XML database
• categorizing document components, on multiple dimensions, using internal meta-data
• constructing documentation build manifests (DITA maps) using high-level queries (in XQuery)

If that sounds interesting, then contact me via the About page.

Enjoy your coding, in whatever language!

Still here…

Sorry the postings have been a bit thin on the ground since the New Year. Rather unexpectedly some freelance work has developed into a permanent position. I am now in a role which is primarily focused on DITA/XML documentation, for a company that produces a Payments Processing system (Direct Debits and such like). The challenge is to make the documentation scheme fit the highly modular nature of the system, and the core product –> client customization delivery process.

While Excel/VBA will still be in the mix, it won’t be central to what I’m doing. I’ll try to carry on posting as and when, so keep dropping by.

Analysing Cross-references Between Documents

I have recently been working on a set of about 200 Use Case documents, converting them into DITA – an XML-based structured document format. In the body text of a Use Case, there can be cross-references to other Use cases – so we have a many-to-many relationship between Use Cases (just as we could between any type of document). It’s interesting to analyse this relationship, to see which Use Cases have lots of outgoing references, and which Use Cases are referenced by many other Use Cases.

This kind of totalling is Pivot Table territory. So the question arises of how to contruct the basic data about the cross-references – a table of A-references-B records – and how to get it into Excel.

A key advantage of XML-based documents is that you can import them into an XML database and run queries over them, where the queries are written in XQuery. Such queries can generate output as XML, HTML or plain text. For our purpose, a simple route is to output an HTML table. This can then be imported by Excel via >Get External Data >From Web. The URL for the import points to our XQuery file, within the database server:

http://roy-laptop:18080/qizx/xqs/ucrefs.xq

Here, the database is XMLMind’s Qizx, running in server mode (locally on my laptop, in this case). This URL can be used like any other in a browser interface, with the results of the query being displayed within the browser. In our case, we are going to give the URL to Excel.

Here’s the code of the query:

xquery version "1.0";
declare option output:method "html";

declare variable $ucs := collection("/UC");

declare function local:uc_refs()
{
  for $ucref in $ucs//uc-ref
  let $file := document-uri(root($ucref))
  let $ucreftext := $ucref/text()
  order by $file
  return
  <tr><td>{$file}</td><td>{$ucreftext}</td><td>1</td></tr>
};

let $ucrefs := local:uc_refs()
return
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <title>UC References</title>
  </head>
  <body>
    <table>
      <thead>
	    <tr><td>Source</td><td>Target</td><td>Token</td></tr>
      </thead>
      <tbody>
	    {$ucrefs}
      </tbody>
    </table>
  </body>
</html>

Variables start with $. The function collection (line 4) scopes the query to a particular directory tree of document files. The expression ‘$ucs//uc-ref’ binds to each occurrence of the <uc-ref> element, anywhere in the collection of documents. $file is the document in which a <uc-ref> is found. The third column of the table (Token) is just the number 1 – this gives me some data for the pivot table to sum.

Now we can do the import into Excel:

Finally, I can insert a pivot table based on this data, subtotalling by either Source or Target, or putting both dimensions on the same axis. I love it when stuff just works…

Out of curiosity, I ran the import with the macro recorder on, to see what the VBA looks like:

Sub Import()
    With ActiveSheet.QueryTables.Add( _
        Connection:= _
            "URL;http://roy-laptop:18080/qizx/xq/ucrefs.xq",
            Destination:=Range ("$A$1"))
        .Name = "ucrefs.xq"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

So it’s just creation of a QueryTable, with the URL given as a Connection string. The WebSelectionType property is ‘all tables’, although I thought I clicked the lower of the two yellow arrows in the Import dialog. Clearly, we could wrap this up a bit more: selecting from a list of queries, creating a new Worksheet, etc.

Having established the basic XML –> XQuery –> HTML Table –> Excel/VBA architecture, I think I’ll experiment more with this in due course.

Incidentally, this is my 100th post (Rah! Rah!), having taken almost exactly two years. And most enjoyable it’s been – at least for me 🙂

An Alternative to VBA in Excel?

Back last year, Gareth Hayter of Slyce Software emailed me about VScript, an alternative to VBA for writing functions and macros in Excel. Unfortunately, for various reasons, I have not been able to look into it in any detail, but it certainly sounds interesting.

VScript is based on Excel-Dna, which is a project to integrate .NET into Excel. The aim is thus to allow you to write functions and macros in C# or VB.Net – so, presumably it is aimed at developers familiar with those languages, and preferring them over VBA. Code is claimed to run considerably faster than VBA (not hard to believe).  VScript provides an IDE integrated with Excel, as with VBA. You can use VScript to create stand-alone XLL add-ins, and digitally sign these.

What about VSTO? Gareth says:

VScript is different from VSTO (Visual Studio Tools for Office) in many ways:

• VSTO is for professional programmers: It can be complex and confusing to use and requires a lot of time, effort and money to learn.
• VSTO is expensive: You need to buy Visual Studio® which starts at $799.
• VSTO is not built into Excel®: It is an external program and works in a very different way from VBA.
• VSTO projects are complicated to deploy: VSTO is not integrated into Excel, which means that it’s difficult to make a few changes and test them quickly. It requires ClickOnce deployment.
• VSTO cannot create User-Defined Functions (UDFs): With VSTO, you can’t create functions that you can use in a similar way to SUM() and AVG().

One to watch …

Moving Data Out of Outlook – part 2

Happy and code-full 2011!

In an earlier post, I was considering exporting my Outlook contacts as XHTML pages, and accessing these as a repository of free-format, but queryable, documents. After some time out for Christmas and New Year, not to mention my family’s seasonal cough-fest, I can report back.

I haven’t attempted a comprehensive export of all contact attributes, just the standard ones that I typically use. This still leaves information in the Contact’s body section, but I’ll migrate that manually, as and when.

Here’s an example contact page:

I just grabbed a standard style sheet, so the formatting is not up to much (for example, the cell text middle-aligns vertically, as you can see with “Address”).

In terms of the VBA code, all we need is an adapter class, ContactPage. Instances of this are created for each ContactItem that we export. A ContactPage is responsible for building the XHTML string corresponding to the ContactItem, and writing it out to a text file. Here’s the root procedure:

Const outdir As String = "C:\Temp\Contacts"

Sub ExportContacts()
  Dim allcontacts As Items
  Set allcontacts = Session.Folders("Personal Folders"). _
                             Folders("Contacts").Items

  MsgBox "Exporting " & allcontacts.Count & " Contacts"

  Dim cn As ContactItem  'Outlook class
  Dim cp As ContactPage  'Custom class
  For Each cn In allcontacts
    Set cp = New ContactPage
    Set cp.Contact = cn    'Property Set
    cp.OutputToFile outdir
  Next
End Sub

ContactPage has a function for PageTitle, which is taken from the ContactItem‘s Company or Full Name (in that order of preference). Functions PageId and PageFile are based on this (with a bit of cleaning up to remove characters such as ‘/’).

The sub OutputToFile uses the Microsoft Scripting Runtime library, specifically FileSystemObject and TextStream. You need to load the Scripting library manually, via >Tools >References.

The rest of ContactPage’s features are just functions that generate elements in the XHTML hierarchy. For example:

Public Function XhtmlPage() As String
'The XHTML representation of cp_contact
    XhtmlPage = Header_ & vbCrLf & _
                Html_
End Function

'...

Public Function Html_() As String
    Html_ = "<html id=" & Chr(34) & PageId & Chr(34) '& ...
    Html_ = Html_ & vbCrLf & _
            Head_ & vbCrLf & _
            Body_ & vbCrLf & _
            "</html>"
End Function

Public Function Body_() As String
    Body_ = "<body>" & vbCrLf & _
            "<h1>" & PageTitle & "</h1>" & vbCrLf & _
            Company_table_ & vbCrLf & _
            Person_table_(1) & vbCrLf & _
            Person_table_(2) & vbCrLf & _
            Person_table_(3) & vbCrLf & _
            Categories_ul_ & vbCrLf & _
            "</body>"
End Function

'...

Public Function LastName_td_() As String
    LastName_td_ = "<td class=" & Chr(34) & "LastName" & Chr(34) & ">" & _
     cp_contact.LastName & "</td>"
End Function

The lowest level functions, typically for <td> elements, access the ContactItem‘s properties. As you can see, there are a lot of quotation characters (Chr(34)) in XHTML. The trailing-underscore convention for the function names was so that I could call the functions after the XHTML tags they generate – without the underscore I got a name clash with something existing.

Minor complications where the Contacts folder contains Distribution Lists (class DistListItem), which is a different class from ContactItem – that is, a MAPIFolder.Items is a heterogeneous Collection. This makes the For Each loop throw a type mismatch error, although you can resume execution (>Debug, >Run).

Also a slight issue with duplicate filenames, since the file output overwrites by default. I used Round(Rnd * 1000, 0) to generate a temporary disambiguating suffiix for the filename.

Here’s the Class Module for ContactPage.

 

Diff for Access Tables

I haven’t had time to progress the Outlook Contact Export, so here is a minor digression.

Suppose that I have two Access tables each containing records that represent the same entities – Orders, say. These two sets of equivalent records come from different sources: maybe Orders as received, and Orders as dispatched. I want to cross-check certain fields for equivalent records – say, that order-quantity received and order-quantity dispatched are the same – and flag any mismatches.

Here are two tables:

I want to check the equivalence of columns B and C. There are differences in B/C for the second and fourth records, and an extra record in the second table.

Note that the IDs are counters, but are not necessarily the same, so I can not do a Join of the two tables. The tables are assumed to represent equivalent sets of records – maybe for the same period – so they could be queries on underlying cumulative tables.

It’s not apparent to me how you could do this using queries. If we could join the tables together, then for numerical values I suppose that we could subtract one field from the other, and check for non-zero differences.

However, a more generic solution is to suck the relevant columns into two Recordsets, using a simple SELECT query as the Source, and then iterate through records and fields, comparing values. Differences can be logged in some way:

• If we expect only rare differences, then we could build up a log String, and display this at the end of the check:
  
• If we expect substantial differences, we could base an output Recordset on a temporary table, and write message-records into it.

I’ve adopted the first approach, for simplicity.

Here’s a code module, and the database (2003 .mdb) with test tables. There’s no user interface: RunDiff is run directly from within VBA.

Moving Data Out of Outlook

I’ve felt uneasy for a while that important data – for example, my Contacts – is sitting in Outlook, in an enormous and opaque database file (.pst). Okay, it’s backed up, but if it became ‘corrupted’ and wouldn’t open, then I have no way of recovering the data, that I know of. Why should I have to open Outlook, just to find someone’s website, or phone number? The whole idea of organizing your life within a single application seems terribly outmoded. I just want a mail client that handles message send and receipt; all the information I want to be in the ‘outside world’.

Retrieving data can be pretty painful too. I’ve just clicked on the ‘Activities’ tab of a contact, and it’s taken over 2 minutes to come back with about 20 items. While there is free-text search, there is no proper query access.

Outlook’s data model is baroquely large – a futile attempt to cover all possibilities. I’m looking at the class ContactItem, and it has nearly 150 attributes. I can have NickName, TelexNumber (telex?) and YomiFirstName (something Japanese); but I can only have three email addresses and one webiste URL.

So what’s the alternative? What I’d like is for each item (contact, whatever) to be a web page in a local (private) website. New pages could be created, edited and added as necessary. The information is all fully accessible using basic or more sophisticated editors. Website and mailto links work normally – the latter opening a mail client, as usual.

As it happens I’m using a document repository for XML documents, including those in XHTML. This runs on a Jetty server, at a localhost port. The repository can be browsed as a directory hierarchy, free-text searched, and queried (using XQuery). Because the repository is fully indexed, the search and query are very fast. I can create/edit documents in the repository directly, without the need to export/import, using a WebDAV-enabled editor.

So, to populate the repository initially, I would need to export my existing contacts from Outlook in some way. Exporting items as XML or HTML directly is beyond Outlook (up to and including v2007). So I’m thinking that I could write a custom export to XHTML – nothing complicated, just a simple page with tables. Elements, such as the table cells, would be given standard id attributes to indicate the content type – such as ‘company’, ‘address’; these can then be used in queries, analogously to column names in SQL.

In terms of the design, I’m wondering about a custom class, say ContactAdapter, which would wrap a ContactItem, and build the complete XHTML string, and write it to a file (using the Scripting library for the file access). I’ve not done anything like this before, so we’ll see. I suppose that I could export the data to Excel, and work on it there, but dealing with rows and columns is less direct than working with the actual ContactItem objects in Outlook.

More anon…