Adding and Binding XML in Office Documents
I am frequently asked about how to do some of the basics with XML parts in Office documents. There are excellent blogs and content out there to guide you in working with Office Open XML such as Brian Jones, Doug Mahugh, MSDN, and the Office Open XML SDK. I have also blogged extensively about OOXML and co-authored a major book on XML. But, the need to help more and more people get into the game of XML and Office is ever present.
So, here are a couple of routines you'll find useful. The first routine adds two XML parts to a Microsoft Word document. The second removes the first XML part.
Private Sub AddToDataStore()
Dim pCustomPart As CustomXMLPart
Set pCustomPart = ThisDocument. _
CustomXMLParts.Add
pCustomPart.Load "C:\XMLPart1.xml"
pCustomPart.Load "C:\XMLSchema1.xsd"
End Sub
Private Sub RemoveFromDataStore()
Dim pCustomPart As CustomXMLPart
Dim pCustomParts As CustomXMLParts
Set pCustomParts = ThisDocument.CustomXMLParts
pCustomParts.Item(1).Delete
End Sub
It's essential to create copies of documents before you start messing around with their content and composition via code.
Now, you can add whatever XML you want. In this case, the XML contains data for a memo and it is in the document. The document contains places for the memo data. Up to this point, the memo data are unseen to the user. To surface the data in the document's editable area you place content controls, one for each memo property (To, From, etc.) in the document and bind the content controls to the XML source in the following way:
Private Sub AddControlMap()
Dim cControls As ContentControls
Set cControls = ThisDocument.ContentControls
cControls.Item(1).XMLMapping.SetMapping "/ns:memo/ns:to", _
"xmlns:ns='https://painjunkie.spaces.live.com'"
cControls.Item(2).XMLMapping.SetMapping "/ns:memo/ns:from", _
"xmlns:ns='https://painjunkie.spaces.live.com'"
cControls.Item(3).XMLMapping.SetMapping "/ns:memo/ns:date", _
"xmlns:ns='https://painjunkie.spaces.live.com'"
cControls.Item(4).XMLMapping.SetMapping "/ns:memo/ns:topics", _
"xmlns:ns='https://painjunkie.spaces.live.com'"
cControls.Item(5).XMLMapping.SetMapping "/ns:memo/ns:body", _
"xmlns:ns='https://painjunkie.spaces.live.com'"
End Sub
What is so slick about all of this is that Office does the work of gluing it all together for you if your XML data conforms to the XML schema you added in the AddToDataStore procedure! By mapping a content control to a specific node in the schema will cause the data (also mapped to the schema) to load into the content control.
Try it out, and tell me how it goes.
Rock Thought of the Day: Lovin' the blues-soaked song "Bridge of Sighs" from Robin Trower off of his 2007 release by the same name. Check it out!
ROCK ON!
Technorati Tags: Office 2007,XML,Office Development,OBA,OOXML,Content Controls,Microsoft Office System,Microsoft Word,VBA
Comments
Anonymous
February 18, 2009
PingBack from http://www.clickandsolve.com/?p=10718Anonymous
March 11, 2009
John, I work with a VB6 application that uses Access 2003 to mail merge information into a Word 2003 document. The VB6 application accesses data from multiple sources (SQL Server and mainframe). When a user desires to generate a letter (document) in Word 2003, data (both SQL Server and mainframe) is then transferred to an MDb and then mail merge is used to bind a canned .dot file to this MDb. There is a strong push in our office to eliminate the need for Access. I when a user desires to create a document, I would like to take the necessary data (SQL Server and mainframe) and place it into an XML file. Word could then take the data from this XML file and place it in the necessary fields in the .dot file. I have heard that Word 2003 does not support mail merge directly from an XML file. I would appreciate your insight on how best to handle this situation. Thanks, Mike