SharePoint Version properties displayed within MS Office Docs
OK, This is not technically an Advertising related Technology post, but it was an important find for me. I hope it is for you too.
SharePoint Properties to track version
While SharePoint Document Libraries can be set to manage document versioning, imbedding that information into the Office Documents that are being versioned is not simple. There are Quick Parts to insert Server managed properties (metadata) from SharePoint, but these do not expose the version history information.
Solution Found
Show SharePoint Version in Office Documents allows you to expose this information as additional fields in your Document Library within SharePoint, and have the toolset automatically populate this information with every version change.
See the page above for full details, there is a version for SharePoint 2010 and WSS 3.0.
Once you have installed this solution, you can easily add the version information to any MS Word 2007 docx managed within your SharePoint Document Library. Simply check out the doc, go to Insert -> Quick Part, and select Document Property, then select the specific property you want to display. I made use of all 4 fields from the solution, Current Version, Approved Version, Approved By, and Approval Date. By making use of the Quick Part, the automatic updating of your Word docx is handled for you. Each time you open the document from SharePoint, the fields automatically populate with the latest information from the associated Document Library.
Thanks to Phil Childs for this wonderful solution!
But... what about Excel?
Since Microsoft, in their infinite wisdom, chose not to include Quick Parts as part of Microsoft Excel 2007, we need to find an alternate way to get this information into your Excel docs. After much digging and combining solutions, I landed on a workable solution. While not complete, it does offer 3 of 4 fields, auto updating. Good enough for my needs.
Since Excel does not offer Quick Parts, how do you display SharePoint Properties in an Excel document and have them auto populate with the latest metadata? Follow along for some basic help.
- Open your Excel doc from SharePoint (this can be .xls or .xlsm format)
- Expose the Developer Tab in the Ribbon
- Click on the Visual Basic button on the far left
- Double Click on the ThisWorkbook item at the bottom of the object tree
- paste the following (modified to your cells and worksheet)
Private Sub Workbook_Open()Sheets("Metrics").Visible = TrueSheets("Metrics").SelectFor Each Prop In ThisWorkbook.ContentTypePropertiesIf Prop.Name = "Current Version" ThenCells(17, 3) = Prop.ValueEnd IfIf Prop.Name = "Approved Version" ThenCells(18, 3) = Prop.ValueEnd IfIf Prop.Name = "Approval Date" ThenCells(20, 3) = Prop.ValueEnd IfNext PropEnd Sub
This script simply runs each time the document is opened, and replaces the values in the Cells listed with the current data from the SharePoint fields "Current Version," "Approved Version" and "Approval Date."
Yes, there are more elegant ways to insert into specific Cells by name, but this worked for me. You need to update the Sheet reference with the specific worksheet you want this information updated on, and then alter the Lines that set Cells(row,col) to Prop.Value to the specific Cell reference you need. If your excel doc has only one worksheet, you can delete the two lines dedicated to showing and selecting the correct worksheet (lines 2 & 3)
I was unable to get the Approved By property out of SharePoint after much mucking with field types. I'm not a programmer, and don't have time to work through the Error 13 mismatch type issues. Nevertheless, the three fields listed here do function and this auto updates as you open the document from SharePoint!
3 out of 4, not bad.
- Scott
