MetaData

[MikeDBMan]

I have a lot of .xlsx (or .xlsm) stored on SharePoint sites. We store several pieces of data in the metadata area of the files. I would like to either use a UDF or some kind of method to display some of the metadata in my spreadsheet. I would prefer NOT to have to open the properties window as there are a lot of fields I don't care about. Is this possible to do? Thanks!

 

[Jerry Sullivan]

Mike, That's a great idea.

Here's a UDF you could try.

Function GetProperty(sPropertyName As String, _
        Optional bCustom As Boolean = False, _
        Optional bVolatile As Boolean = False) As String
    
    If bVolatile Then Application.Volatile
    
    If bCustom Then
        GetProperty = ThisWorkbook.CustomDocumentProperties(sPropertyName)
    Else
        GetProperty = ThisWorkbook.BuiltinDocumentProperties(sPropertyName)
    End If
End Function

The function takes up to 3 parameters:
sPropertyName: (required) The Property Name
bCustom: (optional) True for Custom Properties, False for Built-In Properties (default)
bVolatile: (optional) True for recalculate with any worksheet calculation, False = recalculate when input parameter changes only (default).

Here's some examples showing how the function might be called:

Excel Workbook
	A	B
1	Built-in Property Examples
2	Title	UDF to Show Document Properties
3	Author	JS411
4	Keywords:	Excel, VBA, UDF, Properties
5	Last save time	1/6/2012 12:29:55 AM
6
7	Custom Property Examples
8	Client:	Acme Biz
9	Project	Top Secret
10	Document Number	1234
Sheet

 

[MikeDBMan]

This seems to work. On documents that are .xlsx since I am using a vba program to open and check out the documents, I think I'll just refresh a place in one sheet with the latest version of what the properties are since I can't seem to make a udf work in a non-macro .xlsx file. Thanks for the help!