Last saved by

[JohnUK]

Is there a way of entering into cells (below each other)
1. The name of the file
2. Last person who saved the file
3. Last person accessed the file
4. Date and time last saved?
Help greatly appreciated
John

 

[Chip Pearson]

You can get the name of the file with

ThisWorkbook.Name 'or
ThisWorkbook.FullName.

You can get the original author of the file with

ThisWorkbook.BuiltInDocumentProperties("Author").Value

You can get the last-saved-by user with

ThisWorkbook.BuiltInDocumentProperties("Last Author").Value

These two values return the name that is entered as the "User Name" in
the Options dialog box. This is not the user's logon id.

You can get the last save time with

FileDateTime(ThisWorkbook.FullName)

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gary''s Student]

For item #3, use:

Environ("username")

 

[JohnUK]

Many thanks Chip, but I am not sure how I would enter that into a code so
that it would paste those values into the cells of a worksheet.
John

 

[JLGWhiz]

You enter it into a worksheet by making a variable equal to the return value
of the built in properties:

userName = ThisWorkbook.BuiltInDocumentProperties("Author").Value
ActiveSheet.ActiveCell = userName

 

[JohnUK]

Sorry - I am at a loss with this one

You enter it into a worksheet by making a variable equal to the return value
of the built in properties:

userName = ThisWorkbook.BuiltInDocumentProperties("Author").Value
ActiveSheet.ActiveCell = userName

 

[JLGWhiz]

I'll pass this along for reference anyhow. I got it on the en.allexperts.com
site.

Remarks
This property returns the entire collection of built-in document properties.
Use the Item method to return a single member of the collection (a
DocumentProperty object) by specifying either the name of the property or the
collection index (as a number).

You can refer to document properties either by index value or by name. The
following list shows the available built-in document property names:

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

Hyperlink Base

Number of Characters (with spaces)



Container applications aren't required to define values for every built-in
document property. If Microsoft Excel doesn't define a value for one of the
built-in document properties, reading the Value property for that document
property causes an error.

 

[Chip Pearson]

The code I posted was VBA code, not formula code. You need to create a
User Defined Function in VBA. Press ALT F11 to open the VBA Editor
(VBE). There, press CTRL R to display the Project Explorer window if
it is not already visible (typically on the left side of the screen).
Find your project workbook in the Project Explorer, select it, then go
to the Insert menu and choose Module. This will create a code module
named Module1 in your workbook's VBA Project. In that module, enter
the following code:


Function GetProp(PropName As String, _
Optional Reference As Excel.Range) As Variant
On Error GoTo ErrH:
Dim DocProps As Office.DocumentProperties
Dim WB As Excel.Workbook
If Reference Is Nothing Then
Set WB = ThisWorkbook
Else
Set WB = Reference.Parent.Parent
End If
Set DocProps = _
WB.BuiltinDocumentProperties
GetProp = CStr(DocProps(PropName).Value)
Exit Function
ErrH:
GetProp = CVErr(xlErrValue)
End Function


Now, close the VBE.

Back in Excel, you can call this function from a cell with a function
like

=GetProp("Author")

Change "Author" to the name of the property whose value you want to
return. If the property doesn't exist or isn't set, the function
returns a #VALUE error. If Reference is not specified, the property is
read from the workbook that contains the code. To look up a property
in another (open) workbook, pass a reference to that workbook as the
Reference. For example, to get the Author property of Book3.xls, use

=GetProp("Author",[Book3.xls]Sheet1!A1)

It doesn't matter what reference you pass to GetProp as long as it
refers to a cell in the workbook whose properties you want to
retrieve.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)