VBA Delete File
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:StuffBusinessTemp”.
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:
Example 3: Alternative to Example 2
This is a compact way to achieve the result in example 2. You need to add a reference to the “Microsoft Scripting Runtime” library under Tools > References.
Dim strPath, strPath = "C:\myPath\NewTextDocument.txt" With New FileSystemObject If .FileExists(strPath) Then .DeleteFile strPath End If End With
The FileSystemObject gets discarded after the End With system and hence, no cleanup is needed. Delete operations done using FileSystemObject are permanent and cannot be undone.
You can also use the dir command to check if the file exists. The remaining code remains the same.
Dim strPath, strPath = "C:\myPath\NewTextDocument.txt" With New FileSystemObject If Dir(strPath) &amp;amp;amp;amp;amp;amp;amp;lt;&amp;amp;amp;amp;amp;amp;amp;gt; "" Then .DeleteFile strPath End If End With
Example 4: Using Kill
You can also use the kill command to delete a file. This is a native way of deleting which is faster and more efficient compared to using the FileSystemObject. Delete operations done using kill are also permanent and cannot be undone.
Note that kill cannot delete read only files. If you need to delete read only files, you need to first remove this read only attribute. For that,
Sub delReadOnly(filename As String) If Dir(filename) &amp;amp;amp;amp;amp;amp;amp;lt;&amp;amp;amp;amp;amp;amp;amp;gt; “” Then SetAttr filename, vbNormal kill filename End If End Sub
And then call it by using:
Example 5: Ask confirmation before deletion
If you want to ask for confirmation before deleting a file, use the MsgBox with vbYesNo option as below:
Sub delConfirmation(filename As String) If Dir(filename) &amp;amp;amp;amp;amp;amp;amp;lt;&amp;amp;amp;amp;amp;amp;amp;gt; “” Then If MsgBox("Are you sure you want to delete the file?", vbYesNo) = vbYes Then kill filename Else Exit Sub 'terminate macro End If End If End Sub
Again call it by using:
Example 6: Delete using command prompt
You can also delete a file asynchronously using the command prompt. This can be useful for big files or files on a slow network when your remaining code does not depend on the successful deletion of the file
Shell "DEL " &amp;amp;amp;amp;amp;amp;amp;amp; chr(34) &amp;amp;amp;amp;amp;amp;amp;amp; myPath &amp;amp;amp;amp;amp;amp;amp;amp; chr(34) &amp;amp;amp;amp;amp;amp;amp;amp; " /F ", vbHide
Note that chr(34) — i.e. the double quote characters — are to be used when your file path contains spaces.
Example 5: Delete all files in a folder
You can also use wildcard characters with kill. The below code will thus delete all files in a folder
On Error Resume Next Kill "C:\myPath\*.*" On Error GoTo 0
Example 6: Delete all Excel files in a folder
Similar to example 5, if you want to delete files of a particular type, say, all Excel files, use the code below:
On Error Resume Next Kill "C:\myPath\*.xl*" On Error GoTo 0
Example 7: Delete an empty folder
Say, you want to delete a folder. You can first delete all the files in the folder using example 5. Then, you can delete the folder using the RmDir (Remove Directory) command. Here is the entire code
On Error Resume Next Kill "C:\myPath\*.*" RmDir "C:\myPath\" On Error GoTo 0
Example 8: Delete folder without emptying it first
RmDir requires that the folder is empty before deleting it. You can use the deletefolder command of the fileSystemObject to delete a non-empty folder
Dim FSO As Object Dim myPath As String Set FSO = CreateObject("scripting.filesystemobject") myPath = “C:\myPath” If FSO.FolderExists(myPath) Then 'First check if the folder exists FSO.DeleteFolder myPath Else MsgBox "Folder not found" End If
The path to the folder should not contain a trailing back slash. You can use the below code to remove the back slash, if present
If Right(myPath, 1) = "\" Then ‘If last character is \ then myPath = Left(myPath, Len(myPath) - 1) ‘remove it End If
A few things to consider / take care of while deleting files:
As you are dealing with the file system, there can be many conditions that cause an exception. Please note:
Issues with the path specified (particularly important when the path of the file is inputted by the user) –
1. The path should be a valid one. Possible errors with this one:
– It is a zero-length string
If len(strPath) = 0 then
– it contains only white space
If len(strPath.Replace " ", "") = 0 then
– it contains invalid characters like colon (:)
If instr(strPath,” :”) &amp;amp;amp;amp;amp;amp;amp;gt; 1 then
– it is a device path (starts with \\.\)
If instr(strPath,” \\.\”) = 1 then
2. The path exceeds the system-defined maximum length – You can add a check for length here.
3. The file does not exist. – We have seen this bit of code earlier. Using dir or fileExists we can ensure that the file exists before attempting to delete it.
Other possible errors:
4. The file is in use – You obviously need to make sure that the file that you are trying to delete is not in use or open. You can ensure this manually or programmatically. For example, to close an excel file:
5. The user lacks necessary permissions to delete the file – You will need to ensure that user has necessary permissions before deleting / accessing files.
6. The file is read-only. – We have seen how to take care of read only files in the previous examples by using SetAttr
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
- Calling the Shell in VBA