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

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

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

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

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
news:1CB6D255-5507-471A-95EF-(E-Mail Removed)...
> 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 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---
>>
>> --
>> 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

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


--
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
> news:1CB6D255-5507-471A-95EF-(E-Mail Removed)...
> > 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 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---
> >>
> >> --
> >> 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
>
>

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, 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---
>
>
> --
> 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
> > news:1CB6D255-5507-471A-95EF-(E-Mail Removed)...
> > > 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 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---
> > >>
> > >> --
> > >> 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
> >
> >

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