Office Dev Con – Excel Demo 1

This is the first of two posts documenting talking about the demos that I gave at the Office Developer Conference.  The first of my two demos was the simplest.  If you remember, I opened created a new order form in InfoPath, imported the data from that form into Excel, exported the data back out, re-opened it in InfoPath, saved it back to disk and then opened it in Word.

Here are all of the files for the first demo.  For the rest of this post, I’ll walk you through what I did, and what you’ll need to do to get it working on your machine.


First off, if you decide to put these files somewhere other than c:john’s officedev demo you will need to update each of the InfoPath form files (the .XML files) to point to the new location of the .XSN file.  Let me show you what I mean:

As I mentioned in my demo, XML files can have what is known as a processing instruction at the top of the document which can transfer information to whatever application is loading the xml file.  In InfoPath’s’ case, we have two:

This one simply identifies the XML file as an InfoPath file and instructs Windows to hand it off to the InfoPath runtime

This guy is a little more involved, but basically, it just tracks all of the metadata about the form’s XSN file, such as version, and location of the XSN.  As I moved the XSN out of sharepoint and onto my disk, I had to go into each of the XML files and change the href attribute to the new location: C:john's OfficeDev DemoEmporium.xsn

Playing with the files

In InfoPath:
So, now that everything is ready go to, double click one of the form files (I used Order3.xml because it has multiple line items) to load it in InfoPath.  Change some data, add some line items, etc. Then save it back to disk.

In Excel:

  1. Load the XML Source task pane by clicking Data, XML, XML Source

  2. In the XML Source task pane, click XML Maps and then Add.

  3. Select c:john’s officeDev DemoFormSchema.xsd and click Open.

  4. When prompted to select a root, choose Order and click OK. (This is because of the way InfoPath schemas are designed, they allow for any one of a number of root elements.  However, in our case, Order will always be our root node).

  5. Finally, click Yes/Ok/I agree to send John my CC info/Etc. until the schema has been imported into a map.

  6. Drag the three Details elements (Employee, OrderDate and Customer) to B1, B2 and B3

  7. Drag the Item element (the repeating node containing the order items) to A6.

  8. Note that Excel has automatically created a list object.  Right click the list and select List, Totals Row to turn on the Totals Row.

  9. Confirm that your spreadsheet looks something like this (without the pretty lavendar formatting of my superior design).

Stil in Excel…

  1. Right click the list (again), but this time, select XML, Import

  2. Import c:john’s officedev demoOrder3.xml.

  3. Make some changes

  4. Right click the list and select XML, Export.

  5. Save the file as Order3_modified.xml

  6. Close Excel

What the H???

You’ll notice that Order3_modified.xml will not open back up in InfoPath.  Instead, when you click, it’ll open in IE, like any other normal, uninteresting XML file would.  That is because, Excel is not bothering to keep track of our processing instruction for us (see the setup section for a long and unintersting discuscussion of processing instructions).  If you want your modified order3 to open back up in InfoPath, simply open the older order3.xml, copy out the two processing instructions (that I mention above) and paste them in to order3_modified.xml.  Voila!  You will now see your form in InfoPath

To Open in Word:

By default, you’ll be able to open any XML file in word, however it will be quite uninteresting to look at.  Instead, we want to open the XML data as a receipt that we can print/email to our customer.  To do this, I built a document in Word that I thought fully matched the worthiness of the rest of my demo.  Once it was designed, I saved the doc as XML and converted it to an XSLT.  (btw, the XSLT – which you have, is kind of ugly.  I didn’t really think I’d be showing it to the world, so bear with the absolute disregard for formatting :^)

  1. Open Word

  2. Click Tools, Templates and Add-ins (btw, this is one of the most frequent things I do in Word now).

  3. Click on the XML Schema tab

  4. Click Schema Library

  5. Click Add Schema

  6. Select c:john’s officedev demoFormSchema.xsd and click Open.
    At this point, we have made word aware of the form’s schema, but we haven’t told it anything to do with XML it finds that matches the schema.  Our next step is to tell it about the XSLT that will convert the xml to the receipt.

  7. Back in the Schema Library, make sure that you’ve selected the schema you just imported.

  8. Click Add Solution.

  9. Select c:john’s officedev demoletter.xslt and click Open.

  10. Click OK as many times as you need to get back to Word.

At this point, Word is not only aware of the InfoPath schema, it will apply an XSLT to the data to make it look all perty for us. 

  1. From within word, click Open

  2. Open c:john’s officedev demoOrder3_modified.xml (or any other form file).

  3. Marvel at the spectacle of graphics and formatting that is my XSLT!

Leave a Reply

Your email address will not be published. Required fields are marked *