Tags

, ,

In this article I will explain how you can delete files using VBA.


Basics:

The code below will delete the file specified by the path associated to the variable strPath:

Sub Example1()
Dim ObjFso As Object
Dim strPath As String

'file path
strPath = "D:\Stuff\Business\Temp\TempFile.xlsx"
Set ObjFso = CreateObject("Scripting.FileSystemObject")
'deletes file
ObjFso.deletefile (strPath)
End Sub

In the code above it is assumed there is a file named “TempFile.xlsx” at the location “D:\Stuff\Business\Temp\”.

Before:

VBA, Delete file

After:

VBA, Delete File REsult


Check File Existence First:

If you attempt to delete a file that does not exists  you will get the following error:

Check File Exists

A good method for preventing this error from occurring is to check if the file exists before attempting to delete it. The code below checks if the file specified by the path exists or not. If it exists it will delete the file:

Sub Example2()
Dim ObjFso As Object
Dim strPath As String
Dim CheckExists As Boolean
'file path
strPath = "D:\Stuff\Business\Temp\Tempfile.xlsx"
Set ObjFso = CreateObject("Scripting.FileSystemObject")
'deletes file
CheckExists = ObjFso.FileExists(strPath)
If CheckExists = True Then
    Call ObjFso.deletefile(strPath)
Else
    MsgBox ("The file does not exist")
End If
End Sub

 For more information about this topic please see the link  below:


Run-time Error ’70’ Permission Denied:

Another error that you may encounter when trying to delete a file using VBA, is the Run-time Error ’70’ Permission Denied error. This error occurs when the file is open. This can be prevented by using error handlers.

The function below receives as input a string path it tries to delete it. If the file is open, the error handler will catch the error:


Private Sub Delete_File(ByVal strPath As String)
Dim ObjFso As Object

Set ObjFso = CreateObject("Scripting.FileSystemObject")
On Error GoTo lblError:
'attempt to delete the file
ObjFso.deletefile (strPath)
'remove the FileSystemObject from memory
Set ObjFso = Nothing
Exit Sub

'if the attempt to delete the file fails
lblError:
Err.Clear
End Sub

the code below calls the function:

Sub Example3()
Call Delete_File("D:\Stuff\Business\Temp\Tempfile.xlsx")
End Sub

See also:

You can download the file and code related to this article from the link:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

 

Advertisements