In VBA we can delete any file present in the computer using VBA codes and the code which is used to delete any file is known as Kill command, the method to delete any file is that first, we provide the path of the file which means where the file is located in the computer and then we use Kill command to delete the file.
How to Delete Files using VBA Code?
VBA is the tough thing at the beginning but as you spend more time with VBA you will start loving it just like me. We can open files from another folder of a computer, we can work with them, and now we can delete files as well by using VBA coding. In this article, we will show you how you can delete files using VBA Code in the specific folder.
When we are working with large projects we usually create a lot of intermediate files to support our process. After all the work is done we need to delete those files to avoid any sort of confusion in the future.
And one scenario is when we usually receive an email we save attachments for our regular work or we just want to see the report for that point of time and later we may need to delete those files.
Deleting those files manually will take the time or we may forget to save and it will occupy the space on our computer. We will show you how to delete those files with simple VBA codes.
Kill Method to Delete Files in a Folder using VBA Code
A simple KILL function will delete the folder, specific file, all excel files, etc. Take a look at the syntax of the KILL method in VBA. Kill method cannot delete read-only files.
Path Name: pathname is nothing but the folder path in the computer to delete the files.
Note: Path name can include wildcard characters as well. We can use an asterisk (*) and question marks (?) as wildcard characters in excel.
Asterisk (*) is useful to match any string of any length even zero is considered.
Question mark (?) is useful to match only a single character.
Delete Particular File Name
For example, I have a folder like below.
In this folder, I want to delete the file named as “File 5”. Start the code with KILL function.
Sub Delete_Files() Kill(PathName) End Sub
Copy and paste the folder path.
And Paste in double quotes.
Kill "E:\Excel Files"
Now put one more backward slash (\) and enter the file name with extension.
Kill "E:\Excel Files\File5.xlsx"
When you run this code, it will delete the file named as “File 5.xlsx” in the mentioned folder path.
Delete All Excel Files
To delete all the excel files in the folder using VBA we need to use wildcard characters with KILL function. After mentioning the folder path we need to mention the file as “*.xl*”
Kill "E:\Excel Files\*.xl*"
When you run this code, this will delete all the excel files in the folder.
We have seen how can we delete a single excel file and all the excel files. But if we want to delete all the files in the folder how can we delete it. Since we are using Excel VBA, can it delete other files?
The answer is Yes!!! Use the below code to delete all the files in the folder.
Kill "E:\Excel Files\*.*"
Delete Entire Folder Only
Is it possible to delete the entire folder itself?
Yes, it is possible.
To do this, the first thing we need to do is we need to delete all the files in the folder by using KILL function, and then to delete folder we need to use one more function called RmDir.
RmDir "E:\Excel Files\"
Here RmDir will delete only the empty folder, if any subfolder is there it cannot delete them.
Delete All the Text Files in the Folder
To delete all the text files in the folder use the below code.
Kill "E:\Excel Files\*.txt"
Delete Read-Only Files
As I told KILL function cannot delete “Read Only” files in the folder. In such a case scenario we need to use the other two functions “Dir$” & “SetAttr” functions. Below is the example code to delete the read-only files as well.
Sub Delete_Files1() Dim DeleteFile As String DeleteFile = " E:\Excel Files\" If Len(Dir$(DeleteFile)) > 0 Then SetAttr DeleteFile, vbNormal Kill DeleteFile End If End Sub
You can download this VBA Delete File Excel Template from here – VBA Delete File Excel Template
This has been a guide to VBA Delete Files. Here we discuss the VBA Code to delete 1. Particular File Name, 2. All files, 3. Entire folder, and 4. Read-only Files with downloadable excel template. Below are some useful articles related to VBA –