Sometimes, you may need to delete files from a folder, such as intermediate files created during report preparation.
In this tutorial, I will show you some examples of deleting files in a folder using VBA in Excel.
Note: I strongly recommend creating a data backup before using VBA to delete files, as this action is irreversible as it deletes files without sending them to the Recycle Bin. The backup will ensure your files remain available if you need them.
Delete a Specific File in a Folder
Suppose you have a ‘Sample Report.xlsx’ file in the ‘Reports’ folder on C drive. You can delete the file using the below subroutine:
Sub DeleteSpecificFileInFolder()
' Declares a string variable to hold the file path
Dim FilePath As String
' Sets the file path of the file to be deleted
FilePath = "C:\Reports\Sample Report.xlsx"
' Deletes the file at the specified file path
Kill FilePath
End SubThe above code deletes a specific file, in this case, an Excel file located at “C:\Reports\Sample Report.xlsx”.
It uses the ‘Kill’ statement to delete specified files from the folder.
This script is particularly useful for automated file management tasks where specific files need to be removed programmatically.
Also read: How to Delete a Sheet in Excel Using VBA
Delete All Files in a Folder
Assuming you want to delete all files in the ‘Furniture’ folder. You can use the following code to accomplish the task:
Sub DeleteAllFilesInFolder()
' Disables screen updating for efficiency
Application.ScreenUpdating = False
' Declares variables for file system operations and folder path
Dim FSO As Object
Dim FolderPath As String
Dim FileItem As Object
' Sets the folder path from which files will be deleted
FolderPath = "C:\Furniture\"
' Creates a FileSystemObject to handle file operations
Set FSO = CreateObject("Scripting.FileSystemObject")
' Iterates through each file in the specified folder and deletes it
For Each FileItem In FSO.GetFolder(FolderPath).Files
FSO.DeleteFile FileItem
Next FileItem
' Releases the FileSystemObject
Set FSO = Nothing
' Re-enables screen updating
Application.ScreenUpdating = True
End SubThe above VBA code begins by disabling screen updating to enhance performance.
The subroutine then utilizes the FileSystemObject (FSO) for file management tasks. FSO is created and used to iterate through each file in the specified folder.
Every file encountered is deleted using the DeleteFile method of FSO. After all files in the folder have been processed, the FSO is released, and screen updating is re-enabled.
This script is useful for scenarios where batch file deletion from a specific directory is required.
Also read: How to Open Excel Workbook Using VBA
Delete All Files With a Particular Extension in a Folder
Assuming you have several files with a .txt extension in the ‘Students’ folder on C drive. You can delete the files using the below VBA code:
Sub DeleteFilesWithSpecificExtensionInFolder()
' Disables screen updating for efficiency
Application.ScreenUpdating = False
' Declares a string variable for the file path with a specific extension
Dim FilePath As String
' Sets the file path to target all '.txt' files in the specified folder
FilePath = "C:\Students\*.txt"
' Deletes all files with the '.txt' extension in the specified folder
Kill FilePath
' Re-enables screen updating
Application.ScreenUpdating = True
End SubThe above VBA code is designed to delete all files with a specific extension (in this case, .txt files) from a designated folder (“C:\Students”).
It achieves this by setting the FilePath variable to a string that specifies the folder path and file extension pattern (*.txt). The Kill command is then used to delete all files matching this pattern.
This code uses the asterisk (*) wildcard character to represent all characters before the .txt extension. This wildcard character allows the ‘Kill’ statement to delete all files with a .txt extension in the ‘Reports’ folder.
The use of Application.ScreenUpdating ensures that the screen does not refresh during this process, enhancing performance.
Also read: How to Close Workbook Using VBA
Delete Files With a Particular Word in the File Name
Assume you have several files with the word ‘Sales’ in the file name in the ‘Colleges’ folder on C drive. You can delete these files using the following VBA code:
Sub DeleteFilesWithSpecificWordInFileName()
' Disables screen updating for efficiency
Application.ScreenUpdating = False
' Declaring variables
Dim FSO As Object
Dim FolderPath As String
Dim FileItem As Object
Dim Keyword As String
' Sets the folder path and keyword for file deletion criteria
FolderPath = "C:\Colleges\"
Keyword = "Sales"
' Creates a FileSystemObject for file management
Set FSO = CreateObject("Scripting.FileSystemObject")
' Iterates through each file and delete if keyword is found
For Each FileItem In FSO.GetFolder(FolderPath).Files
If InStr(1, FileItem.Name, Keyword, vbTextCompare) > 0 Then
FSO.DeleteFile FileItem
End If
Next FileItem
' Releases the FileSystemObject
Set FSO = Nothing
' Re-enables screen updating
Application.ScreenUpdating = True
End SubExplanation of the Code
Here’s an explanation of the less apparent statements in the code:
- Set FSO = CreateObject(“Scripting.FileSystemObject”): This line creates a ‘FileSystemObject’ to work with files and folders.
Note: A ‘FileSystemObject’ is an object that enables interaction with a computer’s file system. This object allows you to perform various file and folder-related operations, such as creating, deleting, copying, moving, and retrieving information about files and folders.
- For Each FileItem In FSO.GetFolder(FolderPath).Files: This statement initiates a loop that iterates through each file in the specified folder.
- If InStr(1, FileItem.Name, Keyword, vbTextCompare) > 0 Then: This line compares case-insensitively to check if the particular word is in the file name.
- FSO.DeleteFile FileItem.Path: If the specified word is in the file name, the ‘DeleteFile’ method deletes the file.
Also read: How to Save Workbook Using VBA
Delete Files Older Than Given Number of Days
You can delete files based on days, for instance, files older than a given number of days since they were last modified, files created on a particular date, files not created on the current date, and more.
Suppose you have files in the ‘Company Sales’ folder older than 60 days since they were last modified.
You can use the below code to delete them:
Sub DeleteFilesOlderThanSpecificDays()
' Disables screen updating for efficiency
Application.ScreenUpdating = False
' Declares variables for folder path, days threshold, and file system objects
Dim folderPath As String
Dim iDays As Integer
Dim FSO As Object
Dim File As Object
' Sets the folder path and the age threshold in days for file deletion
folderPath = "C:\Company Sales\"
iDays = 60
' Creates a FileSystemObject for file management
Set FSO = CreateObject("Scripting.FileSystemObject")
' Iterates through each file in the specified folder
For Each File In FSO.GetFolder(folderPath).Files
' Checks if the file is older than the specified number of days
If DateDiff("d", File.DateLastModified, Now) > iDays Then
' Deletes the file if it is older than the threshold
File.Delete
End If
Next
' Releases the FileSystemObject
Set FSO = Nothing
' Re-enables screen updating
Application.ScreenUpdating = True
End SubThe above VBA code delete files from a specified folder (“C:\Company Sales”) that are older than a certain number of days (in this case, 60 days).
The script utilizes a FileSystemObject to manage file operations. It iterates through each file in the designated folder and checks the file’s last modified date using the DateDiff function.
If the difference in days between the file’s last modified date and the current date exceeds the specified threshold (60 days), the file is deleted (using the File.Delete method).
Note: The ‘DateDiff’ function calculates the difference between dates or times. It returns an integer representing the time intervals (such as days, months, or years) between two specified dates or times.
Also read: Check If Workbook Is Open Using VBA
Delete Files Based On Size
Sometimes, you may want to delete based on size. Assuming you have files larger than 25 MB in the ‘Exams’ folder. You can use the following code to delete them:
Sub DeleteFilesBySize()
' Disables screen updating
Application.ScreenUpdating = False
' Declares variables
Dim FolderPath As String
Dim File As Object
Dim FSO As Object
Dim Folder As Object
Dim FileCollection As Object
' Initializes FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
' Sets the target folder path
FolderPath = "C:\Exams\"
' Retrieves the folder and its file collection
Set Folder = FSO.GetFolder(FolderPath)
Set FileCollection = Folder.Files
' Iterates through each file and delete if greater than 25 MB
For Each File In FileCollection
If File.Size > 25000000 Then
File.Delete
End If
Next
' Releases the FileSystemObject and other objects
Set FSO = Nothing
Set Folder = Nothing
Set FileCollection = Nothing
' Re-enables screen updating
Application.ScreenUpdating = True
End SubThe above subroutine delete files larger than a specified size (25 MB) from a given folder (“C:\Exams”).
It uses a FileSystemObject to access the folder and iterate through its files. The VBA script then checks each file’s size and deletes those exceeding 25 MB.
Also read: Run Macro In Another Workbook Using VBA
Delete Non-Excel Files
Suppose you have non-Excel files in the ‘Tenants’ folder. You can delete them using the following code:
Sub DeleteNonExcelFiles()
' Disables screen updating
Application.ScreenUpdating = False
' Declares variables
Dim FSO As Object
Dim folderPath As String
Dim folder As Object
Dim file As Object
' Sets the folder path to be checked
folderPath = "C:\Tenants\"
' Creates a FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
' Checks if the folder exists
If FSO.FolderExists(folderPath) Then
' Retrieves the folder
Set folder = FSO.GetFolder(folderPath)
' Iterates through each file in the folder
For Each file In folder.Files
' Deletes the file if it is not an Excel file (.xlsx or .xlsm)
If Not (Right(file.Name, 5) = ".xlsx" Or Right(file.Name, 5) = ".xlsm") Then
file.Delete
End If
Next file
End If
' Releases the FileSystemObject and folder object
Set FSO = Nothing
Set folder = Nothing
' Re-enables screen updating
Application.ScreenUpdating = True
End Sub
The above VBA code is tailored to remove all files that are not Excel files (with extensions .xlsx or .xlsm) from a specified folder (“C:\Tenants”).
It first checks if the folder exists using a FileSystemObject. If the folder exists, the script iterates through each file within the folder.
For each file, it checks the file extension, and if the file is not an Excel file, it is deleted. This is particularly useful for cleaning up folders by removing non-Excel files, thereby maintaining a directory dedicated to Excel documents.
In this tutorial, I showed you some examples of deleting files in a folder using VBA in Excel.
I hope you found the Excel tutorial helpful.
Other Excel VBA articles you may also like:
