Adding Document Properties in Excel

We’ve already told you about document properties and how useful they are in Word.  We also complained about the poor support for properties in Excel.

You can add and edit document properties in worksheets much the same as in Word documents.

Go to File | Info in Excel 2013 (Prepare | Properties in Excel 2007) and all the properties are there including a document panel, advanced and custom properties, same as in Word.

In Excel, the only way to add document properties into a worksheet is via a custom function.

There’s various solutions on the net.  We found this one here from ‘Jon-jon’, thanks.

Private Function DocProp(Info_needed As String) As Variant
 Application.Volatile
 DocProp = ThisWorkbook.BuiltinDocumentProperties(Info_needed).Value
 End Function

Use this formula to add a property to the worksheet:

=docprop("Author")

Use any of the regular document property names, including

• Title
• Subject
• Author
• Keywords
• Comments
• Last Author
• Last Print Date
• Creation Date
• Category
• Manager
• Company

The full list of available properties is here – where you’ll also find details of the VBA property used.

Note that there’s no error checking, if you request a property that isn’t set or existing, an error is returned.

For custom properties you need a slightly different function such as:

Private Function DocPropCustom(Info_needed As String) As Variant
 Application.Volatile
 DocPropCustom = ThisWorkbook. CustomDocumentProperties(Info_needed).Value
 End Function

There’s no direct way to use document properties in the worksheet!

You’d think with all those options in the worksheet structure they’d be accompanied by a function like say  DocProperty()  which insert or even change the properties within the worksheet, for example =DocProperty(“Title”) could insert the document title.

As you can see from the VBA code above, the document properties are available in VBA so making them available in a regular Excel function would not seem a big ask.  The custom functions above are just ‘wrappers’ that make the existing VBA feature available to the worksheet.

It begs the question .. why are all the document properties in Excel to begin with?   Our spies tell us that there was a Microsoft management edict to have the same interface for document properties in Word, Excel and PowerPoint so that was done by each development team.  But the same care wasn’t taken to making use of those properties.  Fixing this problem would not be something that Microsoft could show off at demonstrations and isn’t cloud related so it’s a lower development priority.