Delete Files Using VBA

Home/Excel VBA/Delete Files Using VBA

VBA Delete Files Macro helps to Delete Files using VBA from a folder. Generally while dealing with the file operations we create or delete files using VBA in the process. For example when we are automating a specific task, we create lot of intermediate files and finally we produce one final output.At end we clean or delete all the intermediate files using VBA.


Sometimes we may regularly monitor outlook and when we receive a new mail with required input files. We will save these files in a specific folder to process these files and produce the final output.And we will check before saving the input files in the folder and delete if there are any existing files are already available in the directory.

Solution(s):

We can use DeleteFile method of FileSystemObject to delete files using VBA.Follwing are the examples to delete a specific file or files from a location.

VBA Delete Files Macro – Example Cases:

Delete a specific file from a folder

Following is the example to delete a specific file from a folder or a directory.You can use DeleteFile method of FileSystemObject to Delete a specific file from a folder.

First it will check whether the file exists in the sourse location or not. If it exists it will delete from the directory ot it will display message.

Code:
'In this Example I am deleting a File 'Sample.xls' which exists in the same location of the macro file
Sub sbDeletetingAFile()
Dim FSO
Dim sFile As String
'Source File Location
sFile = "D:\Job\" & "Sample.xls"   'You can change this Loaction
'Set Object
Set FSO = CreateObject("Scripting.FileSystemObject")
'Check File Exists or Not
If FSO.FileExists(sFile) Then
'If file exists, It will delete the file from source location
FSO.DeleteFile sFile, True
MsgBox "Deleted The File Successfully", vbInformation, "Done!"
Else
'If file does not exists, It will display following message
MsgBox "Specified File Not Found", vbInformation, "Not Found!"
End If
End Sub
Output:

Case 1: If file does not exists in the source location to delete, It will display following message.

Delete Files Using VBA

Case 2: If file is exists in the source location, It will delete the file from source location. And it will display following message.
Delete File from a location -Output

By |June 15th, 2013|Excel VBA|8 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

