File Archive Utility

Many companies have a “free zone” or area of space usually accessible by a network share or FTP. This area is used for temporary space for transferring or sharing files. I say temporary, because this area gets cleaned out (or at least should be cleaned out) every so often.

The problem with these areas is they are not cleaned out when they should be and the space fills up quickly causing complaints by either the IT department because space is getting wasted or by users not being able to use the space. Some companies have created batch files to delete all files in these free zones. This batch file is then scheduled to run at a specific reoccurring time, usually weekly. This is a brute force method and really doesn’t afford the users much time to work in this area.

A better way to deal with this would be to write a Visual Basic Script (VBS) that searches a specified directory and takes action on a file based on modified time. This way, you can set the script to check files not modified past a certain threshold, like 30 days. This will give the user plenty of time to use the space effectively while not causing frustration. System Administrators will also be happy because this space will be cleaned up automatically since this script will be scheduled to run at specified times.

Now for the obligatory statement. I do not take responsibility for this script. Use at your own risk. There, I said it, so no complaining if you accidentally delete all your files.

To start of lets define the variables that will be configurable. The first variable we need to define is:

strRootFolder = “e:\ftp”
strLogFilePath = “c:\scripts”
arrExclusions(0) = “e:\ftp\excluded folder 1”
arrExclusions(1) = “e:\ftp\excluded folder 2”
intExpiration = 30
strArchive = “e:\archive”

The first variable listed above is strRootFolder. This folder defines the root folder to scan. The next variable is strLogFilePath. This variable defines the log file directory. The next 2 variables arrExclusions(0) and arrExclusions(1) define any folders that you want to exclude from the scan. Any subfolders of these exclusions will not be scanned as well. The variables are defined as an array so as to make it easier to add and remove exclusions without having to modify a bunch of code. The next variable intExpiration sets the expiration in days for the files. This expiration is calculated from the difference of the last modified date of the file and the number of days before a file is considered expired. The last configurable variable is strArchive. This variable defines the folder to move the expired files to.

Now for the non configurable variables.

strSerial = Year(Date) & "_" & Day(Date) & "_" & Month(Date) & "." & _
hour(time) & "_" & minute(time) & "_" & second(time)

This variable creates a serial number based on date and time. This is used for various functions in the script.

We also need to set where the log file name with the following:

strLog = strLogFilePath & "\FileExpirationUtility." & strSerial & ".log"

The name of the file is set and serialized with the strSerial variable we set earlier so that multiple log files can exist in the same directory with no name conflicts.

Now we need to create some objects:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objRootFolder = objFSO.GetFolder(strRootFolder)

The objFSO creates a file system object. This is used to create and write to the log file, and perform the functions of this script. The objRootFolder object sets the root folder that we defined earlier as an object so we can perform several functions on it later in the script.

Now on to the meat of the script. Before any folders are scanned we need to create a log file so we can always look back after the script has run to verify what took place. To do that we need to create a log object and we do this with the following line:

Set objLog = objFSO.CreateTextFile(strLog, True)

This uses the file system object that was created earlier to create a basic text file. To start our log file we have the following lines:

objLog.WriteLine "File expiration delete utility script started: " & NOW
objLog.WriteLine ""
objLog.WriteLine "Files older than " & intExpiration & _
" days will be moved to " & strArchive
objLog.WriteLine ""
For intI = LBound(arrExclusions) to UBound(arrExclusions)
objLog.WriteLine "Folder Exclusion: " & VBTab & arrExclusions(intI)
Next
objLog.WriteLine ""
objLog.WriteLine "Date/Time" & vbTab & vbTab & "Function" & VbTab & _
"Description"
objLog.WriteLine "-------------------------------------------------------------"

The above lines gives basic information about the script including:

· When the script started
· The number of days before a file is considered expired
· The location where the files will be moved to
· The folder exclusions
· A tab formatted event log area

We are about to scan the root folder so we need to log this action with the following lines:

objLog.WriteLine NOW & vbTab & "Scanning" & VbTab & "Scanning the directory: " & objRootFolder
intFoldersProcessed = 1

The first line will follow the tab formatted table in the log file with a time stamp, the action being performed, in this case scanning, and the action details. The intFolderProcessed variable is a counter for the number of folders scanned. This information is included in the log file as well.

We are going to create a couple of sub procedures for the folder scanning process and the file scanning process. For the script to work properly we need to call these sub procedures with the following lines:

CALL subProcessFiles(objRootFolder)
CALL subGetSubFolders(objRootFolder)

So now on to the first sub procedure, the recursive folder lookup procedure or subGetSubFolders. In order for this procedure to work we need to pass a parent folder to scan. Initially this parent folder will be the root folder. After the root folder is processed each individual folder will be passed as a parent folder to check for sub folders.

