Working with Document Properties on Header and Footer

[Adis_1102]

Hello community,

I have the following requirement, where I am not sure how to handle as best. I would like to insert the Document Properties (BuiltInProperties f.e.) in the footer or header.

Let me show you a solution from a word document:

This is a document footer from a word doc. It has some descriptive fields and fields where the data is being pulled from the document properties, so its generic. It wasnt made by me but it seems easy to achieve.

Now lets jump to Excel. You see here a footer from an excel sheet. I just wrote in the descriptive "fields" and the random text. Question is now: How to make descriptive fields neat and tidy? Pulling the data from the document properties is not an issue as of now, I ve written the code for it (see below: its not finished but I wanna give you something in return as you help me )

I thought of just writing everything in code, also the formatting...
A more generic solution would be to use "name definition" if possible, so the user can also rearrange the fields and data would still be on the proper position...(not sure if that would work though)

Maybe you have some experience where you may point me into the right direction? I dont expect a fully How to answer, just a pointer into a proper solution...


Thanks in advance!

Sub MyWorkbookProperties()

Dim i As Integer
Dim Custom As Object
Set Custom = ActiveWorkbook.CustomDocumentProperties
Dim Default As Object
Set Default = ActiveWorkbook.BuiltinDocumentProperties
Dim Content As Object

On Error Resume Next
Set Content = ActiveWorkbook.ContentTypeProperties


Dim Name As Variant
Dim Value As Variant



On Error GoTo BadValue

Range("A1") = "Built in Document Properties"

Debug.Print vbNewLine; "Built in Document Properties"; vbNewLine

For i = 1 To Default.Count
    Name = Default.Item(i).Name & ": "
    Value = Default.Item(i).Value
    Range("A" & i + 1) = Name
    Range("B" & i + 1) = Value


Next


Range("D1") = "Custom Document Properties"

For i = 1 To Custom.Count
    Name = Custom.Item(i).Name & ": "
    Value = Custom.Item(i).Value
    Range("D" & i + 1) = Name
    Range("E" & i + 1) = Value
Next


Range("F1") = "Content Type Properties"

For i = 1 To Content.Count
    Name = Content.Item(i).Name & ": "
    Value = Content.Item(i).Value
    Range("F" & i + 1) = Name
    Range("G" & i + 1) = Value
Next






BadValue:
Value = "Bad Value"
Resume Next



End Sub