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:

VBA, Delete file

After:

VBA, Delete File REsult


Check File Existence First:

If you attempt to delete a file that does not exists  you will get the following error:

Check File Exists

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:

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”

Leave a Reply

Your email address will not be published. Required fields are marked *