Deleting Excel Files with Multiple Conditions

[94mustang]

Excel VBA Programmers,


I have this procedure working but I think it is a bit sloppy. Within the If-Then statement nested inside the For Each-Next loop, you see the minus one (-1).I essentially want only the number of characters in the file name itself.In other words, if the name of the file is “Test9876.xls”, I only want the value to return the value of 8.Is there a better command or better way to write the three conditions altogether in the If-Then construct??

[FONT=Arial][SIZE=3][COLOR=#000000]Option Explicit[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]Sub DeleteFilesXDaysOld()[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]‘[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]‘ DeleteFiles Macro[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]‘ This macro will delete files that meet the following criteria:[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]‘1. Number of characters in file name equals 27[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]‘2. Must have the extension of “.xls” and[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]‘3. File must be older than 14 days.[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]‘[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]‘ Keyboard Shortcut: Ctrl+Shift+D[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]‘[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]Dim FSO[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]Dim objFile[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]Dim Directory[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]Dim DirTarget[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]Dim FileExtension[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]Set FSO = CreateObject("Scripting.FileSystemObject")[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]Set Directory = FSO.GetFolder("\\server01\Examples\RemoveFilesHere")[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]Set DirTarget = Directory.Files[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]FileExtension = ".xls"[/COLOR][/SIZE][/FONT]
  
[FONT=Arial][SIZE=3][COLOR=#000000]For Each objFile In DirTarget[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]     If Len(objFile) - Len(Directory) - Len(FileExtension) - 1 = 27 _[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]          And FileExtension = (".xls") _[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]          And DateDiff("D", objFile.DateLastModified, Now) > 14 _[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]          Then objFile.Delete[/COLOR][/SIZE][/FONT]
 [FONT=Arial][SIZE=3][COLOR=#000000]Next[/COLOR][/SIZE][/FONT]
[FONT=Arial][COLOR=#000000]End Sub[/COLOR][/FONT]

 

[AlphaFrog]

FileExtension = ".xls"
'
'
'
And FileExtension = (".xls") _

You set the variable FileExtension as a string. The If condition is not testing the objFile file extension. It's testing the variable.

I didn't understand your file naming convention and your question about the 8.

 

[94mustang]

AlphaFrog,

I managed to find pieces of code from the internet and was able to construct this one. This is my first time working with files and working with the FileSystemObject. I need help writing this code so that it can be understood by others. Not that it will necessarily be reviewed by others but from the standpoint of simply just well written code.

The value of 8 from “Test9876.xls” is simply the number of characters without the “.xls”. Technically, I suppose I can count the number of characters including the “.xls”. Certainly my naming convention does need help. I have written a procedure to make backup copies of a certain file. After these backup files are created, I want to delete the ones older than 14 days and also match the other criteria. I just do not want files that are of a different file type or does not meet the number of characters (in the name only) to be deleted.

What is in the file name is the name of the file itself followed by the date (mmddyy) and time (hhmmss). An example would be “Process_Audits102214_152035.xls”. How could this code be re-written to filter for the three conditions?

I hope this is making more sense. If not, please ask more questions because this is a learning process for me at the moment. Thanks for your previous response.

 

[AlphaFrog]

An example would be “Process_Audits102214_152035.xls”. How could this code be re-written to filter for the three conditions?

Maybe use the Like operator for two of the three conditions

If objFile.Name Like "Process_Audits######_######.xls" And _
DateDiff("D", objFile.DateLastModified, Now) > 14 Then objFile.Delete


Otherwise, your code looks pretty good. You could add comments to help explain the code if you like.

 

[94mustang]

AlphaFrog,

Thanks for this new command that I knew nothing about. That is very helpful. I will add comments to this code. Would it be helpful to add comments on the declarations? Is that where you were confused initially?

 

[AlphaFrog]

AlphaFrog,

Thanks for this new command that I knew nothing about. That is very helpful. I will add comments to this code. Would it be helpful to add comments on the declarations? Is that where you were confused initially?

You're welcome.
I just didn't understand your initial question. Commenting the declarations could be useful but its not what confused me.

Here's your code shortened a bit more and a message added when complete.

[color=darkblue]Sub[/color] DeleteFilesXDaysOld()
[color=green]'[/color]
[color=green]' DeleteFiles Macro[/color]
[color=green]' This macro will delete "\\server01\Examples\RemoveFilesHere" files that meet the following criteria:[/color]
[color=green]'1. File name pattern "Process_Audits######_######.xls"[/color]
[color=green]'2. File must be older than 14 days.[/color]
[color=green]'[/color]
[color=green]' Display deleted files message when done[/color]
[color=green]'[/color]
[color=green]' Keyboard Shortcut: Ctrl+Shift+D[/color]
[color=green]'[/color]
 [color=darkblue]Dim[/color] objFile [color=darkblue]As[/color] Scripting.File  [color=green]' "File system object" file[/color]
 [color=darkblue]Dim[/color] DelFileCounter [color=darkblue]As[/color] [color=darkblue]Long[/color]     [color=green]' Deleted files counter[/color]
    
 [color=darkblue]For[/color] [color=darkblue]Each[/color] objFile [color=darkblue]In[/color] CreateObject("Scripting.FileSystemObject").GetFolder("\\server01\Examples\RemoveFilesHere").Files
    [color=darkblue]If[/color] objFile.Name [color=darkblue]Like[/color] "Process_Audits######_######.xls" And _
       DateDiff("D", objFile.[color=darkblue]Date[/color]LastModified, Date) > 14 [color=darkblue]Then[/color]
            objFile.Delete
            DelFileCounter = DelFileCounter + 1
    [color=darkblue]End[/color] [color=darkblue]If[/color]
 [color=darkblue]Next[/color] objFile
 
 MsgBox DelFileCounter & " files deleted. ", vbInformation, "Delete Old Files Complete"
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[94mustang]

AlphaFrog,

I have incorporated the shorten code you have shown me above because the more concise it is, the faster it will run. I appreciate your patience and teaching. I understand what you have done and think I can apply it in other situations. Are there any really good resources that have all the commands and/or how to use them for VBA? I consider this post to be closed and again thanks for your willingness to help an eager learner.

 

[AlphaFrog]

AlphaFrog,

I have incorporated the shorten code you have shown me above because the more concise it is, the faster it will run. I appreciate your patience and teaching. I understand what you have done and think I can apply it in other situations. Are there any really good resources that have all the commands and/or how to use them for VBA? I consider this post to be closed and again thanks for your willingness to help an eager learner.

You're welcome.

Concise <> Speed

Teaching resources

Tip for searching this site; Use most any search engine with this syntax...
Search term(s) here Site:www.mrexcel.com


FYI:
I own a 2014 Mustang 3.7L Deep Impact Blue (I should have gotten the 5.0L)

 

[94mustang]

I appreciate the resource help. I am always looking for good resource material to learn new code.

FYI:
I still own my 1994 Mustang 5.0L Deep Forest Green. I would say now it is Sun Baked Green. I think it can be considered a classic at this point. It has over 300K miles. It needs an "Extreme Makeover: Car Edition (all cosmetic). Engine still runs great and thankfully no major issues. Does burn oil a little. I've had to change clutches twice: one about 110K and the other around 200K. It is due another one soon but I hope it can hang on just a little bit longer. You could say that it is my "old Betsy". I'm glad Ford brought back the 5.0L a few years back. I think you made a great choice. You will enjoy it in years to come.