I would like to create a user defined function (UDF), to return the file save
date, the user name who saved the file, and if the file is open for editing
the user name who is editing the file for a series of files that are
summarized on a sheet in a separate summary workbook. The only input is the
filename. The other files are in the current directory right now, but I would
like the option of entering the full path so that this function can report on
files that are in other directories as well.

Basically I will enter the filename in column A and the modifed date, etc,
is returned in column B.

For example:

A1 :FileName.XLS
B1 : =LastSave(A1)

UDF formula returns - Modified Date: 10/28/08 3:00pm Saved by: NOTSO

OR If FileName.XLS does not exist in the current directory, then UDF
returns "FileName.XLS NOT Found"

OR If FileName.XLS is open or in use by another user, then UDF returns
the last date modified with a note appended; for example *** Modified Date:
10/28/08 3:00pm Saved by: NOTSO (currently being edited by USERNAME)

Thank you, NOTSO

Try to adapt this;
(example is from Excel Help)

Sub ShowFileAccessInfo(filespec)
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = f.Name & " on Drive " & UCase(f.Drive) & vbCrLf
s = s & "Short Name: " & f.ShortName & vbCrLf
s = s & "Created: " & f.DateCreated & vbCrLf
s = s & "Parent Folder: " & f.ParentFolder & vbCrLf
s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf
s = s & "Last Modified: " & f.DateLastModified & vbCrLf
s = s & "Type: " & f.Type & vbCrLf
s = s & "File Size: " & f.Size & vbCrLf
MsgBox s, 0, "File Access Info"
End Sub

Sub t()
Call ShowFileAccessInfo("C:\Documents and
Settings\Sheeloo\Desktop\Test\Book1.xls")
End Sub

"notso" wrote:

I would like to create a user defined function (UDF), to return the file save
date, the user name who saved the file, and if the file is open for editing
the user name who is editing the file for a series of files that are
summarized on a sheet in a separate summary workbook. The only input is the
filename. The other files are in the current directory right now, but I would
like the option of entering the full path so that this function can report on
files that are in other directories as well.

Basically I will enter the filename in column A and the modifed date, etc,
is returned in column B.

For example:

A1 :FileName.XLS
B1 : =LastSave(A1)

UDF formula returns - Modified Date: 10/28/08 3:00pm Saved by: NOTSO

OR If FileName.XLS does not exist in the current directory, then UDF
returns "FileName.XLS NOT Found"

OR If FileName.XLS is open or in use by another user, then UDF returns
the last date modified with a note appended; for example *** Modified Date:
10/28/08 3:00pm Saved by: NOTSO (currently being edited by USERNAME)

Thank you, NOTSO