Friday, August 28, 2009

Excel and SharePoint Metadata Synchronization

When uploading Excel or other Office documents to a SharePoint Library, the document properties are mapped to the Library columns by name. Therefore, you can easily add columns to the Library as appropriate to handle the MS-Office document properties. However, if you need an extra property for the document to map from a SharePoint column it is a bit harder.

In Office 2003 it was easier but in 2007 it is more confusing:
  1. Click the Office Button on the top left of Excel (2007).
  2. Select Prepare / Properties to show the doc's current properties.
  3. Click Document Properties dropdown on the top left and select Advanced Properties.
  4. Select the Custom tab.
  5. Here you can add a Name of a new property and enter a value for that property.
  6. Click Add and OK and you are all set.
  7. If the name matches a SharePoint Library column, the property will transfer when uploading or downloading the document.

6 comments:

  1. Can currency values be populated into SharePoint as well? The text field have populated perfectly but I am not having any luck with my currency fields?

    ReplyDelete
  2. If you define the SharePoint column as Currency and the document field as text you should get it right.

    ReplyDelete
  3. It seems that it only works when you upload the file.

    If opening the file from SharePoint and change the document property, this doesn't reflect back in the SharePoint column.

    ReplyDelete
  4. I also noticed that updates don't work as expected. After investing some time I decided to consult the customers to give up maintaining the document's properties internally except properties like saved date.

    ReplyDelete
  5. Sadly this trick doesn't work wtih Excel 2010 and MOSS 2007. Both my cell in excel and column in MOSS are set to "TEXT" type. I do not understand why Microsoft is not make this so much easier, similar to Word.

    ReplyDelete
  6. Working with Excel 2010 generates the same effect as described in this installment. Upon upload the identical custom in MOSS column will inherit the initial value from the doc but when updating the doc's property one must do it for both the doc's and server's property otherwise they won't be in sync.

    ReplyDelete

Enter your comment here