WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Delete File

VBA Delete File

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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 a 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.

VBA-Delete-File

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.

vba delete file - kill method

Path Name: pathname is nothing but the folder path in the computer to delete the files.

Note: Pathname can include wildcard characters as well. We can use an asterisk (*) and question marks (?) as wildcard characters in excel.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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.

vba delete file example 1.1

In this folder, I want to delete the file named “File 5”. Start the code with the KILL function.

Code:

Sub Delete_Files()

Kill(PathName)

End Sub

vba delete file example 1.2

Copy and paste the folder path.

vba delete file example 1.3

And Paste in double-quotes.

Kill "E:\Excel Files"

vba delete file example 1.4

Now put one more backward slash (\) and enter the file name with extension.

Kill "E:\Excel Files\File5.xlsx"

VBA Deletef example 1.5

When you run this code, it will delete the file named “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 the KILL function. After mentioning the folder path, we need to mention the file as “*.xl*.”

Code:

Kill "E:\Excel Files\*.xl*"

VBA Deletef example 2.1

When you run this code, this 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 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.

Code:

Kill "E:\Excel Files\*.*"

VBA Deletef example 3.1

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 the KILL function and then to delete the folder, and we need to use one more function called RmDir.

Code:

RmDir "E:\Excel Files\"

VBA Deletef example 4.1

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"

VBA Deletef example 5.1

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.

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

VBA Deletef example 6.1

You can download this VBA Delete File Excel Template from here – VBA Delete File Excel Template.

Recommended Articles

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 –

  • VBA InStr
  • VBA Join
  • New Line in Excel VBA
  • Split Function in Excel VBA
6 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More