Deleting a file in VBA

Question

Using VBA, how can I:

1. test whether a file exists, and if so,
2. delete it?

Answer 1

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          
      ' First remove readonly attribute, if set
      SetAttr FileToDelete, vbNormal          
      ' Then delete the file
      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?

---

EDIT: Marking this answer as community wiki so anyone can modify it if need be.