Saturday, July 03, 2010

Office and SharePoint Metadata Synchronization (2007 and 2010)

This post is an extension to a previous one adding the sequence for Excel 2010.

When uploading 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 the Office 2007 program (Excel, for example)
  2. Select Prepare / Properties to show the doc's current properties.
  3. Click Document Properties dropdown on the top left and 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.
If the name matches a SharePoint Library column the property will pass upon uploading or downloading the document.

In Excel 2010:
  1. Click File and select Info.
    You will (hardly) see the Properties section on the right.
  2. Click to open the Properties dropdown.
  3. Select Advanced Properties to open that window.
    This is the same window as in step 3 above.
  4. Follow steps 4-6 above.
As Niall commented, this works as expected only upon upload. Afterwards when you update the doc's properties you need to do it twice, for the doc's and for the server's property. Otherwise the values won't be identical.

Adding Mark's important tip from September 7th 2012:
One can sync a cell value with a SharePoint list column via an Named Range given to the cell. See Mark's comment below.

33 comments:

  1. Hi

    I am really struggling to pass my SharePoint 2010 library data to a excel macro enabled worksheet.

    I have a column "Sprint Start Date" and a custom property "Sprint Start Date" of type date. However they are not in sync. Any advice please.
    In word with quick parts this is so much easier.

    ReplyDelete
  2. I am not sure I understand the question. Can you provide more details?

    ReplyDelete
  3. Ok, I want to pass metadata held in a SharePoint content type to into a excel enabled workbook which is referenced by the content type.

    Now I have created a column "Sprint Start Date" and a custom property "Sprint Start Date" however, I cannot pass a the value set in the workbook back up to SharePoint.

    I have no probs if I was using say a word content type / doc template as I can happily assign word property data to metadata held in SharePoint. Excel does seem to have such a slick mechanism.

    I can email you bit maps of excel and or my demo SharePoint library if required.

    ReplyDelete
    Replies
    1. Hi Westerdaled,
      I need to implement the same scenario, it will be great help if you can send me the link or code how to implement it.
      I tried this "http://123sharepoint.ch/archives/176#respond", unfortunately giving error "method range of object _global failed"...

      Help is greatly appriciated
      Murali.P

      Delete
    2. Murali,
      Have you tried Mark's suggestion below?

      Delete
    3. Hi Zeev thank you very much for follow Up.

      I forgot that the custom property of a document will get binded with the list columns by default [if both are same], no need to write additional code here, just we need code in-case if we need to return to display in Excel.


      Great thanks
      Murali.P

      Delete
  4. Westerdaled, I had done some testing on your scenario. Generally, Excel doesn't have issues in syncing its custom properties with SharePoint columns. However, the issue you are having seems to be the date type one. I tried to a macth a date type with a date type and failed. I tried text property with date column and also failed. I changed both to text and succeeded. My guess is that SharePoint is expecting a certain date format. at any rate, if you can't find the correct format you can always match by text and then have a calculated column translating the value into a true date.

    ReplyDelete
  5. Ze'ev


    Thanks for that. As you say it is worth testing which data types do pass values and which don't. I will try and do some more digging. If not it looks like your suggested workaround..

    Thanks again

    Daniel

    ReplyDelete
  6. Ze'ev,

    Thank you for your post. I was wondering if it is possible to import the Excel metadata from a SharePoint library template? I was quite successful importing the Excel metadata into a list, but I would like to find a way to upload the metadata into a SharePoint library every time our Excel template is submitted. We have a specific Excel file that we would like to use as a template for a new library, and would love to be able to use the Excel metadata to populate the columns upon submittal. Is this possible? If not, is it possible to get the SharePoint metadata to populate the Excel cells?

    ReplyDelete
  7. Catherine,
    Can you provide an example to clarify your scenario?

    ReplyDelete
  8. I'll try. My spreadsheet has custom metadata cells named "Customer" "Project" and "Quote #". I would like to use this spreadsheet as a library template, and have the information typed into the "Customer" "Project" and "Quote #" cells populate the columns in the SharePoint library. I have already used Content Types to add metadata with these headings, but I would prefer to use the imbedded Excel metadata to populate the columns instead. This way our techs are only filling in the information one time. Does that make sense?

    ReplyDelete
  9. Oh, Ze'ev, in case I was not clear, I used custom properties to distinguish the cells "Customer" "Project" and "Quote #."

    ReplyDelete
  10. Catherine,

    I checked your issue with some surprising results. The version is SharePoint Foundation 2010 and I believe that this applies to SharePoint Server 2010 as well.

    The scenario: I created a Document Library and added a text column named ABC. I created an Excel xlsx doc and added a custom property with the same name. Then uploaded it as a template. I didn't create a content type to test this nuance.

    Clicked on New to open a new document in Excel, entered a value to the server variable that poped up, saved in an appropriate name and closed.

    The results:

    Excel 2010 requires to enter the custom property twice if you want it in both places, while in Excel 2007 if you enter the value in the server properties area it will also show in the document properties.

    One difference I notices was the file type. In 2007 the original xlsx was saved as xls (and so did in 2003 version). In 2010 it was saved as xlsx.

    You may try to upload the template as xls if possible or save as xls. Please let me know if this workaround solve the issue.

    ReplyDelete
  11. Wow! Thank you Ze'ev! I can't believe that it worked after all this time.

    I have one last question for you. Is there a way that the custom properties can be populated by cells in the Excel spreadsheet? For example, if I enter "ABC Company" in the cell named "Company," is there any way for "ABC Company" to populate the server property "Company" at the top? I hope that made sense. You have been a great help, thank you!

    ReplyDelete
    Replies
    1. It is not possible to move data from a cell into a document property without VBA code. If you are ready to use VBA I may be able to provide the code, but not in the next few days.

      Delete
    2. That's ok, we do not want to use code for this particular form. Thank you for all of your help!

      Delete
  12. You can get data from a cell into a Custom Property without using VBA by using a Named Range. For example, cell A2 has a value "ABC Company" and also has a named range called 'Cust' pointing to cell A2.
    Create a custom property, 'Customer', click on the 'Link to content' tick box, the 'Value' box changes to 'Source' dropdown menu, select the range 'Cust'.
    Then proceed as normal for linking properties into Sharepoint. The column 'Customer' will receive 'ABC Company'
    Word of Warning: Sharepoint looks for a match on the name of the column when it was first created. Hence if you make a typo, and go back and change it, it still will not work. You have to delete the column in Sharepoint and create it again (without the typo).
    If you alter the property value in Sharepoint then check the document ‘out’ then ‘in’ the spreadsheet will get the change.

    Great post, very useful.

    ReplyDelete
    Replies
    1. Mark,
      Thank you for the tip. I added a coummnet on this in the post.

      Delete
    2. This tip was very helpful, but (there's always a but, right?) this works like a charm when I have a Excel file on disk and try to upload it to Sharepoint, but if I open a file from sharepoint the map property-range we did before is not there, so if I modify and try to save as ask to fill the properties. Is there something wrong or is this the way this works?
      Thanks for your help.

      Delete
    3. If you open for update directly from a SharePoint library, and try to save as another name, it should show you the currect document's server properties where you can make changes or save with the same ones. It also carries the original document's properties. Itested a saved doc type, not docx.

      Delete
    4. Yes, I see that, maybe I didn't explain myself right. I map a cell with a named range, I think when I open a file and change the cell with the named range, the property mapped to it should update too? Am I right or just being hopeful?, this is not working as I describe; I change the excel cells and the properties presenve their original values.
      Thanks again

      Delete
    5. I tested your scenario and indeed a named range mapped to a custom property doesn't change the property's value upon a change in the cell's value. This happens for both operations: save, and save as. As mentioned earlier, you can trust only an upload operation to sync the values. Afterwards it would be manual. It was probably too complicated for Microsoft to handle this variant.

      Delete
    6. Just to add to this thread. I am needing to do t he same. Excel 2007 spreasheet with cells/named range > Custom Property to Sharepoint 2010 Cuistom Column. The initial upload of the spreadsheet populates the properties and columns. Any attempt to edit the spreasheet named cell contents does not update the custom properties or library columns. This is the case on check out / edit / check in. It is also the case when I bring the latest sheet local, change values and re-upload and overwrite. In fact even the DIP doesn't show changes initially. If it woked I expect the DIP valies would change but after initial upload excel and Sharepoint arent talking. If this must be a second manual step after initial upload it will likely not work for us. Is it any better in Excel 2010 > SP2010?

      Delete
    7. Doug,
      I checked Excel 2010 which has the same issue as 2007, It doesn't synchronize custom properties of existing documents. Only standard properties are synchronized. So I guess it applies to Office 2010.

      Delete
  13. Hi,

    I just found this blog. I am wondering if you can help me. On a daily basis I have a VBA script that creates a balancing workbook. I then map my SharePoint site as a disk drive. I then use a FileSystemObject to copy the Excel workbook to the site. this works great but the workbook does not have any SharePoint metadata values so the workbook is not place in the right location on the SharePoint site. Is there a way yo prgrammatically add the metadata values to a workbook? The workbook gets created on the fly so I cannot create the custom fields. I am using Excel 2010.

    Thank you,
    Kent

    ReplyDelete
    Replies
    1. Kent,

      Your VBA skills are far better than mine. I write very little VBA, only when I have no other choice.

      How about using one of the available properties and then to engage a workflow to copy the doc from the target library to its correct destination based on the property value?

      Delete
    2. Hello Kent,
      how did you map your sharepoint site as disk drive?
      i'd like to see your vba script...

      Delete
  14. Hi Zeev,

    I am not sure what you mean by "engage a workflow"? We use different levels within our SharePoint site which I think is making this even harder.

    Thanks,
    Kent

    ReplyDelete
    Replies
    1. If you know how to plug an Excel document into a library, you can easily use SharePoint Designer (SPD) to create a conditional workflow on that library to apply various actions. SharePoint Designer is a free tool from Microsoft. You need to use the correct SPD version: 2007 for SharePoint 2007 and 2010 for SharePoint 2010. I can't promise that you will find in SPD the exact action you need but it worth trying.

      Delete
  15. Hi Zeev,

    I have an issue with Document Library property on a migrated SharePoint 2010 site. The Site was migrated from 2007.
    I am facing the issue while editing the Document Library property. I am assuming that the issue might be with the synchronization of Document Library property and Document. Sorry if i am technically wrong (actually i am newbie to SharePoint).

    Steps to reproduce

    1.Create a document library (Default document type is MS word)
    2.Create a new drop down column (file type)
    3.Create a document and save it.
    4.Modify the library by removing a column and then add a new column.
    5.Create a document – Verify the properties change reflected correctly in the new Document creation.

    In the 5th step, what i observed is that the removed metadata is still existing and the new metadata is not displayed in the word template.

    But if I upload the document, i can see the changes but not when i click on New button in the Document library which in turn opens the Word template where properties in server will be displayed at the top.

    Please share your knowledge on the above mentioned issue.

    Regards
    Alandha

    ReplyDelete
  16. If I correctly understood the scenario, I suggest you recreate a new template and replace the current one.

    ReplyDelete
  17. I am struggling with this but would really like it to work without VBA if at all possible. I have a SharePoint library with fields. I have an Excel file that gets created (from template) when a File-New is requested. The range names exactly match the Document Properties/SharePoint Columns but none of the data in Excel is writing back to SharePoint. Any thoughts on what I'm missing?

    ReplyDelete
    Replies
    1. I also tried to incorporate Excel named range cells in a template with corresponding SharePoint column names and failed. Excel 2010 and SharePoint 2010

      Delete

Enter your comment here