VBA Delete Workbook (Excel File)

puneet-gogia-excel-champs

- Written by Puneet (Last Updated: June 22, 2023)

To delete an Excel file from a folder you can use two different methods. The first method is the “Kill” statement which 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 helps you to delete a single file or multiple files, and use 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:UsersDellDesktopSample Datafile-one.xlsx"
delete a file using vba kill function

This code will show you an error if the workbook that you specified to delete doesn’t exist.

Helpful Links: Run a MacroMacro RecorderVisual Basic EditorPersonal Macro Workbook

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:UsersDellDesktopSample Data*.xl*"
delete all the files from a folder using vba

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.
    delete a file using the file system object
  • After that, use the create object function to return the FileSystemObject as assign it to the FSO variable.
    create object function
  • Next, assign the path to the “myFile” variable to access the file that you wish to delete.
    assign the path to the myfile
  • In the end, use the “DeleteFile” method (see this) to delete the file.
    use the delete file method

Full Code

Sub vba_delete_file()
Dim FSO
Dim myFile As String
Set FSO = CreateObject("Scripting.FileSystemObject")
myFile = "C:UsersDellDesktopSample Datafile1.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:UsersDellDesktopSample Datafile1.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
Last Updated: June 22, 2023