Microsoft: please enhance Excel such that I can
- Retrieve and manipulate Custom Document Properties using only Formulas (no macros required) in the same way that can be found using Word’s Insert > QuickParts > Field functionality.
- Insert these same custom document properties into the header/footer section of a worksheet.
For example, in Word, I can choose Insert > Quick Parts > Field > DocProperty. Then choose any document property maintained and insert a reference to it.
Limitation of Microsoft Excel
Unfortunately, in Excel a macro must be written in order to retrieve values from the CustomDocumentProperties collection. A formula can then reference this macro. In a corporate network with any sort of security policy, this means that users must “enable the macros” for this to function properly.
(Reference Chip Pearson’s website for GetProperty code.)
Function GetProperty(PropertyName As String, PropertySet As PropertyLocation, _ Optional WhatWorkbook As Workbook) As Variant
Further, I’m unable to insert any auto-updated values into the footer of an Excel worksheet other than the handful of values pre-defined by Microsoft: page number, date/time, sheet name, etc. In order for this to occur, I need yet another macro to execute Before_Print.
Private Sub Workbook_BeforePrint(Cancel As Boolean) With Worksheets("Sheet1").PageSetup .CenterFooter = GetProperty(“Issue.Header.Id”, 2) End With End SubCode found by doing a Google search for excel formula in footer.
I’d like to see efforts made to improve the functionality of these items in the next release of Microsoft Excel.