How to delete files using VBA, including files which may be readonly

Article contributed by Dave Rado and Doug Steele

You can use VBA Kill statement to delete files, as in:

Kill path & filename

However, the Kill statement can't delete readonly files, so, unless there's no chance that the file could be marked as readonly, you must first remove the readonly attribute from the file. You can do this as follows:

Dim KillFile As String
KillFile = "c:\temp\temp.doc"
'Check that file exists
If Len(Dir$(KillFile)) > 0 Then
    'First remove readonly attribute, if set
    SetAttr KillFile, vbNormal
    'Then delete the file
     Kill KillFile
End If

That's a bit unwieldy, so it's better to create a public macro that takes the path & filename as an argument, which you can then call whenever you like from other macros, as follows:

Public Sub KillProperly(Killfile As String)
    If Len(Dir$(Killfile)) > 0 Then
        SetAttr KillFile, vbNormal
        Kill KillFile
    End If
End Sub

You could then call it from another macro like this:

KillProperly "c:\temp\temp.doc"

To delete all the files in a given directory:

'Loop through all the files in the directory by using Dir$ function
Dim MyFile As String
MyFile = Dir$("c:\temp\*.*")
Do While MyFile <> ""
    KillProperly "c:\temp\" & MyFile
    'need to specify full path again because a file was deleted ¹
    MyFile = Dir$("c:\temp\*.*")
Loop

To delete all the Word documents in a given directory, leaving other files alone:

Dim MyFile As String
MyFile = Dir$("c:\temp\*.doc")
Do While MyFile <> ""
KillProperly "c:\temp\" & MyFile
MyFile = Dir$("c:\temp\*.doc")
Loop

__________________

Alternatively you could read all the values into an array, and then delete all the files using a second loop. That would run slightly quicker, but unless it's a very large directory indeed, you wouldn't notice any difference.