Deleting a file in VBA

Question

Using VBA, how can I:

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

score 207 · Accepted Answer · edited Sep 06 '16 at 06:49

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.

thank you - what if there are two files of the same name which exist will the DeleteFile sub kill both of them or only one? any advice much appreciated. — BenKoshy, Aug 01 '16 at 23:15

Mike Woodhouse · Answer 2 · 2015-03-04T13:17:39.027

60

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 can actually sometimes be faster than the legacy alternative, which may surprise a few people. (In my experience at least, YMMV).

edited Mar 04 '15 at 13:17

answered Sep 16 '08 at 11:03

Mike Woodhouse

51,832
12
88
127

7

Using this syntax without declaring a file scripting object, must add reference for Microsoft Scripting Runtime, else: Dim fs As New Scripting.FileSystemObject – pghcpa Mar 04 '15 at 05:13
6

you also need to reference the scripting library. see here: http://stackoverflow.com/questions/3233203/how-do-i-use-filesystemobject-in-vba – ekkis May 14 '15 at 00:06
Since there's no variable to set to Nothing, is there a risk the FileSystemObject will remain in memory, causing a leak or other issue? – johny why Sep 23 '16 at 22:10
No, it will be discarded after the "End With". Since it is not assigned to a variable the effect is similar to the object having been assigned to a variable that has been set to "Nothing". – TCN Jan 01 '17 at 04:09

JohnFx · Answer 3 · 2013-08-02T18:31:16.803

17

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
aFile = "c:\file_to_delete.txt"
Kill aFile
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!

edited Aug 02 '13 at 18:31

answered Sep 15 '08 at 23:34

JohnFx

34,542
18
104
162

4

You raise a good point but, like most things, I think it would depend on context and sometimes simply having a "File Exists" function is handy apart from deletion. – Onorio Catenacci Sep 16 '08 at 11:11
3

+1: maybe the user of the application wants to be asked before removing a file: for instance, using `ActiveWorkbook.SaveCopyAs` is not able to overwrite, so you first have to remove existing file with same filename. – Joël Nov 07 '13 at 14:38
but you should never use `On Error Resume Next`, or so i've been told :D Of course, that's ridiculous advice, and your answer is correct. – johny why Sep 23 '16 at 17:08
The `Len(dir(...))` part isn't SOLELY to check for existence. It is also **checking if the file is HIDDEN** because a hidden file will return an empty string even if it exists (and you won't be able to delete it): `Dir(hiddenFile) = ""`. Hence, the part `SetAttr FileToDelete, vbNormal`eloquently takes care of this for you. – elektrykalAJ Feb 07 '19 at 00:37

score 13 · Answer 4 · answered Sep 15 '08 at 23:12

13

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

answered Sep 15 '08 at 23:12

Rich Adams

26,096
4
39
62

3

I know this question and response are old, just thought I'd add that using Len() to test strings (and functions that return strings) seems to be faster than literal string comparisons in VBA. – JimmyPena Nov 18 '11 at 16:52
9

The reason that `Len()` (and `LenB()`, which is even faster) are faster than string comparison is that in memory, VB strings are preceded by their length. Len/LenB just pull the length from that memory location, they don't have to iterate through the string to know its length. On the other hand, using string comparison has much more work to do. Additionally, avoid using `""` in VB as it always allocates a new string. Use `vbNullString` instead as it is a constant and does not uses more memory. – Renaud Bompuis Nov 12 '14 at 02:25

score 8 · Answer 5 · edited Dec 28 '15 at 08:28

8

In VB its normally Dir to find the directory of the file. If it's 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

edited Dec 28 '15 at 08:28

ZygD

22,092
39
79
102

answered Sep 15 '08 at 23:11

Leo Moore

2,118
2
19
21

score 6 · Answer 6 · answered Sep 15 '08 at 23:40

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

answered Sep 15 '08 at 23:40

Brettski

19,351
15
74
97

1

I use FileSystemObject method too, as Kill is unable to delete files/folders with diacritis – mauek unak Oct 21 '15 at 14:35
This is method I use.. Someone who implements this wants to use error checking and DisplayAlerts = false. (The file won't delete if it's in use, so must have error trap) – Gregg Burns Oct 25 '19 at 19:39

Nigel Heffernan · Answer 7 · 2014-10-08T15:11:22.323

Here's a tip: are you re-using the file name, or planning to do something that requires the deletion immediately?

No?

You can get VBA to fire the command DEL "C:\TEMP\scratchpad.txt" /F from the command prompt asynchronously using VBA.Shell:

Shell "DEL " & chr(34) & strPath & chr(34) & " /F ", vbHide

Note the double-quotes (ASCII character 34) around the filename: I'm assuming that you've got a network path, or a long file name containing spaces.

If it's a big file, or it's on a slow network connection, fire-and-forget is the way to go. Of course, you never get to see if this worked or not; but you resume your VBA immediately, and there are times when this is better than waiting for the network.

This is a great alternative, *if* asynch is what you want. – johny why Sep 23 '16 at 17:10 — johny why, Sep 23 '16 at 17:10

score 2 · Answer 8 · answered Sep 15 '08 at 23:12

2

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.

answered Sep 15 '08 at 23:12

Darrel Miller

139,164
32
194
243

score 0 · Answer 9 · answered Oct 15 '21 at 09:38

A shorter version of the first solution that worked for me:

Sub DeleteFile(ByVal FileToDelete As String)
   If (Dir(FileToDelete) <> "") Then
      ' First remove readonly attribute, if set
      SetAttr FileToDelete, vbNormal
      ' Then delete the file
      Kill FileToDelete
   End If
End Sub

Deleting a file in VBA

9 Answers9

Linked