Accessing Worksheet Properties

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

Can someone tell me how to take the document property,
Date Modified, and display it in a cell? It seems as
though this should be much easier, but I'm probably
missing something.

Any help greatly appreciated! Thanks!
Michelle
 
Hi Michelle
try the following user defined function:

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

Now you can use the following formula in one of your cells:
=DocProps("Last save time")
 
Try this example

Run it on a empty sheet

Sub test()
rw = 1
Worksheets(1).Activate
For Each p In ActiveWorkbook.BuiltinDocumentProperties
On Error Resume Next
Cells(rw, 1).Value = p.Name
Cells(rw, 2).Value = p.Value
rw = rw + 1
On Error GoTo 0
Next
End Sub


Or this

With ActiveWorkbook.BuiltinDocumentProperties
Cells(1, 1).Value = .Item("Last save time")
Cells(2, 1).Value = .Item("Subject")
Cells(3, 1).Value = .Item("Manager")
Cells(4, 1).Value = .Item("Author")
Cells(5, 1).Value = .Item("Keywords")
Cells(6, 1).Value = .Item("Comments")
Cells(7, 1).Value = .Item("Company")
End With
 
You can see all of the values you can pass to this function by looking at
BuiltinDocumentProperties Property in VBA Help

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top