Deleting a file in VBA

Question

Using VBA, how can I (a) test whether a file exists, and if so, (b) delete it?

Answer 1

In VB its normally Dir to find the directoy of the file. If its not blank then it exists and then use Kill to get rid of the file.

test = Dir(filename)
If not test="" then
Kill(Filename)
end if

Answer 2

The following can be used to test for the existence of a file, and then to delete it.

Dim aFile As String
aFile = "c:\file_to_delete.txt"
If Len(Dir$(aFile)) > 0 Then
     Kill aFile
End If

Answer 3

1.) Check here. Basically do this:

Function FileExists(ByVal FileToTest As String) As Boolean
   FileExists = (Dir(FileToTest) <> "")
End Function

I'll leave it to you to figure out the various error handling needed but these are among the error handling things I'd be considering:

• Check for an empty string being passed.
• Check for a string containing characters illegal in a file name/path

2.) How To Delete a File. Look at this. Basically use the Kill command but you need to allow for the possibility of a file being read-only. Here's a function for you:

Sub DeleteFile(ByVal FileToDelete As String)
   If FileExists(FileToDelete) Then 'See above
      SetAttr FileToDelete, vbNormal
      Kill FileToDelete
   End If
End Sub

Again, I'll leave the error handling to you and again these are the things I'd consider:

• Should this behave differently for a directory vs. a file? Should a user have to explicitly have to indicate they want to delete a directory?

• Do you want the code to automatically reset the read-only attribute or should the user be given some sort of indication that the read-only attribute is set?

Answer 4

You can set a reference to the Scripting.Runtime library and then use the FileSystemObject. It has a DeleteFile method and a FileExists method.

See the MSDN article here.

Answer 5

I'll probably get flamed for this, but what is the point of testing for existence if you are just going to delete it? One of my major pet peeves is an app throwing an error dialog with something like "Could not delete file, it does not exist!"

On Error Resume Next
Kill "c:\file_to_delete.txt"
On Error Goto 0
return Len(Dir$(aFile)) > 0 ' Make sure it actually got deleted.

If the file doesn't exist in the first place, mission accomplished!

Answer 6

set a reference to the Scripting.Runtime library and then use the FileSystemObject:

Dim fso as New FileSystemObject, aFile as File

if (fso.FileExists("PathToFile")) then
    aFile = fso.GetFile("PathToFile")
    aFile.Delete
End if

Answer 7

An alternative way to code Brettski's answer, with which I otherwise agree entirely, might be

With New FileSystemObject
    If .FileExists(yourFilePath) Then
        .DeleteFile yourFilepath
    End If
End With

Same effect but fewer (well, none at all) variable declarations.

The FileSystemObject is a really useful tool and well worth getting friendly with. Apart from anything else, for text file writing it's massively faster than the legacy alternative, which may surprise a few people. (In my experience at least, YMMV).

Answer 8

This is more in the nature of a comment, but as I have zero points, here goes:

The accepted answer is useful for training purposes, or in constrained circumstances. However, in any real world (Windows) situation, the Scripting.Runtime will be available, and it's FileSystemObject is so simple and robust that it is by far the best method for anyone who just wants to get the job done.