Excel – XML Workflow – part 2

In the previous post, we were round-tripping data between Job workbooks and Timesheet workbooks. The final link in the cycle was for each Job workbook to update its cumulative hours-booked value, from a single XML file JobHoursList.xml. Here’s an example of that file:

<?xml version='1.0' encoding='ISO-8859-1'?>
<jobhourslist>
  <jobhoursentry>
    <jobnumber>0001</jobnumber>
    <jobhours>91</jobhours>
  </jobhoursentry>
  <jobhoursentry>
    <jobnumber>0002</jobnumber>
    <jobhours>27</jobhours>
  </jobhoursentry>
</jobhourslist>

A Job workbook needs to find the jobhoursentry for its own jobnumber, and load the associated jobhours value.

I decided to load the file, and search it sequentially, using the MS XML library (I went for Microsoft XML v6.0, which is called MSXML2 – I don’t know whether the version makes any difference).

The Workbook_Open handler calls a Load procedure. The whole file is loaded as a MSXML2.DOMDocument:

    Set doc = New MSXML2.DOMDocument
    With doc
        .async = False
        .validateOnParse = False
    End With
    ok = doc.Load(ActiveWorkbook.Path + "\" + datafile)

The nodes are each of type MSXML2.IXMLDOMNode. The jobhourslist is the document (that is, root) element:

    Set jobhourslist = doc.DocumentElement

Then we iterate through the child (jobhoursentry) nodes:

    For Each jobhoursentry In jobhourslist.ChildNodes
      Set jobnumber = jobhoursentry.FirstChild
      Set jobhours = jobhoursentry.LastChild
      'check jobnumber against the one in this workbook
      'and if a match, update the jobhours cell
      '...

Surprisingly straightforward. Incidentally, “DOM” stands for Document Object Model – that is, the node-tree representation of an XML document.

Here’s the VBA module, if you’re interested.

Advertisements

1 Response to “Excel – XML Workflow – part 2”


  1. 1 Adriene July 22, 2013 at 12:08 am

    Brewing that ideal cup of coffee as part of the home drip coffee
    maker is truly not that difficult, provided you have
    got the ideal stuff, the great drinking water, proper filter, and the
    quality freshly roasted coffee. To get more than
    41 many years, we now have been focused on bringing exceptional coffee and
    additionally coffee beverages in order to our consumers, states Howard
    Schultz, Starbucks chairman, leader and CEO, in the
    click release.


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




February 2010
M T W T F S S
« Jan   Mar »
1234567
891011121314
15161718192021
22232425262728

%d bloggers like this: