Deleting a file in VBA

Question

Using VBA  how can I    test whether a file exists  and if so  delete it

User · Answer

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    amp  chr 34   amp  strPath  amp  chr 34   amp     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

User · Answer

1   Check here   Basically do  this   Function FileExists ByVal FileToTest As String  As Boolean    FileExists    Dir FileToTest   lt  gt      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

User · Answer

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    gt  0 Then      Kill aFile End If

User · Answer

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    gt  0   Make sure it actually got deleted    If the file doesn t exist in the first place  mission accomplished

User · Answer

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

User · Answer

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    gt  0   Make sure it actually got deleted    If the file doesn t exist in the first place  mission accomplished

User · Answer

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    gt  0   Make sure it actually got deleted    If the file doesn t exist in the first place  mission accomplished

User · Answer

1   Check here   Basically do  this   Function FileExists ByVal FileToTest As String  As Boolean    FileExists    Dir FileToTest   lt  gt      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

User · Answer

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    gt  0 Then      Kill aFile End If

User · Answer

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    gt  0 Then      Kill aFile End If

User · Answer

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

User · Answer

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    gt  0   Make sure it actually got deleted    If the file doesn t exist in the first place  mission accomplished

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    gt  0 Then      Kill aFile End If

User · Answer

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

User · Answer

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    amp  chr 34   amp  strPath  amp  chr 34   amp     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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

1   Check here   Basically do  this   Function FileExists ByVal FileToTest As String  As Boolean    FileExists    Dir FileToTest   lt  gt      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

User · Answer

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

User · Answer

1   Check here   Basically do  this   Function FileExists ByVal FileToTest As String  As Boolean    FileExists    Dir FileToTest   lt  gt      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

[vba] Deleting a file in VBA

Examples related to vba

Examples related to file-io

Examples related to delete-file

Examples related to file-exists