VBA Delete File
In this article I will explain how you can delete files using VBA.
Contents
Basics:
The code below will delete the file specified by the path associated to the variable strPath:
Sub Example1()
Dim ObjFso As Object
Dim strPath As String
'file path
strPath = "D:StuffBusinessTempTempFile.xlsx"
Set ObjFso = CreateObject("Scripting.FileSystemObject")
'deletes file
ObjFso.deletefile (strPath)
End Sub
In the code above it is assumed there is a file named “TempFile.xlsx” at the location “D:StuffBusinessTemp”.
Before:
After:
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:
Sub Example2()
Dim ObjFso As Object
Dim strPath As String
Dim CheckExists As Boolean
'file path
strPath = "D:StuffBusinessTempTempfile.xlsx"
Set ObjFso = CreateObject("Scripting.FileSystemObject")
'deletes file
CheckExists = ObjFso.FileExists(strPath)
If CheckExists = True Then
Call ObjFso.deletefile(strPath)
Else
MsgBox ("The file does not exist")
End If
End Sub
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:
Private Sub Delete_File(ByVal strPath As String)
Dim ObjFso As Object
Set ObjFso = CreateObject("Scripting.FileSystemObject")
On Error GoTo lblError:
'attempt to delete the file
ObjFso.deletefile (strPath)
'remove the FileSystemObject from memory
Set ObjFso = Nothing
Exit Sub
'if the attempt to delete the file fails
lblError:
Err.Clear
End Sub
the code below calls the function:
Sub Example3()
Call Delete_File("D:StuffBusinessTempTempfile.xlsx")
End Sub
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) <> "" 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.
Kill "C:\myPath\filename.txt"
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) <> “” Then
SetAttr filename, vbNormal
kill filename
End If
End Sub
And then call it by using:
delReadOnly "c:\myPath\filename.txt"
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) <> “” 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:
delReadOnly "c:\myPath\filename.txt"
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 " & chr(34) & myPath & chr(34) & " /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,” :”) > 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:
Workbooks(myPath).Close
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
See also:
You can download the file and code related to this article from the link:
See also:
- 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
If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com
One thought on “VBA Delete File”