Delete Files in a Folder Using VBA in Excel

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 Sub

The 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 Sub

The 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 Sub

The 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 Sub

Explanation of the Code

Here’s an explanation of the less apparent statements in the code:

  1. 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.

  1. For Each FileItem In FSO.GetFolder(FolderPath).Files: This statement initiates a loop that iterates through each file in the specified folder.
  2. 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.
  3. 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 Sub

The 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 Sub

The 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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment