94mustang
Board Regular
- Joined
- Dec 13, 2011
- Messages
- 133
- Office Version
- 365
- 2019
- Platform
- Windows
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??
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??
Code:
[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]