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.
The syntax for the Kill function is:
Kill( pathname )
|pathname||String||The pathname of the file you want to delete or a pathname including wildcards.|
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:
|*||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.
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"