Analyst Cave

VBA Delete File – Deleting files using VBA

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" 
Exit mobile version