To delete an Excel file from a folder you can use two different methods. The first method is the “Kill” statement that takes the file path to refer to the file that you wish to delete. The second method is the FileSystemObject object which has a method associated with it to delete a file.
To use these codes, go to the VBE (Code Editor) from the developer tab.
Delete a File using VBA (Kill Function)
Kill function basically helps you to delete a single file, multiple files and using wildcard characters to delete more than one file. Below is the one-line code that deletes the file from the folder that I have on the desktop.
Kill "C:\Users\Dell\Desktop\Sample Data\file-one.xlsx"
This code will show you an error if the workbook that you specified to delete doesn’t exist.
Delete All the Files from a Folder using VBA
And if you want to delete all the files that you have in a folder, you can use a wildcard character.
Kill "C:\Users\Dell\Desktop\Sample Data\*.xl*"
Delete a File using the FileSystemObject (Object)
The file system object provides you with access to the computer’s file system. You can learn about it from here, but now, let’s write a code to remove a file.
- First, you need to declare variables to use in the code as a workbook and FileSystemObject.
- After that, use the create object function to return the FileSystemObject as assign it to the FSO variable.
- Next, assign the path to the “myFile” variable to access the file that you wish to delete.
- In the end, use the “DeleteFile” method (see this) to delete the file.
Sub vba_delete_file() Dim FSO Dim myFile As String Set FSO = CreateObject("Scripting.FileSystemObject") myFile = "C:\Users\Dell\Desktop\Sample Data\file1.xlsx" FSO.DeleteFile myFile, True End Sub
Let’s say you need to write a code that can check for a file, (exists or not) and then delete it. Here’s the code that you need.
Sub vba_delete_file() Dim FSO Dim myFile As String Set FSO = CreateObject("Scripting.FileSystemObject") myFile = "C:\Users\Dell\Desktop\Sample Data\file1.xlsx" If FSO.FileExists(myFile) Then FSO.DeleteFile myFile, True MsgBox "Deleted" Else MsgBox "There's no workbook with this name." End If End Sub
More Tutorials on VBA Workbooks
- How to Create a New Workbook using VBA
- How to Save a Workbook using a VBA
- How to Close a Workbook using a VBA
- How to Refer to THISWORKBOOK in VBA
- How to Rename a Workbook using VBA
- How to Activate a Workbook using VBA
- How to Combine Workbooks using VBA
- How to Protect\Unprotect a Workbook using VBA in Excel
- How to Check IF a Workbook is OPEN using VBA in Excel
- How to Open a Workbook in Excel using VBA
- How to Check IF an Excel Workbook Exists in a Folder