Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to sort colFiles by date motified? 1

Status
Not open for further replies.
Richardkl

Richardkl

Technical User
Dec 8, 2001
40
0
0
US
How to can I sort the colFiles by date motified so that the oldest is the first one in the list?

I have created a vb script that deletes files that are older than 30 days and leaves at least 30 files in the folder. But I found a flaw in the logic, the colFiles sorts by default to the "name" field and that causes a problem that the first file in the list can be the newest file (these are backup files that auto name). Even though it is greater than 30 days old, the others are older, it will be the one deleted with the present script. I feel that I don't need to even check how old the file is if I can get the files in "colfiles" sorted to the oldest file to be the first in the list, I can just delete files until there are only 30 left, ANY ideas?

NumberOfDaysOld = 30
NumberOfFilesToKeep = 30
strPath = "d:\qb"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
Set colFiles = objFolder.Files
For Each objFile In colFiles
filecount = objFolder.Files.Count
If objFile.DateLastModified > (Date - NumberOfDaysOld) And filecount > NumberOfFilesToKeep Then
objFile.Delete
End If
Next

End Sub
 
Sort by date Sort by votes
I would just add the date and name for each file to an arraylist. Then use the builtin arraylist sort and go through the arraylist deleting according to your criteria.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]
 
Upvote 0 Downvote
We do something similar just by naming the backup files with numeric dates at the beginning so that they are naturally loaded in that order.

20080312_backup, for example, would be todays backup file.

I am not aware of any way to sort files in an fso object other than the default.
 
Upvote 0 Downvote
I would just add the date and name for each file to an arraylist. Then use the builtin arraylist sort and go through the arraylist deleting according to your criteria"

Ok, How do you program the creation of the date and name into an arraylist from the folder? Where is the builtin arraylist sort?

 
Upvote 0 Downvote
The arraylist is a .Net object that is exposed via COM, so the first question is whether or not the machine has .Net installed?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]
 
Upvote 0 Downvote
Once upon a time, using disconnected recordset to do the job is a good instance of making disparate technologies combined to do a job. It remains a good example but much more known among scripters.

You can check out this more recent thread with my intervention among others probably earlier that I lost track.

The same approach now appeared in some ms articles which is the reason why it is more well-known. A gentle explanation of all the detail can be seen in this "hey" article.
 
Upvote 0 Downvote
Have a look here:
faq329-3362

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Disconnected RS works just as well as an arraylist if you want to go that way too.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]
 
Upvote 0 Downvote
EBGreen, just to know:
how do you use .Net arraylist in VBScript ?
 
Upvote 0 Downvote
Dim alFoo
Dim nItem

Set alFoo = CreateObject("System.Collections.ArrayList")

alFoo.Add 5
alFoo.Add 4
alFoo.Add 3
alFoo.Add 2
alFoo.Add 1
alFoo.Add 0

For Each nItem In alFoo
WScript.Echo nItem
Next

alFoo.Sort()
WScript.Echo ""
WScript.Echo "SORTED ASCENDING:"

For Each nItem In alFoo
WScript.Echo nItem
Next

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]
 
Upvote 0 Downvote
Thanks.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thanks all for your help and insite to the different solutions and added to my knowledge base.

I thought about it and instead of doing a sort, I decided to go with find the oldest and delete that item and loop based upon number of files to be deleted.

I still have a question, is there a way to pass the variables "NumberOfFilesToKeep" and "strpath" from a command prompt? like "deletefile.vbs 10 , d:\qb" ?

I did it with the following script;

Dim NumberOfFilesToKeep, NumberOfLoops, TotalFiles, strpath, NLoops
NumberOfFilesToKeep = 10
strpath = "d:\qb"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strpath)
Set colFiles = objFolder.Files
TotalFiles = objFolder.Files.Count
NumberOfLoops = (TotalFiles - NumberOfFilesToKeep)
Do While NLoops < NumberOfLoops
OldestDate = Date
For Each objFile In colFiles
If OldestDate > objFile.DateLastModified Then
OldestDate = objFile.DateLastModified
End If
Next
For Each objFile In colFiles
filecount = objFolder.Files.Count
If objFile.DateLastModified = OldestDate And filecount > NumberOfFilesToKeep Then
objFile.Delete
End If
next
NLoops=NLoops+1
loop
 
Upvote 0 Downvote
Can the ArrayList sort on a specified field within the data? For example, in a recordset you can define your fields and sort on any one you desire. I would assume that the ArrayList could do this using a multi-dimensional array but I have not seen anything like this. Is this something that the ArrayList can handle?

Swi
 
Upvote 0 Downvote
If you want to sort based on a field in the data then a disconnected recordset would probably be better.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]
 
