"Properties" Information Extraction

[JohnS]

How can I extract data from Properties (like Last Saved By and display it
in a cell?

 

[Kevin B]

You can use the UDF do insert the Last Author in a cell:

Press Alt+F11, click INSERT on the MENU and select MODULE.

In the module, copy the text below from the line that starts with function
through the line that say "End Function"

Function Properties()

Dim strPropVal As String

Properties = ThisWorkbook. _
BuiltinDocumentProperties( _
"Last Author").Value

End Function

Use it as you would any function by type = PROPERTIES() in the cell you want
the result in and press enter.

For a complete list of properties, double click the keyword
"BuiltinDocumentProperties" whiile in the VBE and press F1.

Hope this helps...

 

[JohnS]

Kevin - Many thanks for this help. I'll try it later tonight.

JohnS

 

[Gord Dibben]

You will need 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

Enter in a cell one of the below...............

'=DOCPROPS("author")
'or
'=DOCPROPS("last save time")
'or
'DOCPROPS("creation date")

For a list of all built-in Document Proerties, run this macro after copying the
DocProps function to a general module in your workbook.

Paste the macro into the same module.

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
Cells(rw, 4).Value = "=DocProps(" & "A" & rw & ")"
rw = rw + 1
Next
End Sub


Gord Dibben MS Excel MVP