Formula to capture Document Properties

[Guest]

I am trying to capture the Document Properties fields (as you would insert a
field in Word). I have tried formulas like -DocProps("Title") and just
provides the error #NAME?
Can someone urgently help here please???
Thanks

 

[Steve Yandl]

Derek,

You can create user defined functions to extract a number of document
properties. For example the example below presents the workbook's creation
date:

__________________________________

Function CreationDate()
dteDate = ThisWorkbook.BuiltinDocumentProperties(11).Value
CreationDate = Format$(dteDate, "mmm d, yyyy H:mm am/pm")
End Function
__________________________________

There are a number of items under BuiltInDocumentProperties beside item "11"
the Creation Date. Item 1 is Title, item 7 is Last Author, item 12 is Last
Save Time. If you're not sure the property has been assigned, you should
use an 'On Error Resume Next' to avoid an error message.

Steve

 

[Gord Dibben]

DocProps is a User Defined Function.

Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

To use it as you describe, it must be stored in a general module in your
workbook or in an add-in.

To use it from your Personal.xls you prepend the formula as such.........

=Personal.xls!DocProps("Title")


Gord Dibben MS Excel MVP

 

[Guest]

Thanks but I obviously didn't explain myself well.

On File>Properties are the properties added for the document.

I need to capture this information in specific cells in Worksheet.

How can I accomplish this???

Derek

 

[Gord Dibben]

Derek

Try this macro to get a list of documeny properties.

Sub documentprops()
'list of properties on a new sheet
rw = 1
Worksheets.Add
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
rw = rw + 1
Next
End Sub

Then in B1 enter =DocProps(A1)

See my first post for the UDF.

Some of the properties will return 0 or #VALUE!


Gord