Excel 2003 properties in a Moss 2007 doc library

One of the problems that I’ve hit recently is how to pass customer properties from an Excel spreadsheet so that they become column entries in a document library.

Now SharePoint allows you to pass text values quite happily as linked content, however it doesn’t like passing numbers or dates.

Well, I’ve found a way round the numbers problem. It’s a little convoluted, but it does work.

First off, set the column names that you want in the Document library. Make sure you format these as you want them. In my case, I have a column named ES Setup and it’s set to have a 2 decimal point accuracy.

Now upload the excel spreadsheet into the doc library. Don’t bother filling in the properties at this point, just save it. Now re-open the excel spreadsheet. if you check file/properties then click on the button marked “File Properties” you should see your nice column name sitting there waiting for content.

I have on my spreadsheet a named cell called ES_Setup. This contains a set-up cost to clients that I want to bring out into the library. Next to this, I have created a hidden cell called txt_ES_setup. This cell contains the formulae =TEXT(ES_Setup,”0.00″) which basically puts a text copy of the numerical data into the cell.

Now the final part is to link the content of txt_ES_Setup to the column in SharePoint. To do this, go back to the File Properties window. Click on the SharePoint column name and click on Link to Content, Value will then become a drop down list and you can select txt_ES_setup. Click on Modify and you should see the correct value appear down in the properties box along with the linked properties symbol next to the name.

I’ve tried the same method with dates, but at the moment, as soon as you link any form of data into a propertie that forms a data column, you get a script error.

As ever, Watch this space..

Leave a Reply

Your email address will not be published.


This site uses Akismet to reduce spam. Learn how your comment data is processed.