Using VBA, how can I:
This question is related to
vba
file-io
delete-file
file-exists
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
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.
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!
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.
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
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).
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.
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
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
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.
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
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).
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
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!
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
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.
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
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!
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).
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
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
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!
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
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
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).
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.
Source: Stackoverflow.com