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.



50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates

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.


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.

'In this Example I am deleting a File 'Sample.xls' which exists in the same location of the macro file
Sub sbDeletetingAFile()
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!"
'If file does not exists, It will display following message
MsgBox "Specified File Not Found", vbInformation, "Not Found!"
End If
End Sub

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

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

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.


  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.


  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

    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”)
    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


    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”
    FSO.CopyFile (sSFolder & sFile), sDFolder, True
    End If

    End Sub

    Sub ClearSheet()


    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

Related pages

unprotect excel 2010do while loop vba exampledelete module vbahow to dedup in excelconsolidate cells in excelexcel macro workbookvba vbcriticaloops tricky interview questionprint in vbaoutlook vba codetutorial macros excelvba definevba sheetunprotect password protected excelexcel 2007 checkboxmerging cells excelpivot chart tutorialhow to add a command button in excellookup function excel 2010shortcut keys on excelcombining chart types in excelexcel form vbavlookup formula excelsas programming for beginnersexcel formulas definitionexcel column width in inchesmicrosoft excel remove duplicatesexcel functions and formulas with examplesexcel spreadsheet gantt chart templateexcel sorting numbersconsolidate data in excel from multiple worksheetsms excel macro programmingsort excel alphabeticallyexcel vba import csvhow to de dupe in excelselect statement vbaexcel sorting datesadodb.recordset vbavba open txt fileprotect worksheet excel 2007combine excel worksheets into onesql coding for beginnersmacro to remove duplicateslearn to write vba code for excelvb6 mysql connection stringconditional statement worksheetmacro to delete rows in excelusedrange excelproject schedule template excel 2007pivot tables in excel 2003save vba excelvba close excel fileuseful macros in excelhow to unprotect excel filelearn vlookup in excelsas programmer interview questions and answershow do i merge columns in excelcustomer management excel templatevba goto cellexcel datevalue functionadvanced sql interview questions and answerssimple pie chart worksheetusing concatenate in excelexcel macros tutorialsvlookup simplerow vbahow to lock an excel documentexcel vba global variablesadodb connection objectcreate userform in excel vba using codehow to make a dropdown list in excel 2010example of vlookup in excel 2010vba update queryms excel tutorial with examples