Alright, so we were recently faced with a challenge. Client has a bunch of templates they use locally on desktops that they want to be more efficient with as part of a conceived application and workflow process in SharePoint. Simple enough, right, create content types, add columns, insert Document Properties into the Document Content Types and Bob’s your Uncle. Well, not quite. This client gave us their ‘Templates’ and this included 13 Word Documents and 1 Excel Document.
They had presented this to another consultant as well as their in house IT team. All had come up with relatively the same set of ‘solutions’ to the problem. What’s the problem? you ask…The problem is that you cannot add document properties, natively to Excel Documents in the quick and easy manner that you can, say a Word Document. We discussed the problem, and like so many before came up with the same possible solutions:
1. Create an InfoPath form to collect data and present it to users via PDF Output (ultimately that is where it needs to go)
2. Create a Word Document version of the Excel Workbook and let users interact with that.
3. A Variety of equally non-workable solutions for the client.
You see, they like the functionality of Excel. There are cells that have formulas and they want to be able to be flexible with those formulas, dynamic even. Which is to say some of the content in the excel workbook comes from the list item in sharepoint, but some of it is manipulated on demand by the user…Word, might could do that. Word with an embedded excel workbook object might could do that, but none of them were quite right.
Which is what leads me to the VERY helpful link above. ‘But, Bill, that is for BuiltinDocumentProperties, and SharePoint Server Document Properties are not in that collection.’ That is correct, however ActiveWorkbook also allows for ContentTypeProperties which exposes the full gambit of Server Document Properties. Note in order to use this, however, you must have the document in a Document Library with properties attached.
Now that I have my modified User Function (based on the link, thank you Gord Dibben), I can now place the formula =DocProps(“xxxxxxx”) and reveal the Metadata in the Excel Document correctly.
So now, user fills out InfoPath Form on list item, on submit, workflow creates several Word Documents and 1 Excel Workbook from Content Types, saves them in a Document Library, and populates Metadata on each newly created document and workbook.
This is an issue we have faced before, and like so many others created work arounds for clients, this time, that was not an option, and so we found a solution.
- I want to insert the file properties into a cell for printing. – ExcelBanter
- Getting the Current User Name or ID in SharePoint using SharePoint Designer no code – Josh Gaffeys Blog – Site Home – MSDN Blogs
- NOT LIKE [1-9]% : LIKE « Query « SQL Server / T-SQL Tutorial
- Project GeoFlow: A New Dimension in Viewing Geospatial and Temporal Data – Next at Microsoft – Site Home – TechNet Blogs
- This was (a) Good Friday