Updating Excel Server Properties from content with Moss2007 and Office 2010 – Part 2

In first aticle i have told about how to update Excel Server Properties and integration of Moss 2007 for old Excel 97 and 2003 format. As explained before its not able to do that with new xlxs file formats without using some visual basic code.

Part 2 – Using VBA for Excel 2007 , 2010 format:

I have created a document library named TestExcel on my Sharepoint and select document file template for excel file. After do that i have created 3 column on list.

TotalCost as  Number 
Note: In my envoriment i create a column name is Total than i changed the column name as ”TotalCost” so keep in your mind. i will explain it later becuase when you change the column name on a document libarary actual name on db can not changes and this  causes very common misunderstanding. )
PaymentDate as  DateTime
Description as  Single Line of Text

So far so good Lets go to Document Library and create a new Excel Document form library by clicking New-> Document.

If everything is ok you will see familier Excel 2010 open your document. And notice that Document Properties panel is opened for you. If you are getting problem with to open Excel document by clicking Sharepoint Document Library ->New-> Document you have possible owssupp.dll registration problem.
see kb article for more information:
http://support.microsoft.com/kb/833714

!!!Dont forget to save this excel file as “Excel Macro-Enabled Workbook” for other users can able to use this file correctly.

I have created a sample excel content see bellowed screenshot. And notice that i have give cells names which i want to map with server properties .

!Important : Sharepoint uses  ISO8601 format (YYYY-MM-DDTHH:MM:SSZ) for saving dates so you should make changes on date format for sharepoint understanding.
i use for that another cell and formul like =TEXT(B3,”yyyy-mm-ddThh:mm:ssZ”)

Now we have to enable Developer Tab on office ribbon . For do that.

1)Click File Menu -> Options

And Check “Developer” check box. and click ok.

Select Developer Tab -> Click Visual Basic.

type  codes to VBA IDE

//This function is updates server properties.
Sub SynchServerPorperties()
Dim metaprop As MetaProperty
‘On Error Resume Next

For Each metaprop In ActiveWorkbook.ContentTypeProperties
If metaprop.Name = “TotalCost” Then
metaprop.Value = Cells(2, 2).Value
End If
If metaprop.Name = “PaymentDate” Then
metaprop.Value = Cells(3, 3).Value
End If
If metaprop.Name = “Description” Then
metaprop.Value = Cells(4, 2).Value
End If

Next

End Sub

‘We are updating Server Properties before save the file.
Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
SynchServerPorperties
End Sub

*This code provide us when user save file , server properties would be updated.

Click “Save” on excel and test server properties updated.

Update xlms file to sharepoint.

When everytime you click “Save” If you are annoyed a “Privacy Warning” message like me you can get rid of it bellowed way.

Privacy warning : “This document contains macros, ActiveX controls, XML expansion pack information, or web components. These may include personal information that cannot be removed by the Document Inspector.”

Click Developer Tab-> Macro Security

Select “Privacy Options” and remove check on “Remove personel information from file properties on save”

Upload “xlsm” File to Sharepoint and notice that metadata form filled automatically.

If everything is ok. you have to see bellowed screen 🙂

End of Article…
See you next time.