How to insert document property into excel field

  • Thread starter Thread starter aMc
  • Start date Start date
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
 
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
 
Thanks. Was hoping to avoid using vba, but never mind. Not too painful,
and it works a treat ;-)

Thanks for you help
aMc
 
JON JON said:
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
Click to expand...



____________________________________________________________________________--

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
 
Back
Top