I'm using an Excel template as a custom document type in a Sharepoint Document Set. I have some custom matadata for this document type, for instance one called Priority, which is a list (Low, Med, High).
I can read the metadata from within the document using ActiveWorkbook.ContentTypeProperties(Property).
Is there any way to change the value from within the document? I would like to have the metadata values derived from document content.
Refering to server metadata happens via ContentTypeProperties as you described. For them to be feched from doc content, I once created a worksheet function to do this that you tell the wanted metadata name and the value you wish it to be changed, here's my code and comments -
Public Function zSETSERVERMETADATA(ByVal metaTypeName As String, Optional ByVal newValue As String = "") As String
'Recalculate upon every time any cell changes
Application.Volatile
'Set wb pointer trough caller parents
Dim wb As Workbook, r As Range, ws As Worksheet
Set r = Application.Caller
Set ws = r.Parent
Set wb = ws.Parent
'Clear unused elements
Set r = Nothing
Set ws = Nothing
On Error GoTo NoSuchProperty
'If value defined on newValue, set the value and showoutput
If newValue <> "" Then
wb.ContentTypeProperties(metaTypeName).Value = newValue
zSETSERVERMETADATA = wb.ContentTypeProperties(metaTypeName).Value
Set wb = Nothing
Exit Function
'If no value defined on newValue only show output but leave content type unchanged
Else
zSETSERVERMETADATA = wb.ContentTypeProperties(metaTypeName).Value
Set wb = Nothing
Exit Function
End If
NoSuchProperty:
zSETSERVERMETADATA = CVErr(xlErrValue)
Set wb = Nothing
End Function
You can use it like any worksheet function for instance if you want to change server meta called "Author" to value "SickDimension" you call the function in cell with formula -
=zSETSERVERMETADATA("Author";"SickDimension")
Btw, with my function, leaving the second parameter empty will just return the value of that field.