Sub subGetSubFolders(strFolder)
intNoProcess = 0
For Each objSubFolder in strFolder.SubFolders
subErrorHandler
On Error Resume Next
For intI = LBound(arrExclusions) to UBound(arrExclusions)
strSubFolder = lcase(objSubFolder) & "\"
strExclusions = lcase(arrExclusions(intI)) & "\"
intSearch = InStr(strSubFolder, strExclusions)
If intSearch <> 0 Then
intNoProcess = 1
objLog.WriteLine NOW & VbTab & "Skipped" & VbTab & VbTab & "Skipped folder " & lcase(objSubFolder) & ". Folder is excluded."
intFoldersSkipped = intFoldersSkipped + 1
End If
Next
If intNoProcess = 0 Then
objLog.WriteLine NOW & vbTab & "Scanning" & VbTab & "Scanning the directory: " & objSubFolder
intFoldersProcessed = intFoldersProcessed + 1
CALL subProcessFiles(objSubFolder)
End If
subGetSubFolders objSubFolder
Next
End Sub

What this does as said before, is recursively looks up sub folders underneath the root folder defined in the beginning of the script. intNoProcess is a Boolean variable used to identify if a folder should be scanned or not. This variable needs to be reset during each iteration, that is why at the top the value is set to 0 which means to scan the folder.

The subError handler calls up a error handler. This was put in place for the specific instance of not enough permission on the folder to scan. The error will be logged and the script will continue to run despite this error.

The “For intl =” line starts the process to check to see if the folder is in the exclusion list. Remember, all sub folders are excluded as well. If the folder is found to be excluded a log entry will be written stating this information. If the folder is not excluded then the folder counter variable is incremented and the subProcessFiles sub procedure is called to scan the files in the folder.

For recursion to work, the line subGetSubFolders objSubFolder is extremely important. This will call the same subGetSubFolders sub procedure with the next parent folder to enumerate.

Now, we need to build the subProcessFiles sub procedure called in the above procedure.

Sub subProcessFiles(strFolderForProcessing)
Set objFolderForProcessing = objFSO.GetFolder(strFolderForProcessing)
Set colFiles = objFolderForProcessing.Files
For Each objFile in colFiles
Set objProcessingFile = objFSO.GetFile(objFile)
dtmFileDate = objProcessingFile.DateLastModified
intAge = DateDiff("D", dtmFileDate, Date)
If intAge > intExpiration then
intFiles = intFiles + 1
strArchiveFile = strArchive & "\" & objProcessingFile.Name & "." & Year(Date) & "_" & _
Day(Date) & "_" & Month(Date) & "." & hour(time) & "_" & _
minute(time) & "_" & second(time) & "." & intFiles
objProcessingFile.Copy(strArchiveFile)
objLog.WriteLine NOW & vbTab & "Archiving" & VbTab & objFile & " (" & dtmFileDate & _
") moved to " & strArchiveFile
objProcessingFile.Delete True
End If
Next
End Sub

The folder to be scanned is passed to this procedure via the strFolderForProcessing variable. We need to deal with each file as an object. So the 2nd line defines the folder that is being scanned as an object. Then we get the collection of files within the folder object with the colFiles variable. We then set each file as an object and look at the difference between the last modified date and the intExpiration variable set in the beginning in this script. If the age is found to be older than the number of days set, the intFiles incremental integer is increased (This has 2 purposes. First, is for renaming the file. In order to make sure there are no duplicate file names in the archive directory this number is used. Also, this number is used for logging to show how many files were moved.) The new file name is set using the strArchiveFile, which includes the original file name, a date and time stamp, and an incremental file number. The file is then copied to the archive location with the new name. Then a log entry is made. Then the original file is deleted.

Now we need to create the subErrorHandler as referenced by the subGetSubFolders procedure.

Sub subErrorHandler
If Err.Number <> 0 Then
intErrors = intErrors + 1
objLog.WriteLine NOW & VbTab & "Error" & VbTab & VbTab & Err.Number & ":" & Err.Description
Err.Clear
End If
End Sub

This checks to see if there are any errors and if so will log the error with the description. There is also an incremental counter to track the number of errors.

There are only a few more lines that we need to include to finish up the log file.

objLog.WriteLine ""
objLog.WriteLine "-------------------------------------------------------------"
objLog.WriteLine "Archived " & intFiles & " files."
objLog.WriteLine "Processed " & intFoldersProcessed & " folders."
objLog.WriteLine "Skipped " & intFoldersSkipped & " folders."
objLog.WriteLine “Found “ & intErrors & “ errors.”
objLog.WriteLine "File expiration delete utility script ended: " & NOW


The lines above give the following information to the bottom of the log file:

· Number of archived files
· Number of folders scanned or processed
· Number of skipped folders due to exclusions
· Number of errors encountered as caught by the error handler
· The finish time of the script

This script gives the basis for some really interesting scripts. Here are a few ideas that this script can be modified with:

· Remove old folders
· Delete the files directly without moving them to an archive location

Have fun and let me know what you think.