Last May I blogged about XML mapping with Word and SharePoint - i.e., inserting document properties into your Word Document using content types. Today one of my clients asked about doing this with an Excel template, and after much investigating the Excel ribbon and Googling the issue, we discovered that you can't do it in the simple, out-of-box way that Word provides. Apparently it can only be done with VBA (Thanks, Douglas M. Smith at BrainBench!). I'm a little surprised that the MS Excel team's blog does not discuss this, and that the MS Word team's blog on the XML mapping subject does not mention that this is not the same for Excel.
As usual, what turns up when researching this issue is lots of people asking the question, and no answers.
Luckily, my client is a developer. She found this solution (thanks, Chip Pearson at Pearson Software Consulting, LLC!) with downloadable code which let her access a property and set it in the spreadsheet.
The code needed modifications to get it to work and suit my client's needs. I won't detail the entire solution here, but a few notes:
Chip's code handles the Office document properties, not the Server properties (i.e. SharePoint). The property you need to use to access the server properties is WB.ContentTypeProperties.
The Excel template must be saved as .xltm (a macro-enabled Excel template) rather than xltx. We stored it in the Forms folder of the SharePoint document library, and changed the default template URL in the Advanced settings for the document library.
The upshot is: it can be done, but you'll need a developer if you're not one already.
Hi,
i am having the same issue. If its not possible in OOB then how we can be sure it works with the code.
Posted by: yagya shree | April 30, 2009 at 08:22 PM
yagya, thanks for your comment. My client was able to get the code to work; this functionality seems to require a more robust solution than what you get OOB.
Posted by: sadalit | May 01, 2009 at 11:26 AM
how can I access the item id?
Posted by: stef | April 28, 2011 at 11:26 AM