VBA Kill function

Description

The VBA Kill function deletes a file based on the provided pathname.

If using Windows you can also use the FileSystemObject VBA DeleteFile method which allows you to delete a file or folder.

Syntax

The syntax for the Kill function is:

Kill( pathname )

Parameters

Parameter Variable Type Description
pathname String The pathname of the file you want to delete or a pathname including wildcards.

Other Notes

The Kill function’s pathname parameter can include wildcards. You can use wildcard characters to specify multiple files to be deleted. The patterns that you can choose from are:

Wildcard Description
* Allows you to match any string of any length (including zero length)
? Allows you to match on a single character

The VBA Kill function will not delete readonly files.

Example usage

The Kill function can be used in VBA. Let’s look at some Kill function examples:

Kill "somefile.txt"
Result: Deletes file "somefile.txt" in the current directory

Kill "C:\*.txt"
Result: Deletes all txt files under drive C:\

As mentioned above the VBA Kill function will not delete readonly files. Therefore to delete a file in VBA you need to make sure its file property is set to vbNormal (use SetAttr function). Similarly it is good to verify if the file exists before attempting to delete it. The function below takes care of both:

Public Function KillFile(filePath As String) as Boolean
    If Len(Dir(filePath)) > 0 Then
        SetAttr filePath, vbNormal
        Kill filePath
        KillFile = true
        Exit Function
    End If
    KillFile = false
End Function

Sub ExampleKill()
   If KillFile("C:\test.txt") Then Debug.Print "Killed successfully!"
End Sub

Recursive file deleting

In some cases you might want to delete all files of a certain filename in the directory and all subsequent directories. In this case in my post Deleting files using VBA I explored a recursive file delete function:

Sub RecursiveKill(filePath As String, fileName As String)
    Dim currDir As String
    Dim currentPath As String, dirItem As Variant
    Dim dirCollection As Collection
    Set dirCollection = New Collection
    
    'Delete the file in current Directory
    If Len(Dir(filePath & fileName)) > 0 Then
       SetAttr filePath & fileName, vbNormal
       Kill filePath & fileName
    End If
    'Delete the file recursively in remaining directories
    currDir = Dir(filePath, vbDirectory)
    Do Until currDir = vbNullString
        If Left(currDir, 1) <> "." And (GetAttr(filePath & currDir) And vbDirectory) = vbDirectory Then
            dirCollection.Add filePath & currDir & "\"
        End If
        currDir = Dir()
    Loop
     
    For Each dirItem In dirCollection
        RecursiveKill CStr(dirItem), fileName
    Next dirItem
End Sub

Delete all copies of file “1.txt” in the given and below directories like this:

RecursiveKill "C:\some_path\", "1.txt"