How to Delete File in VBA
- Delete File in VBA
- Delete a Single File in VBA
- Delete All Text Files in VBA
- Delete All Excel Files in VBA
- 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:
- Delete a single file using VBA.
- Delete all text files using VBA.
- Delete all Excel files using VBA.
- 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: