In this article I will explain how you can delete files using VBA.
The code below will delete the file specified by the path associated to the variable strPath:
In the code above it is assumed there is a file named “TempFile.xlsx” at the location “D:\Stuff\Business\Temp\”.
Check File Existence First:
If you attempt to delete a file that does not exists you will get the following error:
A good method for preventing this error from occurring is to check if the file exists before attempting to delete it. The code below checks if the file specified by the path exists or not. If it exists it will delete the file:
For more information about this topic please see the link below:
Run-time Error ’70’ Permission Denied:
Another error that you may encounter when trying to delete a file using VBA, is the Run-time Error ’70’ Permission Denied error. This error occurs when the file is open. This can be prevented by using error handlers.
The function below receives as input a string path it tries to delete it. If the file is open, the error handler will catch the error:
the code below calls the function:
You can download the file and code related to this article from the link:
- VBA Check File Exists
- Find and List all Files and Folders in a Directory
- VBA Copy File
- Excel VBA, Find and List All Files in a Directory and its Subdirectories