To delete a file using VBA you need to use the VBA Kill Function. The problem with the VBA Kill function however is that it will not check whether the file exists in the first place, and instead will throw and error.
filePath = "C:\pathtoyourfile\file" 'This might throw an error in case file does not exist Kill filePath 'This will never throw an error If Len(Dir(filePath)) > 0 Then SetAttr filePath, vbNormal Kill filePath End If
VBA Deleting multiple files using Wildcards (*)
In case you have many files to delete in a single directory that match a certain sequence of characters in the name you can use Wildcards (*).
A Wildcard (*) replaces any number of characters (more than one) in the filename. An example below:
Kill "my_*.xlsx" 'will delete files '"my_file.xlsx" '"my_file2.xlsx" '"my_name.xlsx" '"my_n.xlsx" '... an any file starting with "my_" and with an extension "xlsx"
VBA Deleting files matching a Regular Expression
In some cases there are more complex rules defining the files we should delete. In such case we can create a new function that uses VBA Regular Expressions:
Sub RegexKill(filePath As String, pattern As String) Dim regex As Object, fileName As String, deleted As Long Set regex = CreateObject("VBScript.RegExp") regex.pattern = pattern fileName = Dir(filePath) Do While fileName <> vbNullString If regex.Test(fileName) Then Kill (filePath & fileName) fileName = Dir Loop End Sub
Having this functions we can delete files using patterns (be sure to read my VBA Regular Expression tutorial if you are new to Regex).
Let us use this example below.
I would like to delete files “1.txt” and “2.txt” while maintaining remaining files. Hence I can use the Regex pattern [0-9] which means any single numeric digit. Execution would look hence like this:
RegexKill "C:\Users\tomasz.kacprowicz\Desktop\Test\", "[0-9].txt"
VBA Delete files recursively
In some cases you might not know if which directory the file you want to delete is and you have only a root directory. Traversing these folder manually is cumbersome. However with the help of my TraversePath procedure which I created in the postVBA Dir Function – How to traverse directories we can create our own RecursiveKill 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
Now using the same example from above let us assume we have the file 1.txt copied multiple times across our directory. We can easily delete all copies like this:
RecursiveKill "C:\some_path\", "1.txt"