Date Modified and By Whom

B

BillCPA

I have a script that cycles through folders/subfolders on a network drive and
lists all files (Excel, Word, etc.) in a workbook. Is there a way to capture
the 'Last Author' and 'Last Save Time' on all types of files? Without
opening the file?
 
R

ryguy7272

I can't see your code, so I'm just going to make a best guess here:
Module1:
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

Module2:
Sub Dates()

'Do Until ActiveCell = ""
For X = 1 To 1
Dim redRng As Range

Set redRng = Range("A1", Range("A100").End(xlUp))
For Each cell In redRng

If cell.Value <> "" Then
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=DocProps(""last author"")"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=DocProps(""last save time"")"
ActiveCell.Offset(1, -3).Select
End If

Next cell

Next X
'Loop

End Sub

Fiddle around with those Offsets; I just put names in ColumnC and time/date
in ColumnD.

You may want to format those time/date cells like this:
m/d/yyyy h:mm


HTH,
Ryan---
 
B

BillCPA

That will work for Excel. I was hoping for some way to just look at a file
on the drive and pull the last user name and last date used, for any type of
file (.xls, .doc, .mdb, .txt, etc.), and with having to open the file.
 
R

Rick Rothstein

The operating system (at least Windows) does not store either of those items
with the file name.
 
B

BillCPA

I did find part of what I needed - '.datelastmodified' will give you the date
the file was last changed. That was mainly what I wanted.

There probably isn't any way to find the last user, since that wouldn't
necessarily be relevant to directory information (altho Frontpage does track
that in its directory).

Thanks for all your suggestions - you always learn something from the ideas
people submit.
 
R

Rick Rothstein

I did find part of what I needed - '.datelastmodified' will give you the
date
the file was last changed. That was mainly what I wanted.
Click to expand...

In that case, look up the FileDateTime Function in VB's help file... you can
then get the last modified date directly, without using scripting.
There probably isn't any way to find the last user, since that wouldn't
necessarily be relevant to directory information (altho Frontpage does
track
that in its directory).
Click to expand...

Correct.
 
Top