Display SharePoint Version number in Excel
I have managed to display the SharePoint Version Number in Word 2007, using the label and the Document Management Policies in SharePoint. Now I am looking for something similar in MS Excel 2007, allowing me to automatically display the current version number of the Excel file somewhere inside the document. The idea is to always have an up to date version number on any printout of an Excel file, so that we can control our documents better. While I managed to display the number through the "Quick Parts" tool in Word, I cannot see anything similar in Excel. Any ideas on how I can achieve this? Thanks for the help.
April 28th, 2010 9:15am
Firstly on the SharePoint document library create a custom column called SPVersion. A custom workflow will then be required to copy the SharePoint version number into the SPVersion column when an item is updated or added. Start Excel and from the quick access toolbar, select More Commands. Then from the left navigation, choose Popular and ensure that 'Show developer bar in the ribbon' is selected. Click OK. Now open the Excel and save the file as a Macro Enabled Excel Workbook. From the developer ribbon, click on 'View Code'. Right click on Microsoft Excel Objects and click on Insert > Module. Paste the user defined function below into the module: Function SPVersion() Dim wb As Workbook Set wb = ThisWorkbook For Each prop In wb.ContentTypeProperties If prop.Name = "SPVersion" Then SPVersion = prop.Value End If Next prop End Function Now return to the Excel sheet and enter the function =SPVersion in any of the Excel cells to display the SharePoint version number.
April 28th, 2010 2:52pm
Thanks, that works.
April 29th, 2010 1:44am