Find newest file in a series of folders

Hello All,

I need to create a VB script that will start in a folder(Team) then walk
the tree and find the newest file based on date modified in each
folder(Project and it's subfolders) The goal here is to provide each team
with a list of their Projects and the latest activity on each project to
help them decide which projects are ready to be archived off our main
server.

Team
Project1
Project2
Project 3

Return results to a .txt file that would look like:

Team Project Newest File
Harry Project1 10/17/2005
Harry Project2 11/25/2006


I'm looking for some Code ideas as well as concepts. I've never been very
good at creating efficient algorithms / searches. Each Project may have up
to 10k files and subfolders 10 + deep.



Thanks in advance



Steve

ekkehard
Fri Sep 07 13:51:21 PDT 2007

Stever schrieb:
> Hello All,
>
> I need to create a VB script that will start in a folder(Team) then walk
> the tree and find the newest file based on date modified in each
> folder(Project and it's subfolders) The goal here is to provide each team
> with a list of their Projects and the latest activity on each project to
> help them decide which projects are ready to be archived off our main
> server.
>
> Team
> Project1
> Project2
> Project 3
>
> Return results to a .txt file that would look like:
>
> Team Project Newest File
> Harry Project1 10/17/2005
> Harry Project2 11/25/2006
>
>
> I'm looking for some Code ideas as well as concepts. I've never been very
> good at creating efficient algorithms / searches. Each Project may have up
> to 10k files and subfolders 10 + deep.
>
> Thanks in advance
>
> Steve
You may try this:

' Needed for ADO
Const adDBTimeStamp = 135 ' 00000087
Const adVarWChar = 202 ' 000000CA
Const adAffectAll = 3 ' 00000003

Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
' Check: your starting directory
Dim sRoot : sRoot = oFS.GetAbsolutePathName( "c:\Programme" )
Dim sTeam : sTeam = oFS.GetFileName( sRoot )
Dim oWSH : Set oWSH = CreateObject( "WScript.Shell" )
' Check: dir cmd to list files recursively ordered by timestamp descending
Dim sCmd : sCmd = "%comspec% /c dir @D@ /s /o:-d"
Dim nWidth : nWidth = 0
Dim reDir : Set reDir = New RegExp
reDir.Global = True
reDir.Multiline = True
' Check: format of this and parent dir
reDir.Pattern = "^.+?<DIR>.+?$"
Dim reFile : Set reFile = New RegExp
reFile.Global = True
reFile.Multiline = True
' Check: German "Verzeichnis", Date format
reFile.Pattern = "^\s+Verzeichnis\svon\s(.+)$" _
+ "(?:\r\n)+\s+((\d{2}.\d{2}.\d{4})\s+" _
+ "(\d{2}:\d{2})\s+[^ ]+\s" _
+ "(.*))$"
' Recordset to collect all newest files from all subdirs
Dim oRS : Set oRS = CreateObject( "ADODB.Recordset" )
oRS.Fields.Append "DT" , adDBTimeStamp
oRS.Fields.Append "FSpec", adVarWChar, 255
oRS.Open

WScript.Echo "Starting in", sRoot, sTeam
Dim oSubDir, sProj
For Each oSubDir In oFS.GetFolder( sRoot ).Subfolders
sProj = oSubDir.Name
If nWidth < Len( sProj ) Then nWidth = Len( sProj )
Next
For Each oSubDir In oFS.GetFolder( sRoot ).Subfolders
sProj = oSubDir.Name
Dim oExec : Set oExec = oWSH.Exec( Replace( sCmd, "@D@", oSubDir.Path ) )
Dim sTxt : sTxt = oExec.Stdout.ReadAll
' delete "..<DIR>.." lines
sTxt = reDir.Replace( sTxt, "" )

Dim oMTS : Set oMTS = reFile.Execute( sTxt )
If 0 < oMTS.Count Then
Dim oMT
For Each oMT in oMTS
oRS.AddNew
oRS( "DT" ) = CDate( oMT.SubMatches( 2 ) + " " + oMT.SubMatches( 3 ) )
oRS( "FSpec" ) = oMT.Submatches( 0 ) + "\" + oMT.SubMatches( 4 )
oRS.Update
Next
oRS.Sort = "DT"
oRS.MoveLast
WScript.Echo sTeam, Left( sProj + Space( nWidth ), nWidth ), oRS( "DT" ).Value,
oRS( "FSpec" ).Value
' oRS.Delete adAffectAll ' Operation is not allowed in this context.
Do Until oRS.BOF
oRS.Delete
oRS.MovePrevious
Loop
End If
Next

The idea is to use the dir command to get all files from the project
directories sorted by datetime desc, then cutting the first (newest)
file from each subdir using a RegExp, putting these files into an
ADO Recordset, sort that and so getting the newest file from all subdirs.

The script needs some work to change the Germanisms ("Verzeichnis", Date
format) and testing, but I hope it will get you started.

John
Fri Sep 07 16:14:59 PDT 2007


"ekkehard.horner" <ekkehard.horner@arcor.de> wrote in message news:46e1b9cb$0$16116$9b4e6d93@newsspool1.arcor-online.net...
> Stever schrieb:
>> Hello All,
>>
>> I need to create a VB script that will start in a folder(Team) then walk the tree and find the newest file based on date
>> modified in each folder(Project and it's subfolders) The goal here is to provide each team with a list of their Projects and
>> the latest activity on each project to help them decide which projects are ready to be archived off our main server.
>>
>> Team
>> Project1
>> Project2
>> Project 3
>>
>> Return results to a .txt file that would look like:
>>
>> Team Project Newest File
>> Harry Project1 10/17/2005
>> Harry Project2 11/25/2006
>>
>>
>> I'm looking for some Code ideas as well as concepts. I've never been very good at creating efficient algorithms / searches.
>> Each Project may have up to 10k files and subfolders 10 + deep.
>>
>> Thanks in advance
>>
>> Steve
> You may try this:
>
> ' Needed for ADO
> Const adDBTimeStamp = 135 ' 00000087
> Const adVarWChar = 202 ' 000000CA
> Const adAffectAll = 3 ' 00000003
>
> Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
> ' Check: your starting directory
> Dim sRoot : sRoot = oFS.GetAbsolutePathName( "c:\Programme" )
> Dim sTeam : sTeam = oFS.GetFileName( sRoot )
> Dim oWSH : Set oWSH = CreateObject( "WScript.Shell" )
> ' Check: dir cmd to list files recursively ordered by timestamp descending
> Dim sCmd : sCmd = "%comspec% /c dir @D@ /s /o:-d"
> Dim nWidth : nWidth = 0
> Dim reDir : Set reDir = New RegExp
> reDir.Global = True
> reDir.Multiline = True
> ' Check: format of this and parent dir
> reDir.Pattern = "^.+?<DIR>.+?$"
> Dim reFile : Set reFile = New RegExp
> reFile.Global = True
> reFile.Multiline = True
> ' Check: German "Verzeichnis", Date format
> reFile.Pattern = "^\s+Verzeichnis\svon\s(.+)$" _
> + "(?:\r\n)+\s+((\d{2}.\d{2}.\d{4})\s+" _
> + "(\d{2}:\d{2})\s+[^ ]+\s" _
> + "(.*))$"
> ' Recordset to collect all newest files from all subdirs
> Dim oRS : Set oRS = CreateObject( "ADODB.Recordset" )
> oRS.Fields.Append "DT" , adDBTimeStamp
> oRS.Fields.Append "FSpec", adVarWChar, 255
> oRS.Open
>
> WScript.Echo "Starting in", sRoot, sTeam
> Dim oSubDir, sProj
> For Each oSubDir In oFS.GetFolder( sRoot ).Subfolders
> sProj = oSubDir.Name
> If nWidth < Len( sProj ) Then nWidth = Len( sProj )
> Next
> For Each oSubDir In oFS.GetFolder( sRoot ).Subfolders
> sProj = oSubDir.Name
> Dim oExec : Set oExec = oWSH.Exec( Replace( sCmd, "@D@", oSubDir.Path ) )
> Dim sTxt : sTxt = oExec.Stdout.ReadAll
> ' delete "..<DIR>.." lines
> sTxt = reDir.Replace( sTxt, "" )
>
> Dim oMTS : Set oMTS = reFile.Execute( sTxt )
> If 0 < oMTS.Count Then
> Dim oMT
> For Each oMT in oMTS
> oRS.AddNew
> oRS( "DT" ) = CDate( oMT.SubMatches( 2 ) + " " + oMT.SubMatches( 3 ) )
> oRS( "FSpec" ) = oMT.Submatches( 0 ) + "\" + oMT.SubMatches( 4 )
> oRS.Update
> Next
> oRS.Sort = "DT"
> oRS.MoveLast
> WScript.Echo sTeam, Left( sProj + Space( nWidth ), nWidth ), oRS( "DT" ).Value, oRS( "FSpec" ).Value
> ' oRS.Delete adAffectAll ' Operation is not allowed in this context.
> Do Until oRS.BOF
> oRS.Delete
> oRS.MovePrevious
> Loop
> End If
> Next
>
> The idea is to use the dir command to get all files from the project
> directories sorted by datetime desc, then cutting the first (newest)
> file from each subdir using a RegExp, putting these files into an
> ADO Recordset, sort that and so getting the newest file from all subdirs.
>
> The script needs some work to change the Germanisms ("Verzeichnis", Date
> format) and testing, but I hope it will get you started.

That's an interesting and efficient technique, though it can be made slightly faster by specifying /a-d on the dir command to omit
directories from the listings. Thus change:

Dim sCmd : sCmd = "%comspec% /c dir @D@ /s /o:-d"

to:

Dim sCmd : sCmd = "%comspec% /c dir @D@ /s /o:-d /a-d"

and then all the statements involving reDir can be deleted.

Stever
Fri Sep 07 16:41:00 PDT 2007

Ok i'm stumped....

I left the Greman date format because I don't know what to put in it's
place.

No matter what my test directories look like as far as files or no files,
the program never executes the code inside If 0 < oMTS.Count Then. I put
WScript.Echo oMTS.Count just before the IF and the oMTS count is always 0.

Ideas?

Thanks again

Steve



"ekkehard.horner" <ekkehard.horner@arcor.de> wrote in message
news:46e1b9cb$0$16116$9b4e6d93@newsspool1.arcor-online.net...
> Stever schrieb:
>> Hello All,
>>
>> I need to create a VB script that will start in a folder(Team) then walk
>> the tree and find the newest file based on date modified in each
>> folder(Project and it's subfolders) The goal here is to provide each team
>> with a list of their Projects and the latest activity on each project to
>> help them decide which projects are ready to be archived off our main
>> server.
>>
>> Team
>> Project1
>> Project2
>> Project 3
>>
>> Return results to a .txt file that would look like:
>>
>> Team Project Newest File
>> Harry Project1 10/17/2005
>> Harry Project2 11/25/2006
>>
>>
>> I'm looking for some Code ideas as well as concepts. I've never been very
>> good at creating efficient algorithms / searches. Each Project may have
>> up to 10k files and subfolders 10 + deep.
>>
>> Thanks in advance
>>
>> Steve
> You may try this:
>
> ' Needed for ADO
> Const adDBTimeStamp = 135 ' 00000087
> Const adVarWChar = 202 ' 000000CA
> Const adAffectAll = 3 ' 00000003
>
> Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
> ' Check: your starting directory
> Dim sRoot : sRoot = oFS.GetAbsolutePathName( "c:\Programme" )
> Dim sTeam : sTeam = oFS.GetFileName( sRoot )
> Dim oWSH : Set oWSH = CreateObject( "WScript.Shell" )
> ' Check: dir cmd to list files recursively ordered by timestamp descending
> Dim sCmd : sCmd = "%comspec% /c dir @D@ /s /o:-d"
> Dim nWidth : nWidth = 0
> Dim reDir : Set reDir = New RegExp
> reDir.Global = True
> reDir.Multiline = True
> ' Check: format of this and parent dir
> reDir.Pattern = "^.+?<DIR>.+?$"
> Dim reFile : Set reFile = New RegExp
> reFile.Global = True
> reFile.Multiline = True
> ' Check: German "Verzeichnis", Date format
> reFile.Pattern = "^\s+Verzeichnis\svon\s(.+)$" _
> + "(?:\r\n)+\s+((\d{2}.\d{2}.\d{4})\s+" _
> + "(\d{2}:\d{2})\s+[^ ]+\s" _
> + "(.*))$"
> ' Recordset to collect all newest files from all subdirs
> Dim oRS : Set oRS = CreateObject( "ADODB.Recordset" )
> oRS.Fields.Append "DT" , adDBTimeStamp
> oRS.Fields.Append "FSpec", adVarWChar, 255
> oRS.Open
>
> WScript.Echo "Starting in", sRoot, sTeam
> Dim oSubDir, sProj
> For Each oSubDir In oFS.GetFolder( sRoot ).Subfolders
> sProj = oSubDir.Name
> If nWidth < Len( sProj ) Then nWidth = Len( sProj )
> Next
> For Each oSubDir In oFS.GetFolder( sRoot ).Subfolders
> sProj = oSubDir.Name
> Dim oExec : Set oExec = oWSH.Exec( Replace( sCmd, "@D@",
> oSubDir.Path ) )
> Dim sTxt : sTxt = oExec.Stdout.ReadAll
> ' delete "..<DIR>.." lines
> sTxt = reDir.Replace( sTxt, "" )
>
> Dim oMTS : Set oMTS = reFile.Execute( sTxt )
> If 0 < oMTS.Count Then
> Dim oMT
> For Each oMT in oMTS
> oRS.AddNew
> oRS( "DT" ) = CDate( oMT.SubMatches( 2 ) + " " +
> oMT.SubMatches( 3 ) )
> oRS( "FSpec" ) = oMT.Submatches( 0 ) + "\" +
> oMT.SubMatches( 4 )
> oRS.Update
> Next
> oRS.Sort = "DT"
> oRS.MoveLast
> WScript.Echo sTeam, Left( sProj + Space( nWidth ), nWidth ),
> oRS( "DT" ).Value, oRS( "FSpec" ).Value
> ' oRS.Delete adAffectAll ' Operation is not allowed in this
> context.
> Do Until oRS.BOF
> oRS.Delete
> oRS.MovePrevious
> Loop
> End If
> Next
>
> The idea is to use the dir command to get all files from the project
> directories sorted by datetime desc, then cutting the first (newest)
> file from each subdir using a RegExp, putting these files into an
> ADO Recordset, sort that and so getting the newest file from all subdirs.
>
> The script needs some work to change the Germanisms ("Verzeichnis", Date
> format) and testing, but I hope it will get you started.

John
Fri Sep 07 17:01:22 PDT 2007


"Stever" <Stever@hotmail.com> wrote in message news:OjlAVia8HHA.4612@TK2MSFTNGP03.phx.gbl...
> Ok i'm stumped....
>
> I left the Greman date format because I don't know what to put in it's place.
>
> No matter what my test directories look like as far as files or no files, the program never executes the code inside If 0 <
> oMTS.Count Then. I put WScript.Echo oMTS.Count just before the IF and the oMTS count is always 0.
>
> Ideas?
>
> Thanks again
>
> Steve
>

Have you changed:

reFile.Pattern = "^\s+Verzeichnis\svon\s(.+)$" _

to:

reFile.Pattern = "^\s+Directory\sof\s(.+)$" _

Stever
Fri Sep 07 17:39:23 PDT 2007

Thanks that helped. Now I am getting an error Wrong number of arguments or
invalid property assignment:'Value' on this piece of code.

oRS( "FSpec" ).Value
' oRS.Delete adAffectAll ' Operation is not allowed in this context.
Do Until oRS.BOF
oRS.Delete
oRS.MovePrevious
Loop
End If
Next

"John W" <johnawilliamsaesquire@agmail.com> wrote in message
news:zImdndBF5ObOe3zbnZ2dneKdnZydnZ2d@pipex.net...
>
> "Stever" <Stever@hotmail.com> wrote in message
> news:OjlAVia8HHA.4612@TK2MSFTNGP03.phx.gbl...
>> Ok i'm stumped....
>>
>> I left the Greman date format because I don't know what to put in it's
>> place.
>>
>> No matter what my test directories look like as far as files or no files,
>> the program never executes the code inside If 0 < oMTS.Count Then. I put
>> WScript.Echo oMTS.Count just before the IF and the oMTS count is always
>> 0.
>>
>> Ideas?
>>
>> Thanks again
>>
>> Steve
>>
>
> Have you changed:
>
> reFile.Pattern = "^\s+Verzeichnis\svon\s(.+)$" _
>
> to:
>
> reFile.Pattern = "^\s+Directory\sof\s(.+)$" _
>
>

John
Fri Sep 07 18:03:23 PDT 2007


"Stever" <Stever@hotmail.com> wrote in message news:%23YcAEDb8HHA.2208@TK2MSFTNGP06.phx.gbl...
> Thanks that helped. Now I am getting an error Wrong number of arguments or invalid property assignment:'Value' on this piece of
> code.
>
> oRS( "FSpec" ).Value
> ' oRS.Delete adAffectAll ' Operation is not allowed in this context.
> Do Until oRS.BOF
> oRS.Delete
> oRS.MovePrevious
> Loop
> End If
> Next
>

The oRS("FSpec").Value should be part of the preceding Wscript.echo line.

> "John W" <johnawilliamsaesquire@agmail.com> wrote in message news:zImdndBF5ObOe3zbnZ2dneKdnZydnZ2d@pipex.net...
>>
>> "Stever" <Stever@hotmail.com> wrote in message news:OjlAVia8HHA.4612@TK2MSFTNGP03.phx.gbl...
>>> Ok i'm stumped....
>>>
>>> I left the Greman date format because I don't know what to put in it's place.
>>>
>>> No matter what my test directories look like as far as files or no files, the program never executes the code inside If 0 <
>>> oMTS.Count Then. I put WScript.Echo oMTS.Count just before the IF and the oMTS count is always 0.
>>>
>>> Ideas?
>>>
>>> Thanks again
>>>
>>> Steve
>>>
>>
>> Have you changed:
>>
>> reFile.Pattern = "^\s+Verzeichnis\svon\s(.+)$" _
>>
>> to:
>>
>> reFile.Pattern = "^\s+Directory\sof\s(.+)$" _
>>
>>
>
>

John
Fri Sep 07 18:16:40 PDT 2007


"John W" <johnawilliamsaesquire@agmail.com> wrote in message news:K-qdne9RT5fpRnzbnZ2dneKdnZydnZ2d@pipex.net...
>
> "ekkehard.horner" <ekkehard.horner@arcor.de> wrote in message news:46e1b9cb$0$16116$9b4e6d93@newsspool1.arcor-online.net...
>> Stever schrieb:
>>> Hello All,
>>>
>>> I need to create a VB script that will start in a folder(Team) then walk the tree and find the newest file based on date
>>> modified in each folder(Project and it's subfolders) The goal here is to provide each team with a list of their Projects and
>>> the latest activity on each project to help them decide which projects are ready to be archived off our main server.
>>>
>>> Team
>>> Project1
>>> Project2
>>> Project 3
>>>
>>> Return results to a .txt file that would look like:
>>>
>>> Team Project Newest File
>>> Harry Project1 10/17/2005
>>> Harry Project2 11/25/2006
>>>
>>>
>>> I'm looking for some Code ideas as well as concepts. I've never been very good at creating efficient algorithms / searches.
>>> Each Project may have up to 10k files and subfolders 10 + deep.
>>>
>>> Thanks in advance
>>>
>>> Steve
>> You may try this:
>>
>> ' Needed for ADO
>> Const adDBTimeStamp = 135 ' 00000087
>> Const adVarWChar = 202 ' 000000CA
>> Const adAffectAll = 3 ' 00000003
>>
>> Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
>> ' Check: your starting directory
>> Dim sRoot : sRoot = oFS.GetAbsolutePathName( "c:\Programme" )
>> Dim sTeam : sTeam = oFS.GetFileName( sRoot )
>> Dim oWSH : Set oWSH = CreateObject( "WScript.Shell" )
>> ' Check: dir cmd to list files recursively ordered by timestamp descending
>> Dim sCmd : sCmd = "%comspec% /c dir @D@ /s /o:-d"
>> Dim nWidth : nWidth = 0
>> Dim reDir : Set reDir = New RegExp
>> reDir.Global = True
>> reDir.Multiline = True
>> ' Check: format of this and parent dir
>> reDir.Pattern = "^.+?<DIR>.+?$"
>> Dim reFile : Set reFile = New RegExp
>> reFile.Global = True
>> reFile.Multiline = True
>> ' Check: German "Verzeichnis", Date format
>> reFile.Pattern = "^\s+Verzeichnis\svon\s(.+)$" _
>> + "(?:\r\n)+\s+((\d{2}.\d{2}.\d{4})\s+" _
>> + "(\d{2}:\d{2})\s+[^ ]+\s" _
>> + "(.*))$"
>> ' Recordset to collect all newest files from all subdirs
>> Dim oRS : Set oRS = CreateObject( "ADODB.Recordset" )
>> oRS.Fields.Append "DT" , adDBTimeStamp
>> oRS.Fields.Append "FSpec", adVarWChar, 255
>> oRS.Open
>>
>> WScript.Echo "Starting in", sRoot, sTeam
>> Dim oSubDir, sProj
>> For Each oSubDir In oFS.GetFolder( sRoot ).Subfolders
>> sProj = oSubDir.Name
>> If nWidth < Len( sProj ) Then nWidth = Len( sProj )
>> Next
>> For Each oSubDir In oFS.GetFolder( sRoot ).Subfolders
>> sProj = oSubDir.Name
>> Dim oExec : Set oExec = oWSH.Exec( Replace( sCmd, "@D@", oSubDir.Path ) )
>> Dim sTxt : sTxt = oExec.Stdout.ReadAll
>> ' delete "..<DIR>.." lines
>> sTxt = reDir.Replace( sTxt, "" )
>>
>> Dim oMTS : Set oMTS = reFile.Execute( sTxt )
>> If 0 < oMTS.Count Then
>> Dim oMT
>> For Each oMT in oMTS
>> oRS.AddNew
>> oRS( "DT" ) = CDate( oMT.SubMatches( 2 ) + " " + oMT.SubMatches( 3 ) )
>> oRS( "FSpec" ) = oMT.Submatches( 0 ) + "\" + oMT.SubMatches( 4 )
>> oRS.Update
>> Next
>> oRS.Sort = "DT"
>> oRS.MoveLast
>> WScript.Echo sTeam, Left( sProj + Space( nWidth ), nWidth ), oRS( "DT" ).Value, oRS( "FSpec" ).Value
>> ' oRS.Delete adAffectAll ' Operation is not allowed in this context.
>> Do Until oRS.BOF
>> oRS.Delete
>> oRS.MovePrevious
>> Loop
>> End If
>> Next
>>
>> The idea is to use the dir command to get all files from the project
>> directories sorted by datetime desc, then cutting the first (newest)
>> file from each subdir using a RegExp, putting these files into an
>> ADO Recordset, sort that and so getting the newest file from all subdirs.
>>
>> The script needs some work to change the Germanisms ("Verzeichnis", Date
>> format) and testing, but I hope it will get you started.
>
> That's an interesting and efficient technique, though it can be made slightly faster by specifying /a-d on the dir command to
> omit directories from the listings. Thus change:
>
> Dim sCmd : sCmd = "%comspec% /c dir @D@ /s /o:-d"
>
> to:
>
> Dim sCmd : sCmd = "%comspec% /c dir @D@ /s /o:-d /a-d"
>
> and then all the statements involving reDir can be deleted.
>

And I've found a bug. A project-level folder is omitted if its name contains spaces. This can be fixed by surrounding it with
quotes:

Dim oExec : Set oExec = oWSH.Exec( Replace( sCmd, "@D@", Chr(34) & oSubDir.Path & Chr(34)) )

Stever
Fri Sep 07 18:49:05 PDT 2007

I'm a little embarrassed that I didn't catch that one. Tunnel vision!

Thanks!

"John W" <johnawilliamsaesquire@agmail.com> wrote in message
news:h_ydnZdVcbFGaXzbnZ2dnUVZ8v6dnZ2d@pipex.net...
>
> "Stever" <Stever@hotmail.com> wrote in message
> news:%23YcAEDb8HHA.2208@TK2MSFTNGP06.phx.gbl...
>> Thanks that helped. Now I am getting an error Wrong number of arguments
>> or invalid property assignment:'Value' on this piece of code.
>>
>> oRS( "FSpec" ).Value
>> ' oRS.Delete adAffectAll ' Operation is not allowed in this
>> context.
>> Do Until oRS.BOF
>> oRS.Delete
>> oRS.MovePrevious
>> Loop
>> End If
>> Next
>>
>
> The oRS("FSpec").Value should be part of the preceding Wscript.echo line.
>
>> "John W" <johnawilliamsaesquire@agmail.com> wrote in message
>> news:zImdndBF5ObOe3zbnZ2dneKdnZydnZ2d@pipex.net...
>>>
>>> "Stever" <Stever@hotmail.com> wrote in message
>>> news:OjlAVia8HHA.4612@TK2MSFTNGP03.phx.gbl...
>>>> Ok i'm stumped....
>>>>
>>>> I left the Greman date format because I don't know what to put in it's
>>>> place.
>>>>
>>>> No matter what my test directories look like as far as files or no
>>>> files, the program never executes the code inside If 0 < oMTS.Count
>>>> Then. I put WScript.Echo oMTS.Count just before the IF and the oMTS
>>>> count is always 0.
>>>>
>>>> Ideas?
>>>>
>>>> Thanks again
>>>>
>>>> Steve
>>>>
>>>
>>> Have you changed:
>>>
>>> reFile.Pattern = "^\s+Verzeichnis\svon\s(.+)$" _
>>>
>>> to:
>>>
>>> reFile.Pattern = "^\s+Directory\sof\s(.+)$" _
>>>
>>>
>>
>>
>
>

Stever
Fri Sep 07 19:00:17 PDT 2007

"Stever" <Stever@hotmail.com> wrote in message
news:emeaTxX8HHA.4736@TK2MSFTNGP06.phx.gbl...
> Hello All,
>
> I need to create a VB script that will start in a folder(Team) then walk
> the tree and find the newest file based on date modified in each
> folder(Project and it's subfolders) The goal here is to provide each team
> with a list of their Projects and the latest activity on each project to
> help them decide which projects are ready to be archived off our main
> server.
>
> Team
> Project1
> Project2
> Project 3
>
> Return results to a .txt file that would look like:
>
> Team Project Newest File
> Harry Project1 10/17/2005
> Harry Project2 11/25/2006
>
>
> I'm looking for some Code ideas as well as concepts. I've never been very
> good at creating efficient algorithms / searches. Each Project may have up
> to 10k files and subfolders 10 + deep.
>
>
>
> Thanks in advance
>
>
>
> Steve
>

Almost there.... This is only going 1 subfolder deep in the tree.
Unfortunately my users like to burry things pretty deep in the tree. Also is
there a way to put all of the returned dates into yet another record set and
sort that by date? That would complete the goal of returning just the
Project plus the date of the newest file in it. I realize that I am takign
advantage of your time but... do you have any quick fixes?

Team
Project1 Works
Sub1 Works
Sub2 Not working.txt 02/10/2001
Sub3 Not working.

Project2 Works
Sub1 Works
Sub2 Not working.txt 01/20/2007
Sub3 Not working.

Desired Output.

Project1 02/10/2001
Project2 01/20/2007


Thanks for your time

ekkehard
Sat Sep 08 01:22:57 PDT 2007

Stever schrieb:
> Hello All,
>
> I need to create a VB script that will start in a folder(Team) then walk
> the tree and find the newest file based on date modified in each
> folder(Project and it's subfolders) The goal here is to provide each team
> with a list of their Projects and the latest activity on each project to
> help them decide which projects are ready to be archived off our main
> server.
>
> Team
> Project1
> Project2
> Project 3
>
> Return results to a .txt file that would look like:
>
> Team Project Newest File
> Harry Project1 10/17/2005
> Harry Project2 11/25/2006
>
>
> I'm looking for some Code ideas as well as concepts. I've never been very
> good at creating efficient algorithms / searches. Each Project may have up
> to 10k files and subfolders 10 + deep.
>
>
>
> Thanks in advance
>
>
>
> Steve
>
>
Hi John,

thanks for your interest, the improvement, the bug slaying, and
doing support duty for me. I'm very grateful for that.

BTW, I incorporated the double quotes into the initialization
of sCmd:

sCmd = "%comspec% /c dir ""@D@"" /s /o:-d /a:-d"


Hi Stever,

drilling down the subfolders should be handled by the /s parameter
to the dir command. Please check if

dir <project directory> /s /o:-d /a:-d

from a command line picks up all subdirs with all files. If that's
the case, the Regexp must be checked. reFile is based on output like

Verzeichnis von C:\wis\_vbs\0506\dev\forum\challenge\s p a c e s

08.09.2007 09:43 4 log.txt

Ekkehard

John
Sun Sep 09 10:44:16 PDT 2007


"Stever" <Stever@hotmail.com> wrote in message news:OQVqXwb8HHA.2752@TK2MSFTNGP06.phx.gbl...
> "Stever" <Stever@hotmail.com> wrote in message news:emeaTxX8HHA.4736@TK2MSFTNGP06.phx.gbl...
>> Hello All,
>>
>> I need to create a VB script that will start in a folder(Team) then walk the tree and find the newest file based on date
>> modified in each folder(Project and it's subfolders) The goal here is to provide each team with a list of their Projects and
>> the latest activity on each project to help them decide which projects are ready to be archived off our main server.
>>
>> Team
>> Project1
>> Project2
>> Project 3
>>
>> Return results to a .txt file that would look like:
>>
>> Team Project Newest File
>> Harry Project1 10/17/2005
>> Harry Project2 11/25/2006
>>
>>
>> I'm looking for some Code ideas as well as concepts. I've never been very good at creating efficient algorithms / searches.
>> Each Project may have up to 10k files and subfolders 10 + deep.
>>
>>
>>
>> Thanks in advance
>>
>>
>>
>> Steve
>>
>
> Almost there.... This is only going 1 subfolder deep in the tree. Unfortunately my users like to burry things pretty deep in the
> tree. Also is there a way to put all of the returned dates into yet another record set and sort that by date? That would
> complete the goal of returning just the Project plus the date of the newest file in it. I realize that I am takign advantage
> of your time but... do you have any quick fixes?
>
> Team
> Project1 Works
> Sub1 Works
> Sub2 Not working.txt 02/10/2001
> Sub3 Not working.
>
> Project2 Works
> Sub1 Works
> Sub2 Not working.txt 01/20/2007
> Sub3 Not working.
>
> Desired Output.
>
> Project1 02/10/2001
> Project2 01/20/2007
>
>
> Thanks for your time
>


I used Ekkehard's idea of the dir command and a regular expression to find the newest file, and then an array and a sort routine,
rather than an ADO recordset. To run: script.vbs //nologo > results.txt


Option Explicit

Dim sStartFolder

sStartFolder = "C:\temp\"

Const ForWriting = 2
Dim dtNewestFile, nWidth, i
Dim aProjects() 'Dynamic array to hold projects and dates of newest file

Dim objFso, objFolder, objSubFolder
Dim oWSH : Set oWSH = CreateObject( "WScript.Shell" )
Dim sCmd : sCmd = "%comspec% /c dir /s /o:-d /a-d "

Dim reFile : Set reFile = New RegExp
reFile.Global = True
reFile.Multiline = True
reFile.Pattern = "^\s+Directory\sof\s(.+)$" _
+ "(?:\r\n)+\s+((\d{2}.\d{2}.\d{4})\s+" _
+ "(\d{2}:\d{2})\s+[^ ]+\s" _
+ "(.*))$"

Set objFso = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFso.GetFolder(sStartFolder)

'Process subfolders in start folder

Dim numProjects : numProjects = 0
For Each objSubFolder In objFolder.SubFolders

If Len(objSubFolder.Name) > nWidth Then nWidth = Len(objSubFolder.Name)

Dim oExec : Set oExec = oWSH.Exec( sCmd & Chr(34) & objSubFolder.Path & Chr(34) )
Dim sTxt : sTxt = oExec.Stdout.ReadAll

Dim oMTS : Set oMTS = reFile.Execute( sTxt )
If 0 < oMTS.Count Then
Dim oMT, dtThis
Dim dtNewest : dtNewest = CDate("01/01/1970")
For Each oMT In oMTS
dtThis = CDate( oMT.SubMatches( 2 ) + " " + oMT.SubMatches( 3 ) )
If dtThis > dtNewest Then dtNewest = dtThis
Next

'Add project and its date to array
ReDim Preserve aProjects(1,numProjects)
aProjects(0,numProjects) = objSubFolder.Name
aProjects(1,numProjects) = dtNewest
numProjects = numProjects + 1
End If

Next

'Sort and output

Insertion_Sort aProjects

WScript.Echo "Team", _
Left( "Project" & Space( nWidth ), nWidth ), _
"Newest File"

For i = 0 To UBound(aProjects,2)
WScript.Echo objFolder.Name, _
Left( aProjects(0,i) & Space( nWidth ), nWidth ), _
aProjects(1,i)
Next

'---------------------------------

Sub Insertion_Sort( aProjects )

'Sort aProjects array by descending date

Dim i, j, dtNewest, sProject

For i = 1 To UBound(aProjects,2)
dtNewest = aProjects(1,i)
sProject = aProjects(0,i)
j = i
Do While aProjects(1,j-1) < dtNewest
aProjects(1,j) = aProjects(1,j-1)
aProjects(0,j) = aProjects(0,j-1)
j = j - 1
If j = 0 Then Exit Do
Loop
aProjects(1,j) = dtNewest
aProjects(0,j) = sProject
Next

End Sub

Stever
Wed Sep 12 21:11:51 PDT 2007

This was a brilliant way to accomplish the task and is actually fairly fast.
I'm a bit of a novice at scripting so it took me a while to totally
understand the code. I have never seen Regular Expressions before so that
really threw me. I need to learn more about that. Sounds complex but
powerful. I am going to add some additional code to put the results into a
.mdb file so I can create reports. I will post my additions when I get it
polished and tested.

Thanks guys for the education!

Steve.