Working with Document Properties on Header and Footer

Adis_1102

New Member
Joined
Oct 8, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.
2.PNG


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 :))
1.PNG


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!



VBA Code:
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top