How to Delete File in VBA

1. Delete File in VBA
2. Delete a Single File in VBA
3. Delete All Text Files in VBA
4. Delete All Excel Files in VBA
5. Delete a Folder in VBA

We will introduce different methods of deleting files using VBA with examples.

Delete File in VBA

It is normal for every user to have intermediate files during a process, but those files need to be deleted after the process is done. For example, we often get emails with attached files in them.

These files are beneficial for us only when the files are required to work on.

These files should be deleted after some time as they occupy extra space in the computer (or drive) that can bring down the system’s performance.

If we seek to delete these extra files manually, it takes time. One can even forget to delete such files if one decides to delete intermediate files in more than one sitting.

Simple VBA codes can be used to delete these extra files.

Several codes deal with the different methodologies for deleting files. Some of the main codes are:

1. Delete a single file using VBA.
2. Delete all text files using VBA.
3. Delete all Excel files using VBA.
4. Delete an entire folder using VBA.

Delete a Single File in VBA

There are two methods to delete an Excel file. One is the Kill function, called FileSystemObject.

Let’s go through an example and use the Kill function to delete a single file.

Sub DeleteFileWithKill()

Kill("D:/Test/testFile.xlsx")

End Sub

Before running the code:

After running the code:

As you can see from the above example, that is quite easy to delete a file using the kill function.

In the FileSystemObject method, we first declare variables, create an object function, assign the path for the file, and then use the DeleteFile function. The complete code is shown below.

Sub DeleteFileWithFSO()

Dim FileSysObj

Dim FileToDel As String

Set FileSysObj = CreateObject("Scripting.FileSystemObject")

FileToDel = "D:\Test\testFile.xlsx"

FileSysObj.DeleteFile FileToDel, True

End Sub

Before running FileSystemObject method:

After running FileSystemObject method:

If we want to check the existence of a file and then delete it, then the VBA code is below.

Sub DeleteFileAfterChecking()

Dim FileSysObj

Dim FileToDelete As String

Set FileSysObj = CreateObject("Scripting.FileSystemObject")

FileToDelete = "D:\Test\testFile.xlsx"

If FileSysObj.FileExists(FileToDelete) Then

FileSysObj.DeleteFile FileToDelete, True
MsgBox "File Deleted"

Else
MsgBox "There is no file with the name you provided!"

End If
End Sub

Output:

As you can see from the above example, by using the FileSystemObject, we can even make sure to delete the file if the file exists. If it is not present, we can display a message saying the file doesn’t exist.

Delete All Text Files in VBA

We can also use VBA to delete all the files of certain extensions such as .txt. As shown below, let’s go through an example and try to delete all the .txt files present in a certain folder.

Sub DeleteTxtFile()
Kill "D:\Test\Dst\*.txt"
End Sub

Output before running the code:

Output after running the code:

Delete All Excel Files in VBA

We can also use VBA to delete all the files of certain extensions, such as .xlsx. As shown below, let’s go through an example and try to delete all the .xlsx files present in a certain folder.

Sub DeleteXlFile()
Kill "D:\Test\Dst\*.xlsx"
End Sub

Output before running the code:

Output after running the code:

Delete a Folder in VBA

VBA provides a solution to delete a whole folder. A function called RmDir is used to delete an entire folder.

As shown below, let’s go through an example and try to delete the whole folder.

Sub DeleteFolder()

RmDir "D:\Test\Dst\"

End Sub

Output before running the code:

Output after running the code: