EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA File & Directories Functions VBA Delete File
Secondary Sidebar
VBA File & Directories Functions
  • VBA File & Directories
    • VBA Get Cell Value
    • VBA FileDialog
    • VBA Delete File
    • VBA Check File Exists
    • VBA DIR
    • VBA GetOpenFileName
    • VBA Write Text File
    • FileCopy in VBA
    • VBA FreeFile
    • VBA Solver

VBA Delete File

By Madhuri ThakurMadhuri Thakur

VBA Delete File

Introduction to VBA Delete File

Sometimes when we work in VBA we create some unwanted files, or we have some unwanted files on our computer. How do we get rid of them? We can simply go and delete each file manually by locating the file and right click on it then, we click on the delete button to delete the file. Or we can press the delete button from the keyboard to delete the file. But how do we do this in VBA is what we will learn in this article.

So now we have understood the concept of this topic that we need to delete a file by using VBA macros. In VBA, we have a kill command which is used to delete a file from its location. When we simply delete the file by going through the process of locating the file and right-clicking on it to delete or press the delete button even, the file goes to the recycle bin. But when we use the Kill command in VBA to delete a file the file is permanently deleted from the computer. It doesn’t go to the recycle bin. Now let us look at the syntax which we will be using to delete a file using VBA. It is as follows:

Syntax of Delete File in Excel VBA

String = “ Path of the File “
Kill String

Always remember that the path of the file should be in the inverted commas. Now to delete a file in VBA we must have its path. How do we get a path of the file? We need to right-click on the file and click on properties which give us different options for the properties of the file once we click on the security tab which is the second number in the tabs we can find the path location under the object name. Have a look at the screenshot below for reference.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,258 ratings)

The path written after the object name is the argument we need to feed for the killfile function in order to delete any file. Now let us start deleting files by looking at a few examples below.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Before we move to examples always ensure that we have the developer’s tab enabled in order to use macros. It is done by moving to the files section and then to the options section. We will find a checkbox that should be checked to enable the developer’s tab. Now let us move ahead to examples. For example, I have two files named sample 1 and sample 2. Both are text files and are on desktop. Have a look at them below.

Sample 1

How to Delete a File Using VBA?

Let’s see the examples of Delete File in Excel VBA.

You can download this VBA Delete File Excel Templates here – VBA Delete File Excel Templates

Example #1 – VBA Delete File

Let us first delete the sample 1 file in this example. Both the examples will have one minute difference which I will explain at the end of the topic. First, we need to get a path for the sample 1 file. In order to do that right click on the file and from the properties option go to security and we will find the path to beside the object name as follows.

Sample 1 path

Step 1: Now let us enter into VBA from the visual basic option. It can be found under the developer’s tab.

VBA delete Example 1-1

Step 2: Once we are in VBA we need to insert a module. To enter the code window double click on the module which will take us to the code window.

VBA Delete module

Step 3: Now the first step for writing a VBA code is to name the macro as follows.

Code:

Sub Sample()

End Sub

VBA delete Example 1.1

Step 4: Declare a variable as a string which will store the path for the file.

Code:

Sub Sample()

Dim KillFile As String

End Sub

VBA delete Example 1.2

Step 5: Now let us assign the path to this string of the file we want to delete which is sample1.

Code:

Sub Sample()

Dim KillFile As String
KillFile = "C:\Users\CBA_amardeep\Desktop\Sample1.txt"

End Sub

VBA delete Example 1.3

Step 6: Now let us delete the file using the Kill Function as follows.

Code:

Sub Sample()

Dim KillFile As String
KillFile = "C:\Users\CBA_amardeep\Desktop\Sample1.txt"
Kill KillFile

End Sub

VBA delete Example 1.4

Step 7: When we run the above code and look at the desktop we can no longer find the first text file we created.

Sample 1

Only the second file is present and the first file is moved. Now, what happens if the file path was wrong or the file didn’t even exist. Now we have deleted the first file and it didn’t even exist so we can run the code again to check what result we will get. Run the above code again.

Debug

VBA gives us a runtime error that the file is not found. This is important which we need to keep in mind.

Example #2 – VBA Delete File

Now let us delete the second file using the kill function. Again we need to have the path for the second file for which we need to right click on the file and from the properties option go to security and we will find the path to in beside the object name as follows.

Sample 2 properties

Now we have the path for the second file so let us delete this file.

Step 1: Enter into VBA through the developer’s tab.

VBA Delete Example 2-1

Step 2: Once we are in VBA we need to insert a module. To enter the code window double click on the module which will take us to the code window.

VBA Delate module

Step 3: Name the macro first in order to proceed further as follows.

Code:

Sub sample1()

End Sub

VBA Delete Example 2.1

Step 4: Similar to above, declare a variable as a string to store the file’s path.

Code:

Sub sample1()

Dim KillFile As String

End Sub

VBA Delete Example 2.2

Step 5: In the string store the path of the file as follows.

Code:

Sub sample1()

Dim KillFile As String
KillFile = "C:\Users\CBA_amardeep\Desktop\Sample2.txt"

End Sub

VBA Delete Example 2.3

Step 6: Now we will check that the file even exists using the If function as follows.

Code:

Sub sample1()

Dim KillFile As String
KillFile = "C:\Users\CBA_amardeep\Desktop\Sample2.txt"
If Len(Dir$(KillFile)) > 0 Then
SetAttr KillFile, vbNormal
  Kill KillFile
  Else
MsgBox "File Not Found"
End If

End Sub

VBA Delete Example 2.4

Step 7: Now if we run the above code we can see that the file has been deleted and it is no longer in the desktop.

VBA Delete Example 2.5

Step 8: In above example, we have seen that if the file doesn’t exist VBA gives us an error also in this case as the file has been deleted we can run the code again and we can see that instead of the error we get a personalized message as follows.

VBA Delete

Things to Remember

There are few things which we need to remember about deleting a file in VBA:

  1. We use the Kill function to delete a file.
  2. We need to have the path of the specific file which is to be deleted.
  3. If the file is not found we encounter an error.
  4. The files deleted by the Kill function do not go in the recycle bin.

Recommended Articles

This is a guide to VBA Delete File. Here we discuss how to use Excel VBA Delete File along with few practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA While Loop
  2. VBA Remove Duplicates
  3. VBA Data Types
  4. VBA Sleep
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more