Date Modified and By Whom

  • Thread starter Thread starter BillCPA
  • Start date Start date
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?
 
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---
 
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.
 
The operating system (at least Windows) does not store either of those items
with the file name.
 
Hummm, I’m running out of ideas. The only other thing I can think of is this:
http://www.rondebruin.nl/copy4.htm

Go to the section named:
Change cells or range in one or all worksheets in each file

Copy the code, but you don’t copy the text that is red!
Replace red text with the code I gave you earlier today; that will operate
on Files in the Folder that you specify (obviously, you need to change things
such as this… MyPath = "C:\Users\Ron\test"). That is a batch processing
macro. I have used it many times, all with great success. Look here for
more on batch processing:
http://en.wikipedia.org/wiki/Batch_processing

So, will it work? In theory, yes. Again, I haven’t see your code, so I
don’t know. Personally, I think you know what you are doing. I think you
can figure it out from here. ;)

Good luck,
Ryan---
 
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.
 
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.
 
Back
Top