Upvote 0 Downvote
I'm also interested in learning how to sort a file by the creation date using vbscript. I've been reading about the disconnected recordset and can't get it to work with my vbscript. Here is a sample of my code:

Code: 
Sub CheckFolder(objCurrentFolder,objLogFile,State,Yr,Evt,Ship, Header)
	Const adBSTR = 8
	Const adDBDate = 133
	Const MaxCharacters = 255
	
	Set DataList = CreateObject("ADOR.Recordset")
	
	With DataList
	 Set .ActiveConnection=Nothing
	  .CursorLocation = adUseClient
	  .LockType = adLockBatch.Optimistic
	 With .Fields
	    .Append "FileName", adBSTR, MaxCharacters
	    .Append "ProfileDate", adDBDate
	 End With
	  .Open
	End With
	
   	Dim strSearch
   	Dim strStYr
   	Dim strYr
   	  
	GetState = State
	GetYr = Yr
	GetEvent = Evt
	GetShip = Ship	
	
For Each objFile In objCurrentFolder.Files	  
              	  
	   	strStYr=Mid(objFile.Name,6,4) 
	   	strSearch = strStYr & strEvent 
	   	strSearch2 = strStYr & strShip & strEvent
     
	  If GetShip = "" Then
	    
	   If strSearch=GetState & GetYr & GetEvent  Then
	           intCount = intCount + 1
	          
	         DataList.AddNew
	         DataList("FileName").Value = Cstr(objFile.name)
	         DataList("ProfileDate").Value = Cstr(objFile.datecreated)
	         DataList.Update  
	         
			DataList.Sort = "ProfileDate DESC"
			DataList.MoveFirst		
			
		   	  'Alternating Row Colors
		   	   If intCount Mod 2 = 0 Then
		   	   	 strOutput = "<table>" & "<tr bgcolor=#CAE1FF>"  & "<td>" & "TSA:" & _
		   	 	 Chr(32) & Chr(32) & "<A href=" & _
		   	 	 DateList & ">" & "</td>" & "</tr>" & "</table>" & Chr(13)
     End If	      
  
	Next
	
	
	'Recurse Through all of the folders 
	For each objNewFolder In objCurrentFolder.subFolders
	CheckFolder objNewFolder, objLogFile, State, Yr, Evt, Ship, false
		
	Next
 
Upvote 0 Downvote
What you posted is syntactically invalid:


Sub CheckFolder(objCurrentFolder,objLogFile,State,Yr,Evt,Ship, Header)
Const adBSTR = 8
Const adDBDate = 133
Const MaxCharacters = 255

Set DataList = CreateObject("ADOR.Recordset")

[red]'I have never been a fan of nested With statements[/red]
With DataList
Set .ActiveConnection=Nothing
.CursorLocation = adUseClient
.LockType = adLockBatch.Optimistic
With .Fields
.Append "FileName", adBSTR, MaxCharacters
.Append "ProfileDate", adDBDate
End With
.Open
End With

Dim strSearch
Dim strStYr
Dim strYr

GetState = State
GetYr = Yr
GetEvent = Evt
GetShip = Ship

For Each objFile In objCurrentFolder.Files

strStYr=Mid(objFile.Name,6,4)
strSearch = strStYr & strEvent
strSearch2 = strStYr & strShip & strEvent

If GetShip = "" Then

If strSearch=GetState & GetYr & GetEvent Then
intCount = intCount + 1

DataList.AddNew
DataList("FileName").Value = Cstr(objFile.name)
DataList("ProfileDate").Value = Cstr(objFile.datecreated)
DataList.Update

DataList.Sort = "ProfileDate DESC"
DataList.MoveFirst

'Alternating Row Colors
If intCount Mod 2 = 0 Then
strOutput = "<table>" & "<tr bgcolor=#CAE1FF>" & "<td>" & "TSA:" & _
Chr(32) & Chr(32) & "<A href=" & _
DateList & ">" & "</td>" & "</tr>" & "</table>" & Chr(13)
End If
[red]End If 'MISSING END IF[/red]
[red]End If'MISSING END IF[/red]
Next


'Recurse Through all of the folders
For each objNewFolder In objCurrentFolder.subFolders
CheckFolder objNewFolder, objLogFile, State, Yr, Evt, Ship, False
Next
[red]End Sub'MISSING END SUB[/red]



[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]
 
Upvote 0 Downvote
Sorry. I emlinated some of the code to try and shorten it.

