locked
Display SharePoint Version number in Excel RRS feed

  • Question

  • 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.

    • Moved by Mike Walsh FIN Wednesday, April 28, 2010 6:29 AM Seems more like an Excel question. Certainly not customization of SharePoint. being generous and moving to SP Admin (From:SharePoint - Design and Customization (pre-SharePoint 2010))
    Wednesday, April 28, 2010 6:15 AM

Answers

  • 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.

    • Marked as answer by andreasbbbbb Wednesday, April 28, 2010 10:44 PM
    Wednesday, April 28, 2010 11:52 AM

All replies

  • 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.

    • Marked as answer by andreasbbbbb Wednesday, April 28, 2010 10:44 PM
    Wednesday, April 28, 2010 11:52 AM
  • Thanks, that works.
    Wednesday, April 28, 2010 10:44 PM
  • Hi Neville

    This is a really great post. Thank you! The only issue that I have is that the SPVersion column does not update along with the system version number. As if the SPD workflow is only set to start on the creation of an item, but it's not. I have clicked both options. Any ideas? I use Office 2013 with SP 2013.

    Thank you

    Martin

    Wednesday, May 22, 2013 12:52 PM
  • up...
    Thursday, September 25, 2014 1:49 PM