You can use the Kill statement in VBA to delete a specific Excel file in a specific folder.
Here is one common way to use this statement in practice:
Sub DeleteFile()
On Error Resume Next
Kill "C:\Users\Bob\Desktop\My_Data\soccer_data.xlsx"
On Error GoTo 0
End Sub
This particular macro deletes the Excel file called soccer_data.xlsx located in the following folder:
C:\Users\Bob\Desktop\My_Data
The line On Error Resume Next tells VBA that if an error occurs and the file is not found that no error message should be shown.
We then use On Error GoTo 0 to reset the default error message settings.
If you would like to display an error message if the file is not found, then simply remove these two lines from the code.
The following example shows how to use this syntax in practice.
Example: Delete File Using VBA
Suppose we have the following folder with three Excel files:
Suppose we would like to use VBA to delete the file called soccer_data.xlsx.
We can create the following macro to do so:
Sub DeleteFile()
On Error Resume Next
Kill "C:\Users\Bob\Desktop\My_Data\soccer_data.xlsx"
On Error GoTo 0
End Sub
Once we run this macro and open the folder again, we will see that the file called soccer_data.xlsx has been deleted:
All other files remained untouched in the folder.
If you would like an error message to be shown if the file doesn’t exist, you can use the following macro instead:
Sub DeleteFile()
Kill "C:\Users\Bob\Desktop\My_Data\soccer_data.xlsx"
End Sub
When we run this macro, we receive the following error message:
We receive this error message because the file soccer_data.xlsx has already been deleted and no longer exists in the folder.
Note: Be aware that the Kill statement permanently deletes a file and does not simply send it to the recycling bin.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Number of Sheets in Workbook
VBA: How to Extract Data from Another Workbook
VBA: How to Delete Sheet if Name Contains Specific Text