Excel - How do I embed SharePoint document properties in Excel 2007?
Asked By Phil N
30-Jun-08 05:36 PM
I am able to embed document properties into a Word 2007 file but don't see
the equivalent for Excel. Surely it must be possible to embed SharePoint
metadata into a cell so that user of a spreadsheet don't have to cut & paste?
Thanks.
ActiveWorkbook.BuiltinDocumentProperties
(1)
ActiveWorkbook.CustomDocumentProperties
(1)
ActiveWorkbook.ContentTypeProperties
(1)
SharePoint
(1)
Excel 2007
(1)
Word 2007
(1)
Error
(1)
Application.Volatile
(1)
TESBC replied...
Okay this post is a old but for those that haven't worked it out and want to do it here are solutions as I was searching for sometime and finally worked this out.
All of these require you to create a visual basic module and work with Excel 2007. I haven't tested this with any other version so don't know if it will work or not.
Document Properties:
Function DocumentProperty(Property As String)
Application.Volatile
On Error GoTo NoDocumentPropertyDefined
DocumentProperty = ActiveWorkbook.BuiltinDocumentProperties(Property)
Exit Function
NoDocumentPropertyDefined:
DocumentProperty = CVErr(xlErrValue)
End Function
Custom Properties:
Function DocumentPropertyCustom(Property As String)
Application.Volatile
On Error GoTo NoDocumentPropertyDefined
DocumentPropertyCustom = ActiveWorkbook.CustomDocumentProperties(Property)
Exit Function
NoDocumentPropertyDefined:
DocumentPropertyCustom = CVErr(xlErrValue)
End Function
Sharepoint Properties:
Function DocumentServerProperty(Property As String)
Application.Volatile
On Error GoTo NoDocumentPropertyDefined
DocumentServerProperty = ActiveWorkbook.ContentTypeProperties(Property)
Exit Function
NoDocumentPropertyDefined:
DocumentServerProperty = CVErr(xlErrValue)
End Function
way to do this? Thanks for the help. Excel Miscellaneous Discussions ActiveSheet.PageSetup.PrintArea (1) ActiveWorkbook.BuiltinDocumentProperties (1) ActiveWorkbook.CustomDocumentProperties (1) Microsoft Excel (1) PageSetup (1) ActiveSheet.PageSetup (1) Worksheets (1) Excel (1) Andy With use this UDF Function DocProps(prop As String) Application.Volatile On Error GoTo err_value DocProps = ActiveWorkbook.BuiltinDocumentProperties _ (prop) Exit Function err_value: DocProps = CVErr(xlErrValue) End Function ' = DOCPROPS("author") 'or ' = DOCPROPS("last list of properties on a new sheet rw = 1 Worksheets.Add For Each p In ActiveWorkbook.BuiltinDocumentProperties Cells(rw, 1).Value = p.Name Cells(rw, 4).Value = " = DocProps(" & "A" & rw & ")" rw = rw
be displayed in a cell using some formula? Excel Miscellaneous Discussions ActiveSheet.PageSetup.CenterHeader (1) ActiveWorkbook.BuiltinDocumentProperties (1) ActiveWorkbook.CustomDocumentProperties (1) Microsoft Excel (1) PageSetup (1) Worksheets (1) Error (1) Excel (1) '- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Function DocProps(prop As String) '- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Application.Volatile On Error GoTo err_value DocProps = ActiveWorkbook.BuiltinDocumentProperties _ (prop) Exit Function err_value: DocProps = CVErr(xlErrValue) End Function and enter in a cell for Custom Properties Function CustProps(prop As String) Application.Volatile On Error GoTo err_value CustProps = ActiveWorkbook.CustomDocumentProperties(prop) Exit Function err_value: CustProps = CVErr(xlErrValue) End Function = custprops("Checked By") = custprops("Client") To
Dokumenteigenschaft Excel Hallo, ich versuche in Excel 2003 mit Function druckdatum() As Date druckdatum = ActiveWorkbook.BuiltinDocumentProperties("Last Print Date") End Function eine Funktion zu kreieren, mit der ich das Druckdatum verfügbar sollte es auch mit dem Text gehen. Was ist falsch? Gruß Stephan Excel - German Discussions ActiveWorkbook.BuiltinDocumentProperties (1) Office 2003 (1) Excel 2003 (1) Vista (1) XP (1) Dokumenteigenschaft (1) Fehlerwert (1 keywords: Dokumenteigenschaft description: Hallo, ich versuche in Excel 2003 mit Function druckdatum() As Date druckdatum = ActiveWorkbook.BuiltinDocumentProperties(Last Print Date) End Function
Exit Sub End If Application.EnableEvents = True End Sub Public Sub UserForm_Initialize() Me.CustDocName.Text = ActiveWorkbook.CustomDocumentProperties("DocName") Me.CustMajNo.Text = ActiveWorkbook.CustomDocumentProperties("MajNo") Me.CustMinNo.Text = ActiveWorkbook.CustomDocumentProperties("MinNo") Me.CustDocVer.Text = _ ActiveWorkbook.CustomDocumentProperties("DocName") & "_v" & _ ActiveWorkbook.CustomDocumentProperties("MajNo") & "." & _ ActiveWorkbook.CustomDocumentProperties("MinNo") End Sub Private Sub CustDocName_Change