VBA Delete File

Publication Date :

03 Jul, 2019

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

How to Delete Files using VBA Code?

VBA is a tough thing initially, but as you spend more time with VBA, you will start loving it just like me. We can open files from another computer folder and work with them. Now, we can delete files as well by using VBA coding. This article will show you how to delete files using VBA code in a specific folder.

When working with large projects, we usually create many intermediate files to support our process. we need to delete those files to avoid future confusion after completing the work.

And one scenario is when we usually receive an email. For example, we save attachments for our regular work, or we want to see the report for that point in time, and later we may need to delete those files.

Deleting those files manually may take time, or we may forget to save them. In addition, it will occupy the space on our computer. Therefore, 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. The 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 length string. In addition, it also considers zero.

Question mark (?) is useful to match only a single character.

Delete Particular File Name

For example, we have a folder like the one below.

We want to delete the file named "File 5" in this folder. But, first, start the code with the Kill function.

Code:

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 FilesFile5.xlsx"

When you run this code, it will delete the file named “File 5.xlsx” in the mentioned folder path.

Delete All Excel Files

Using VBA, we need to use wildcard characters with the Kill function to delete all the Excel files in the folder. After mentioning the folder path, we need to mention the file as "*.xl*."

Code:

Kill "E:Excel Files*.xl*"

When you run this code, it will delete all the Excel files in the folder.

We have seen how we can 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 them? Also, 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.

Code:

Kill "E:Excel Files*.*"

Delete Entire Folder Only

Is it possible to delete the entire folder itself?

Yes, it is possible.

We need to delete all the files in the folder using the Kill function. Then to delete the folder, we need to use one more function called RmDir.

Code:

RmDir "E:Excel Files"

Here RmDir will delete only the empty folder if any subfolder is where 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.

Code:

Kill "E:Excel Files*.txt"

Delete Read-Only Files

As we said, the Kill function cannot delete "Read Only" files in the folder. In such a scenario, we need to use the other two functions: "Dir$" and "SetAttr." Below is the example code to delete the read-only files as well.

Code:

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