How to insert document property into excel field


A

aMc

Hi - quick (and possibly stupid) question:

I cannot for the life of me figure out how to insert a document property
(e.g., Author, Title etc) into an excel spreadsheet. In word I'd do this by
clicking on Insert->Field, but there appears to be no correlation in Excel.

Is it possible to do this? If so, can anyone enlighten me?

Thanks in advance
aMc

vrth4hqlr4l001 at sneakemail dot com
 
Ad

Advertisements

J

JON JON

Hi, aMc,

You need a User-Defined Function (UDF) to accomplish this. Paste the
following code to a regular module.

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

Then, write this formula to any cell you choose

=docprop("Author")

You can change "Author" with any of the following:
Title
Subject

Author

Keywords

Comments

Template

Last Author

Revision Number

Application Name

Last Print Date

Creation Date

Last Save Time

Total Editing Time

Number of Pages
Number of Words
Number of Characters

Security

Category

Format

Manager

Company

Number of Bytes

Number of Lines

Number of Paragraphs

Number of Slides

Number of Notes

Number of Hidden Slides

Number of Multimedia Clips


Caveat:
This UDF can only be used within the workbook you wrote the function.
When any of the properties change the formula will not display the
changes until a calculation took place. Press F9 to force calculation

Regards,

Jon-jon
 
A

aMc

Thanks. Was hoping to avoid using vba, but never mind. Not too painful,
and it works a treat ;-)

Thanks for you help
aMc
 
Ad

Advertisements

Joined
Feb 5, 2018
Messages
1
Reaction score
0
Hi, aMc,

You need a User-Defined Function (UDF) to accomplish this. Paste the
following code to a regular module.

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

Then, write this formula to any cell you choose

=docprop("Author")

You can change "Author" with any of the following:
Title
Subject

Author

Keywords

Comments

Template

Last Author

Revision Number

Application Name

Last Print Date

Creation Date

Last Save Time

Total Editing Time

Number of Pages
Number of Words
Number of Characters

Security

Category

Format

Manager

Company

Number of Bytes

Number of Lines

Number of Paragraphs

Number of Slides

Number of Notes

Number of Hidden Slides

Number of Multimedia Clips


Caveat:
This UDF can only be used within the workbook you wrote the function.
When any of the properties change the formula will not display the
changes until a calculation took place. Press F9 to force calculation

Regards,

Jon-jon



____________________________________________________________________________--

Hi Jon-jon

I see this works well....

How would you be able to do this by using another document, say a word doc or another excel file?

Thanks

AP12553
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top