Code: 
Sub CheckFolder(objCurrentFolder,objLogFile,State,Yr,Evt,Ship, Header)
	Const adBSTR = 8
	Const adDBDate = 133
	Const MaxCharacters = 255
	
	Set DataList = CreateObject("ADOR.Recordset")
	
	With DataList
	 Set .ActiveConnection=Nothing
	  .CursorLocation = adUseClient
	  .LockType = adLockBatch.Optimistic
	 With .Fields
	    .Append "FileName", adBSTR, MaxCharacters
	    .Append "ProfileDate", adDBDate
	 End With
	  .Open
	End With
	
   	Dim strSearch
   	Dim strSearch2
   	Dim strStYr
   	Dim strYr
   	Dim strShip
   	Dim strEvent
   	Dim strOutput
   	Dim strOutput1
   	Dim objNewFolder
   	Dim objFile
   	Dim objStream
   	Dim objShell	
   	Dim Getyr
        Dim GetShip
        Dim GetEvent	
        Dim EvtName
        Dim intCount
     
	GetState = State
	GetYr = Yr
	GetEvent = Evt
	GetShip = Ship
	
	For Each objFile In objCurrentFolder.Files	  
      
         	strEvent = Mid(objFile.Name,13,1)
	   	strStYr=Mid(objFile.Name,6,4) 
	   	strSearch = strStYr & strEvent 
	   	strSearch2 = strStYr & strShip & strEvent
     
	  If GetShip = "" Then
	    
		   If strSearch=GetState & GetYr & GetEvent  Then
	           intCount = intCount + 1
	          
	         DataList.AddNew
	         DataList("FileName").Value = Cstr(objFile.name)
	         DataList("ProfileDate").Value = Cstr(objFile.datecreated)
	         DataList.Update  
	         
			DataList.Sort = "ProfileDate DESC"
			DataList.MoveFirst		
			
		   	  'Alternating Row Colors
		   	   If intCount Mod 2 = 0 Then
		   	   	 strOutput = "<table>" & "<tr bgcolor=#CAE1FF>"  & "<td>" & "TSA:" & _
		   	 	 Chr(32) & Chr(32) & "<A href=" & _
		   	 	 DateList & ">" & "</td>" & "</tr>" & "</table>" & Chr(13)
		   	  If intCount Mod 2 = 0 Then
		   	   	 strOutput = "<table>" & "<tr bgcolor=#CAE1FF>"  & "<td>" & "TSA:" & _
		   	 	 Chr(32) & Chr(32) & "<A href=" & _
		   	 	CStr(objFile.Path) & ">" &  CStr(objFile.Path) & "</A>" & Chr(32) & _
		   	 	 "CREATED:" & Chr(32) & CStr(objFile.datecreated) & "</td>" & "</tr>" & "</table>" & Chr(13) 	  
				  
				  objLogFile.writeline strOutput
			  Else
			  	 strOutput = "<title>Search Results</title>" & "<table>" & "<tr bgcolor=#C1FFB1>"  & "<td>" & "TSA:" & _
		   	 	 Chr(32) & Chr(32) & "<A href=" & _
		   	 	CStr(objFile.Path) & ">" &  CStr(objFile.Path) & "</A>" & Chr(32) & _
		   	 	 "CREATED:" & Chr(32) & CStr(objFile.datecreated) & "</td>" & "</tr>" & "</table>" & Chr(13)	  
				  objLogFile.writeline strOutput
				  	
		      End If 
		      
		   End If
		 
		Else
			If strSearch2= GetState & GetYr & GetShip & GetEvent Then  
	           				   	  
			  	 strOutput = "<title>Search Results</title>" & "<table>" & "<tr bgcolor=#C1FFB1>"  & "<td>" & "TSA:" & _
		   	 	 Chr(32) & Chr(32) & "<A href=" & _
		   	 	 CStr(objFile.Path) & ">" &  CStr(objFile.Path) & "</A>" & Chr(32) & _
		   	 	 "CREATED:" & Chr(32) & CStr(objFile.datecreated) & "</td>" & "</tr>" & "</table>" & Chr(13)	  
				  objLogFile.writeline strOutput
			end If	  	
		        
  
     End If	      
  
	Next
				
	'Recurse Through all of the folders 
	For each objNewFolder In objCurrentFolder.subFolders
	CheckFolder objNewFolder, objLogFile, State, Yr, Evt, Ship, false
		
	Next
	
End Sub
 
