Date Modified and By Whom
 

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?

--
Bill @ UAMS

Date Modified and By Whom
 

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"BillCPA" wrote:

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?

--
Bill @ UAMS

Date Modified and By Whom
 

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.

--
Bill @ UAMS


"ryguy7272" wrote:

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"BillCPA" wrote:

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?

--
Bill @ UAMS

Date Modified and By Whom
 

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

--
Rick (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
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.

--
Bill @ UAMS


"ryguy7272" wrote:

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

--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


"BillCPA" wrote:

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?

--
Bill @ UAMS

Date Modified and By Whom
 

Hummm, Im 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 dont 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 havent see your code, so I
dont know. Personally, I think you know what you are doing. I think you
can figure it out from here. ;)

Good luck,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
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.

--
Bill @ UAMS


"ryguy7272" wrote:

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

--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


"BillCPA" wrote:

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?

--
Bill @ UAMS

Date Modified and By Whom
 

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.

--
Bill @ UAMS


"ryguy7272" wrote:

Hummm, Im 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 dont 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 havent see your code, so I
dont know. Personally, I think you know what you are doing. I think you
can figure it out from here. ;)

Good luck,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
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.

--
Bill @ UAMS


"ryguy7272" wrote:

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

--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


"BillCPA" wrote:

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?

--
Bill @ UAMS

Date Modified and By Whom
 

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.

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).

Correct.

--
Rick (MVP - Excel)