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

Premium Project Management Templates
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE85% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: June 15th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

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

    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

      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

    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

    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

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

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

    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

    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

    how about running a .cmd file from execl macro.

Leave A Comment