8 Comments

  1. Fermin Bas November 30, 2014 at 9:42 PM - Reply

    I’m trying to delete a previous version of a file that my vba program is creating, but when the code that you describe in this page (I’m using it right as yours, only changing the name and location of file, of course) I get an error: “Run-time error ’70’: Permission denied”. I have to clarify something: I did use your code with another program that I wrote in vba excel 2010 where I used to save a file in Desktop because it was a temporary file, then it worked fine, this time I’m deleting a file in a certain address, not in Desktop and then is when I get the error message mentioned before.

    Thanks in advance for your assistance

    Fermin Bas

    • PNRao December 4, 2014 at 6:38 PM - Reply

      Hi Fermin,
      We can not delete the files from the web servers using this method. If you want to to delete the the file from your shared drives, make sure you entered correct server path and you have delete permissions.

      Thanks-PNRao!

  2. Atle Olsen May 7, 2015 at 1:49 PM - Reply

    Hi, what if I want to delete all files in a folder older then 30days, how is this possible?

  3. Mahindra June 30, 2015 at 4:08 AM - Reply

    Is it possible to have a vba procedure/func to close any text based “.dat” type files if they are open from a given directory path.

    I have another excel program that runs based on bunch of these dat files as input files from an input folder.
    Before running excel program I want to make sure all my text based dat files are closed if they are open in a notepad for example.
    There is no need to save these dat files if they are open in notepad before closing them programmatically.

  4. anjas April 20, 2016 at 4:27 PM - Reply

    Thanks for your info, this success in my work… thansk very much

  5. chethan May 25, 2016 at 5:39 PM - Reply

    I have created a macro which has scan and delete particular drive(ie D:\ drive). But i need to add one more drive for the below macro (ie P:\ drive). and i need to add IF CONDITION for checking if P or D drive is not available in system still i need to get the macro run and log should be saved. Kindly help me
    Below is my macro for scanning only one drive
    ‘Force the explicit delcaration of variables
    Option Explicit

    Sub ListFiles()

    ‘Set a reference to Microsoft Scripting Runtime by using
    ‘Tools > References in the Visual Basic Editor (Alt+F11)

    ‘Declare the variables
    Dim objFSO As Scripting.FileSystemObject
    Dim objTopFolder As Scripting.Folder
    Dim strTopFolderName As String

    ‘Insert the headers for Columns A through F
    Range(“A1”).Value = “File Name”
    Range(“B1”).Value = “File Size”
    Range(“C1”).Value = “File Type”
    Range(“D1”).Value = “Date Created”
    Range(“E1”).Value = “Date Last Accessed”
    Range(“F1”).Value = “Date Last Modified”
    Range(“G1”).Value = “File Deleted”
    Range(“H1”).Value = “File Deletion/Scan Time”
    Range(“I1”).Value = “File Path”

    ‘Assign the top folder to a variable
    strTopFolderName = “D:\”

    ‘Create an instance of the FileSystemObject
    Set objFSO = CreateObject(“Scripting.FileSystemObject”)

    ‘Get the top folder
    Set objTopFolder = objFSO.GetFolder(strTopFolderName)

    ‘Call the RecursiveFolder routine
    Call RecursiveFolder(objTopFolder, True)

    ‘Change the width of the columns to achieve the best fit
    Columns.AutoFit

    End Sub

    Sub RecursiveFolder(objFolder As Scripting.Folder, _
    IncludeSubFolders As Boolean)

    ‘Declare the variables
    Dim objFile As Scripting.File
    Dim objSubFolder As Scripting.Folder
    Dim NextRow As Long

    ‘Find the next available row
    NextRow = Cells(Rows.Count, “A”).End(xlUp).Row + 1

    On Error GoTo Handler

    ‘Loop through each file in the folder
    For Each objFile In objFolder.Files
    Cells(NextRow, “A”).Value = objFile.Name
    Cells(NextRow, “B”).Value = objFile.Size
    Cells(NextRow, “C”).Value = objFile.Type
    Cells(NextRow, “D”).Value = objFile.DateCreated
    Cells(NextRow, “E”).Value = objFile.DateLastAccessed
    Cells(NextRow, “F”).Value = objFile.DateLastModified
    Cells(NextRow, “I”).Value = objFile.Path
    ‘If (DateDiff(“d”, objFile.DateLastModified, Now) > 1) And objFile.Type “Data Base File” And Left(objFile.Name, 2) “~$” Then

    If (DateDiff(“d”, objFile.DateLastModified, Now) > 7) And (objFile.Type = “Microsoft Word Document” Or _
    objFile.Type = “Microsoft Word 97 – 2003 Document”) And _
    Left(objFile.Name, 2) “~$” Then
    ‘Set FileSys = Nothing
    ‘Set objFolder = Nothing
    ‘Set objSubFolder = Nothing
    ‘Set objFile = Nothing
    Kill (objFile.Path)
    Cells(NextRow, “G”).Value = “Yes”
    Cells(NextRow, “H”).Value = Format(Now, “yyyy-dd-mm hh:nn”)
    Else
    Cells(NextRow, “G”).Value = “No”
    Cells(NextRow, “H”).Value = Format(Now, “yyyy-dd-mm hh:nn”)
    End If

    NextRow = NextRow + 1
    Next objFile

    ‘Loop through files in the subfolders
    If IncludeSubFolders Then
    For Each objSubFolder In objFolder.SubFolders
    Call RecursiveFolder(objSubFolder, True)
    Next objSubFolder
    End If

    Exit Sub

    Handler:

    Resume Next

    End Sub

    ‘In this Example I am Copying the File From “C:Temp” Folder to “D:Job” Folder
    Sub sbCopyingAFile()

    ‘Declare Variables
    Dim FSO
    Dim sFile As String
    Dim sSFolder As String
    Dim sDFolder As String

    ‘This is Your File Name which you want to Copy
    sFile = “Files_Deletion Macro.xlsm”

    ‘Change to match the source folder path
    sSFolder = “D:\Desktop Scan and Deletion\”

    ‘Change to match the destination folder path
    sDFolder = “Z:\PMOS\DMS Control Room\Chethan\A5901\”

    ‘Create Object
    Set FSO = CreateObject(“Scripting.FileSystemObject”)

    ‘Checking If File Is Located in the Source Folder
    If Not FSO.FileExists(sSFolder & sFile) Then
    MsgBox “Specified File Not Found”, vbInformation, “Not Found”
    Else
    FSO.CopyFile (sSFolder & sFile), sDFolder, True
    End If

    End Sub

    Sub ClearSheet()

    Sheet1.Cells.Clear

    End Sub

  6. Kamila June 11, 2016 at 12:08 AM - Reply

    I am getting this same issue and its very difficult to solve this problem without any third party app, i guess the best thing to do is to use a third party app like Long Path Tool. Just download it and use it to solve this issue. I hope this would help.Give it a try..

  7. bobet October 7, 2016 at 11:59 AM - Reply

    how about running a .cmd file from execl macro.

Leave A Comment