VBA Delete File

What is VBA Delete File?

VBA Delete File is the process of deleting files from given folder paths. In VBA, we have two different ways of deleting files, i.e., the first is the Kill Method, and the second is with the “FileSystemObject.” In this article, we will specifically talk about the KILL method to delete files in a folder.

The “Kill” method will delete a file from a given folder. We can perform various kinds of actions like deleting any particular file, deleting multiple files from the folder, deleting the entire folder, and doing many such things.

For example, look at the following VBA code.

This code will delete the file “Sales Report.xlsx” from the desktop and the folder “Power BI Use Cases.”

How to Delete Files using VBA Code?

In VBA, to delete files we use the KILL command. To automate tasks in VBA, we create a lot of intermediatory files and save them in specific folders. At the end of the project, we must delete those files.

Hence, it is necessary to automate the deletion of files at the end of the project. Before we get into examples, let us show you the KILL method syntax.

The following is the syntax of the KILL method in VBA.

KILL (PathName)

PathName: The KILL method requires just one argument i.e., the path from which the file to be deleted.

In the path section of the KILL method, we can provide wildcard characters like asterisk (*) and question marks (?).

• Asterisk (*): This will be used to match any number of string lengths.
• Question Mark (?): This is useful to match only a single character.

Now let’s look at the steps involved for VBA delete file in folder.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Let us look at some of the examples of using the VBA delete file method in practical applications.

Example #1 – Kill Method to Delete Files in a Folder using VBA Code

As seen, KILL is widely used to delete files from a folder in VBA. Let’s take the same folder from the previous example.

Assume we must delete one of the files from this folder, i.e., “Sales Report 2015.xlsx.”

We can use the following code to delete the file from this folder.

Inside the KILL method, we have used the file path and the name of the file that is to be deleted.

One thing we have done while entering the file name is to enter the extension as well because it is essential to tell the KILL method about the file, especially when there are two or more files with the same name. Hence, the file extension differentiates them.

Execute the code. It will delete the given file from the given folder location.

Example #2 – Delete All Excel Files

Sometimes, we may need to delete all the Excel files from a folder. We have a method to do this. Let’s take the same folder from the previous two examples here as well.

We have seven files in total, and out of these, we have 5 Excel files. We must get rid of all of them. Since we have 5 Excel files, we cannot write five different lines of code; instead, we will use a wildcard character asterisk (*) to match any number of characters.

First, copy the folder path.

Once the folder path is entered, we must concatenate the backslash (\) before we enter the file names.

Next, we must enter the file names. To do this, we use the asterisk (*) followed by the file extension.

Here, the wildcard asterisk (*) will match any number of characters before the file extension “.xlsx.”

Basically, it will match all the file names that have the file extension “.xlsx.”

Once we execute this code, it will delete all the Excel files with the file extension “.xlsx.”

However, we can still see one of the Excel files i.e., “Sales Report 2016.xlsm.”

The reason for this is that the extension of this file is not “.xlsx,”; but “.xlsm.” Even though the wildcard asterisk (*) has matched the file name, the file extension we have given is “.xlsx”; hence, the KILL method will not consider this for deletion.

To delete all kinds of Excel files, we must include the wildcard character asterisk (*) as follows.

The first wildcard (*) will match the file name, and the second wildcard will match the file extension after the words “.xl.”

All the Excel file extensions will start with “.xl”; hence, it will make sure all the Excel files will be deleted from the given folder.

Example #3 – Delete Entire Folder Only

Deleting files in a folder is thus done. How about deleting the entire folder itself? Deleting the entire folder is not possible in the VBA delete file method. However, we can use the RmDir method.

The syntax of the RmDir is like the KILL method.

We must provide the folder path in double quotes and concatenate the backslash (\). Let’s take the same folder from previous examples.

Assume we must delete the folder “Files Folder” from our computer. We construct the code as follows:

Let’s execute the code and see what happens.

We have got the run time error ’75’ Path / File access error.

The reason for this is that the RmDir function deletes only empty folders. Since we have some files or sub-folders inside the folder “Files Folders,” the RmDir function cannot delete it.

Hence, first, we must empty the folder before we delete the entire folder. To do this, we  use the following code to delete all the files.

Now, we have used wildcard asterisk (*) characters for file names as well as extensions. The reason for this is that we must delete all the files; hence, we provide a wildcard to match all file names and extensions.

In the following line, we can add the RmDir function code to delete the entire folder.

It will now delete the entire folder given in the RmDir function.

Note: It is essential to include the backslash (\) for the folder that is to be deleted using the VBA RmDir function.

Example #4 – Delete All the Text Files in the Folder

Like how we have deleted Excel files from the folder, similarly, we can delete all the text files as well. For example, look at the following folder image.

We have Excel files, we have PDF files, and we have text files. Now, we need to identify the way to select only text files.

The one way of identifying and letting VBA know the text file is by its file extension. The file extension of text files is “.txt”.

We can use the wildcard character asterisk (*) along with the text file extension to delete all the text files in one go.

The following code will delete all the text files.

Once we execute this code, it will delete all the text files from the folder. The following image is the result of the same.

Example #5 – Delete Read Only Files

VBA Delete files is not applicable for read-only files because the KILL method will get the VBA delete file permission denied error if we try to delete read-only files. Hence, we must use two other VBA functions, i.e., VBA Dir$ and VBA SetAttr.

The following code will delete only the read only files.

It will now delete all the read-only files from the given folder location.

Note: We can have multiple options in the VBA SetAttr function, and one such method is the VBA delete file from the directory. vbDirectory is the command we can use.

Important Things to Note

• The VBA KILL method will not delete read-only files.
• If the given folder location is not found, then we get the Run time error ’53’ File not Found.
• Wildcard character asterisk (*) will match any number of characters.
• The KILL method deletes only the files with the file extension given.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA Delete File. We learn how to delete specific files, all files, folders & read only files using Excel VBA codes, with examples. You can learn more from the following articles –

• VBA Delete Row
• VBA Insert Row
• VBA Clear Content