Upvote 0 Downvote
strSearch2 = strStYr & strShip & strEvent
Where is strShip initialized ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Updated code.
Code: 
Sub CheckFolder(objCurrentFolder,objLogFile,State,Yr,Evt,Ship, Header)
    Const adBSTR = 8
    Const adDBDate = 133
    Const MaxCharacters = 255
    
    Set DataList = CreateObject("ADOR.Recordset")
    
    With DataList
     Set .ActiveConnection=Nothing
      .CursorLocation = adUseClient
      .LockType = adLockBatch.Optimistic
     With .Fields
        .Append "FileName", adBSTR, MaxCharacters
        .Append "ProfileDate", adDBDate
     End With
      .Open
    End With
    
       Dim strSearch
       Dim strSearch2
       Dim strStYr
       Dim strYr
       Dim strShip
       Dim strEvent
       Dim strOutput
       Dim strOutput1
       Dim objNewFolder
       Dim objFile
       Dim objStream
       Dim objShell    
       Dim Getyr
        Dim GetShip
        Dim GetEvent    
        Dim EvtName
        Dim intCount
     
    GetState = State
    GetYr = Yr
    GetEvent = Evt
    GetShip = Ship
    
    For Each objFile In objCurrentFolder.Files      
        [b]strShip =  Mid(objFile.Name,10,3)[/b]
           strEvent = Mid(objFile.Name,13,1)
           strStYr=Mid(objFile.Name,6,4) 
           strSearch = strStYr & strEvent 
           strSearch2 = strStYr & strShip & strEvent
     
      If GetShip = "" Then
        
           If strSearch=GetState & GetYr & GetEvent  Then
               intCount = intCount + 1
              
             DataList.AddNew
             DataList("FileName").Value = Cstr(objFile.name)
             DataList("ProfileDate").Value = Cstr(objFile.datecreated)
             DataList.Update  
             
            DataList.Sort = "ProfileDate DESC"
            DataList.MoveFirst        
            
                 'Alternating Row Colors
                  If intCount Mod 2 = 0 Then
                       strOutput = "<table>" & "<tr bgcolor=#CAE1FF>"  & "<td>" & "TSA:" & _
                     Chr(32) & Chr(32) & "<A href=" & _
                     DateList & ">" & "</td>" & "</tr>" & "</table>" & Chr(13)
                 If intCount Mod 2 = 0 Then
                       strOutput = "<table>" & "<tr bgcolor=#CAE1FF>"  & "<td>" & "TSA:" & _
                     Chr(32) & Chr(32) & "<A href=" & _
                    CStr(objFile.Path) & ">" &  CStr(objFile.Path) & "</A>" & Chr(32) & _
                     "CREATED:" & Chr(32) & CStr(objFile.datecreated) & "</td>" & "</tr>" & "</table>" & Chr(13)       
                  
                  objLogFile.writeline strOutput
              Else
                   strOutput = "<title>Search Results</title>" & "<table>" & "<tr bgcolor=#C1FFB1>"  & "<td>" & "TSA:" & _
                     Chr(32) & Chr(32) & "<A href=" & _
                    CStr(objFile.Path) & ">" &  CStr(objFile.Path) & "</A>" & Chr(32) & _
                     "CREATED:" & Chr(32) & CStr(objFile.datecreated) & "</td>" & "</tr>" & "</table>" & Chr(13)      
                  objLogFile.writeline strOutput
                      
              End If 
              
           End If
         
        Else
            If strSearch2= GetState & GetYr & GetShip & GetEvent Then  
                                        
                   strOutput = "<title>Search Results</title>" & "<table>" & "<tr bgcolor=#C1FFB1>"  & "<td>" & "TSA:" & _
                     Chr(32) & Chr(32) & "<A href=" & _
                     CStr(objFile.Path) & ">" &  CStr(objFile.Path) & "</A>" & Chr(32) & _
                     "CREATED:" & Chr(32) & CStr(objFile.datecreated) & "</td>" & "</tr>" & "</table>" & Chr(13)      
                  objLogFile.writeline strOutput
            end If          
                
  
     End If          
  
    Next
                
    'Recurse Through all of the folders 
    For each objNewFolder In objCurrentFolder.subFolders
    CheckFolder objNewFolder, objLogFile, State, Yr, Evt, Ship, false
        
    Next
    
End Sub
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

lunter
  • Locked
  • Question
How to create vbArray+vbByte (8209) in *.vbs stript
Replies
0
Views
88
lunter
lunter
Don Child
  • Locked
  • Question
How to validate an INSERT, after Executing
Replies
1
Views
99
Don Child
Don Child
Brianfree
  • Locked
  • Question
Excel Rows to Column 1
Replies
2
Views
135
Brianfree
Brianfree
stable-x
  • Locked
  • Question
How to set/define a variable from two variables?
Replies
3
Views
133
stable-x
stable-x
PiotrMP006
  • Locked
  • Question
How to quickly check if a file has 1 or more lines of text?
Replies
1
Views
102
combo
combo

Part and Inventory Search

Sponsor

Back
Top